What comes to your mind when you think about Excel? Most of us think about building tables for data sets, using formulas and tools to analyze data, displaying the data by using charts, and maybe some are eager to do analysis with PivotTables.
We have access to multiple tools on the Excel tabs to make our work easier and faster. One of the tools that is quite amazing is Data Validation on the Data tab (Figure 1).
Data Validation is used to control the cells’ input to ensure that the data is complete, accurate, and error-free.
Let’s look at how we can use Data Validation for the following table (Figure 2), which hypothetically is being used daily to track the number of patients coming to the Emergency Room in one of Alberta’s hospitals.
The new admissions clerk is ready to start her shift. She opens Excel’s template used daily to track all the patients coming to the Emergency Room. She notices that someone from the previous shift entered the first patient’s information into the spreadsheet (Figure 2). When the clerk starts typing the second patient’s information the co-worker asks her whether she wants a cup of coffee. The clerk enters tomorrow’s date (13-04-2021) by mistake being preoccupied with thinking whether she wants double or single cream. As soon as the clerk enters tomorrow’s date, she gets the following notification (Figure 3):
Like some of us, the new clerk doesn’t read the notification and clicks on the “Retry” button. Nothing happens so she clicks on the “Cancel” button. The notification box disappears and the clerk notices that the date automatically changed to 12-04-2021. Magic!
That makes the clerk wonder about what happened on her spreadsheet so she changes the date back to 13-04-2021 and she gets the same notification as previously (Figure 3). This time she reads the notification but doesn’t understand the meaning of the “data validation restrictions”. So, she searches data validation on the web and learns that it is on the DATA tab in the DATA TOOLS group. She clicks on the first date that was entered this morning and clicks on Data Validation under the Data Validation command, and the following dialog box pops out (Figure 4). This time she reads carefully and learns that the creator of the spreadsheet put on the validation criteria restricting the date in the first column to TODAY’s date only.
That piques the clerk’s curiosity and she wonders whether the other columns in the spreadsheet also have restrictions. She looks around and doesn’t see any new patients so she clicks on the first entry in the second column titled “Patient’s AHC #” which is Alberta Health Care’s number (Figure 5). Then she clicks on Data Validation to see if there are any restrictions for this cell (Figure 5).
The clerk keeps looking at the dialog box and realizes that the validation criteria for the patient’s AHC # limit the cell entry to exactly 9 digits. That was the “Aha” moment for our clerk! “That’s right,” she thought, “Albertans’ healthcare card number is 9 digits long.” She starts to wonder what would happen if she enters 10 digits (Figure 6).
The clerk is very impressed with this error alert feature and wonders how it was done so she clicks on the Error Alert tab in the Data Validation dialog box.
“That is so cool.” thought our clerk, “I can type any message I want in the error alert box.” From that moment on the clerk is hooked on learning more about Data Validation. She clicked on the next cell where her predecessor entered the name of the first patient and clicked on Data Validation to see the restrictions (Figure 8).
To her surprise, she sees the COUNTIF formula. She reads that $C$5:$C$100 are the addresses of the cells in the third column titled “Name” and C5 is the address of the first selected cell. It means that Excel counts the number of occurrences of the name in cell C5 in the selected range C5:C100. She types the same name in the second row and gets the following notification (Figure 9).
It means that the formula =COUNTIF($C$5:$C$100,C5)=1 entered in the validation criteria gives the above notification to prevent duplicate entries into this column.
“I wonder what restrictions they use in the next column,” thought our clerk as she clicked on cell D5, the first birth date that was entered into the table (Figure 10). To her surprise, she didn’t see any restrictions but a message (Figure 10).
The clerk was delighted by discovering more about Excel’s Data Validation. She clicked in the next column on cell E5 and noticed a little white square with the arrow down to the right of the cell (Figure 11). She clicked on the arrow and a list of Alberta`s regions popped out.
With the cell E5 still selected the clerk clicked on the Data Validation command to see how these validation criteria work (Figure 12).
The clerk hadn’t yet seen the settings that would allow a list. She noticed that the source refers to cells I2 to I7. She clicked on cell E6 and then she clicked on a little arrow down to the right of cell E6 and, voila, the list opened up (Figure 13). “I think that this list has a dual role, “ thought the clerk, “it allows us to chose the region from the list, and at the same time it prevents us from entering a region that is not on the list.”
“Time to check the last column that has the postal codes,” thought our clerk as she clicked on cell F5 (Figure 14).
As she opened the Data Validation command for cell F5, she noticed that the validation criteria referred to the EXACT and UPPER functions from the TEXT functions group. The UPPER function changes text values to uppercase and the EXACT function performs a case-sensitive comparison. The formula that was entered as the validation criteria requires the postal code in cell F5 to be written in uppercase (Figure 15).
“I wonder what will happen if I write the postal code using lower case letters,” thought the clerk as she typed the postal code (Figure 16).
The clerk clicked “Cancel” and the letters in the postal code changed automatically into uppercase (Figure 17).
“Wow, the data validation is such an interesting Excel feature,” thought the clerk. “It controls what I can enter into the cells, it makes me enter data in a specified format, it can restrict the type of values I can enter into the cell, it allows us to create and use dropdown lists and we can create warning and error messages. With all the data validation in place, we can control the input of data and prevent common input errors.”