Updated: Jun 6, 2022
This weekly blog will be a way for me to share different software tips I use in my work and any other content I deem relevant and important. Usually, the post will be complementary to a video I’ll release on my YouTube channel during the same week. I hope we can connect, collaborate, learn from each other, and even have some fun via this platform. Let’s dive in!
Data validation is an underrated feature important for all data entry and record keeping. Data validation allows you to control the entries made in your data entry form. For example, you may want whole numbers, decimals, dates within a specific range, texts of a specific length, and so on. I’ll use an example to show you the different ways data validation is useful.
Meet our friend, Yücel (pronounced “you-gel”). You’ll come to learn that Yücel is the busiest person in the world. Yücel owns 3 art galleries called Big Sleep Arts in Utopia city where they sell their art pieces. Yücel wants to use Excel to manage all the information related to their business. The 3 galleries are: Blue (B), Red (R) and Green (G). The first entry form Yücel creates is for sales. They record the date of the purchase, the branch where the sale was made, the ID of the art piece purchased, the unit price of the art piece, the quantity of the art pieces, and the total amount.
Table 1: Sales
Yücel wants to use data validation to prevent the employees at the 3 branches from making errors while entering information in the sales table. To find data validation, we go up to the Toolbar and select Data, then Data Validation
Finding data validation in Excel
Yücel limits the date entries to a particular date range, by first selecting the range of cells they want. In this case, they select the whole Date column. Then, in the data validation pop-up window, under settings, select “Date” in the “Allow” dropdown list.
Selecting Date as a validation criterion
Then, while in the data validation pop-up window, Yücel creates a date range to limit the dates one can enter in the column. Yücel uses 02/08/2021 as the start date and 31/12/2021 as the end date (dd/mm/yyyy is the superior date format, please)
Adding a start date and end date
For the branches, Yücel wants the employees to enter the specific branches easily without any spelling errors. They use a dropdown list to achieve this. Yücel selects the Branch column, goes back to data validation, and selects “List” in the “Allow” dropdown.
Select “List” from the dropdown list
Yücel can either manually enter the names of the branches separated by commas in the “Source” box…
Enter branch options manually in the “Source” box
OR, they can write (or paste) the names of the branches anywhere in the worksheet, and insert the range into the source box. I recommend this for a longer list of items, such as the number of districts in Uganda. Notice your desired range of cells in the “Source” box is automatically updated by Excel.
Find the desired range for the dropdown list in the worksheet
Now, when Yücel clicks in any cell in the “Branch” column, they can view the dropdown list by clicking on the downward facing arrow in the right side of the cell
Dropdown list created
whole number – Yücel can use this for item_id and quantity to restrict the cell to whole number (0-9) entries
decimal – restrict the cell to enter only decimal numbers
time – Yücel could use this for employees to record work times in a separate worksheet, and restrict the cells to accept only a valid time within a certain range. For example: 8AM – 5PM.
text length – Yücel could also use this for the entries in the item_id column to restrict the length of a text entry
custom formula – enter a custom formula. For example: if Yücel wants the number entered in a cell to always be twice a certain value minus five (2x – 5). Not sure why Yücel would be such a show off.
To use the above validation criteria, Yücel follows the same process(es) they have used above.
Yücel can place an input message within a desired cell range to prompt the person entering the data to enter it appropriately. To do this, in the data validation pop-up window, click “Input message”, then type the message of your choice
Create an Input message to guide data entry
Yücel can also enter custom error alerts if the person entering the data goes rogue. In the data validation pop-up window, click “Error Alert” and enter the error message of your choice (I recommend creating a more useful alert that shows the person entering the data what to do).
Create error alerts to guide data entry
If an employee with dreams of an expansion insists on entering a branch called “Yellow”, they will view the custom error Yücel sets.
Error alert. Please use a more instructive error alert
Once Yücel masters data validation, they learn it is valuable for handling more sophisticated tasks in Excel, such as dashboards, multiple dependent dropdown lists and so on. However, for now, these basics are a good start for Big Sleep Arts to manage their information.
PS:
1. If you have any questions, comments or concerns about any of the concepts I have tackled in this article, OR if you know better tricks, please reach out to me. I would love to hear from you. Otherwise, please subscribe to this blog for more updates.
2. This process is similar in Google Sheets if you prefer to use that.
©[year] Rights Reserved
