Advanced Excel – Data Validation
When multiple people are working on the same file, sometimes it is necessary for a cell to warn users that the data is not the correct type for that cell. Data validation allows you to designate a type of data for a cell. It also allows you to designate if Excel will block incorrect data or allow it and give a warning after the incorrect data has been entered. This can help keep forms neat by not allowing data which would cause errors in formulas.
- Open the Order_Form.xlsx workbook.
In the first sheet in this book, we have an order form. We want the data entered into this form to be both realistic and coherent. To make sure this is done, we will restrict the type of data that can be placed in each column.
- Select Column A.
In this column, we want to ensure that the only data entered is text.
- Navigate to the Data tab and click the Arrow that says ‘Data Validation’ in the Data Tools group.
- Choose ‘Data Validation…’.
A dialog box is displayed with three tabs. In the first tab, you can choose the type of data to be entered in that cell.
- In the section labeled ‘Allow:’, choose ‘Text Length’.
- In the section labeled ‘Data:’, choose ‘less than’.
We choose this option because we want the length to be less than 19 characters, but we are not concerned with the text length being too short.
- Specify the max length to 19 characters.
- Navigate to the ‘Input message’ tab.
This tab edits whether or not a message is displayed while one of the validated cells is active.
- In the title section, type “Last name, First name”.
This is so that users of this sheet know to put the name in with their last name first.
- In the message section, type “Names may be at most 19 characters in length.”
In some cases, names that are too long can cause problems such as when a name does not fit on a shipping label.
- Click on the Error Alert Tab.
This designates whether or not a user can enter invalid data as well as whether they will receive a message or not.
- Under style, select ‘stop’.
This option will stop the user from saving invalid data in the sheet. They will also receive a message saying that they entered invalid data.
- For the error message, type ‘Names must be less than 19 characters.’
Since the general message does not specify why the data was entered incorrectly, we want to tell users what kind of data should be entered.
- Click ‘Ok’ to save the data validation.
- Click into cell A2, type “Williamson, Jonathan” and press enter.
- Press Cancel to delete this name.
If more control is needed, user’s data validation can cause anyone who edits this cell to select from a list of possible entries.
- Select Column B.
We want to make sure any data in this column is from the list in column G.
- Open the Data Validation menu and choose ‘List’ in the allow section.
- Choose cells G2:G18 for the source of the list.
- Click Ok and click into cell B2.
Notice there is now a dropdown arrow next to this cell.
- Click on the dropdown arrow and choose one of the items.
- Fill in a few more cells in column B.
For the next column, we want the list to be dependent on the data in the item column. Notice to the right of each item in column G is a list of sizes. Each item has a different list of sizes, so we are going to use a name range to create a list which will be dependent on the previous selection in the form.
- Select column C.
- Open the Data Validation window and select list.
We won’t actually be choosing a list, but we will be entering a formula to tell excel where to find the name of the list.
- Type “=indirect(B1)”.
Since C1 is the active cell, this tells Excel to look to the cell to the left for the name of the list.
- Click ‘Ok’.
- Click ‘Yes’.
- Click on the dropdown arrow next to cell C1.
At this point, we have not named the ranges that our list will come from, so there are no entries in our list and nothing happens.
- Select Cells H2:T18.
- From this selection, define the range name based on the values in the left row.
As we did before with range names, we are defining a list by the name to the left of the range.
- Click on the drop down list in cell C2.
Since we have defined a list to each name in column B, there are now options in the drop down list in column C. For most of these lists, there are blank entries, and the actual options cannot be seen without scrolling through the list. This is because with our set of data, there were blank entries in the rows. We can fix this by simply deleting the blank cells in our named ranges.
- Select cells H2:T18.
We will be using a search function, but we only want to search within this range of data.
- Navigate to the editing group on the Home tab and choose Find and select.
- Choose Go To special…, select blanks, and click ‘Ok’.
This selects all of the blank cells in our selected range. With these cells selected, we can now delete them.
- Now right click on a selected cell, choose delete, and shift cells left.
Nothing actually changed on the sheet, but the blank cells are now removed from our named ranges. The ranges could have been made without having the blank cells; however, that would have involved selecting each row and naming the ranges individually. There should no longer be blank cells available to be selected in column.
- Click the dropdown arrow in cell C2 to check for blank entries.
Because we do not want our lists to be on the same page as our order form, we can move it to another sheet.
- Select cells G2:T18 and press ctrl + x on the keyboard.
- Click on sheet 2 and select cell A1.
- Press ctrl + v on the keyboard.
- Return to sheet one and test some cells in columns C and B to check the data validation.
- Select column F and open the data validation window.
- In the allow section, choose custom.
If a time range restriction was required, or if the date was not dependent on the current date, the date option could be chosen here. Since the date that we want information entered after will change, we will have to use a formula.
- In the formula section type “=F1>TODAY()-1”.
This is telling Excel that the date in D1 must be greater than the current date minus one day. This way, we allow users to enter todays date in addition to any date after today.
- Press ok and try to enter todays date in the cell F2.
Since the current date is included, we do not get an error for this data.
- In cell F3 try to enter yesterday’s date.
Since yesterday’s date is not greater than the current date minus one day, Excel returns an error. Now, if today’s date was entered and the document was reopened another day, we would not receive an error unless the cell was edited. This is because data validation only returns errors on data that is entered.
As we have just seen, when it is important that data follow certain rules in a worksheet, data validation can ensure that this happens.