Data Validation in Excel

The utility of excel in our everyday life cannot be overstated. Spreadsheets can be used to carry out research, budgeting, valuations and so many other things. All of the aforementioned spreadsheets are usually have allowance for sensitivity analysis built in. These sensitivity analysis could be in form of 2 dimensional or 3 Dimensional tables, or through the use of a dropdown list.

The advantage of using a data validation is that you can specify the range of inputs you want the user to play around with in the model. You can also make sure the user chooses one out of a finite number of choices. This is particularly useful for people carrying out their undergraduate projects utilizing the Likert scale. If you don’t use data validation, the user is free to write whatever he wants in those cells, and It could be a serious pain sorting the information and manually reclassifying the responses into the categories you desire. Now that we have established just how important data validation is, let’s move into the various types of data validation.

Operations in Excel’s Data Validation

There are various types of operations in data validation, a few of them are:

  1. Allow only numbers within a specified range in the cell
  2. Allow data entries of a specific length
  3. Specify range of dates and time and restrict dates and time outside this range
  4. Validation based on another cell value
  5. Display an input message when the cell is selected
  6. Display a warning message when incorrect data has been entered

Now let us look in depth into each of these seven categories we mentioned above. I hope that by the end of this post, you would be comfortable creating any of these in your spreadsheet.

Allow Only Numbers within a Specified range in Excel

For this type of data validation, we can specify the lowest and highest value of numbers we want the user to input in the cell. This is particularly useful for keeping a database. For example we may have a database of orders in a food production and delivery company where the user is required to input an order ID but the policy might be to limit each worksheet to 3,000 records for monitoring purposes. All you need to do is to specify the starting order ID and the maximum number you can have. Let’s take an example with the steps below.

Step 1: Imagine we have an excel sheet for recording order details at a restaurant, we can designate one of the columns as the Order ID column and specify a range that must be entered into each cell. The first step is to prepare the headings and then highlight the column you are designating as the Order ID column

Allow Numbers within Specified range Step 1

Step 2: On the ribbon, navigate to Data > Data Tools Group and then select Data Validation

Allow Numbers within Specified range Step 2

Step 3: After finishing step 2, a data validation box should pop out. Proceed by Selecting “Whole number” in the allow field dropdown.

Allow Numbers within Specified range Step 3 1

Step 4: Ensure that “between” is selected in the data field, and specify your minimum and maximum value like I did as stated above and click on OK.

Allow Numbers within Specified range Step 4 1

Result: If anyone inputs a number below or above the range we specified (between 1000 – 4000), you get an error notification as seen below

Allow Numbers within Specified range Step 5

Allow Data Entry of A Specific Length

This is particularly useful for research purposes where you want to give respondents the flexibility to enter unique answers but you also want them to be as brief as possible. The steps are very similar to the one treated above, let’s go over them.

Step 1: Highlight the column you are designating as the response column

Limit text Step 1

Step 2: On the ribbon, navigate to Data > Data Tools Group and then select Data Validation

Allow Numbers within Specified range Step 2

Step 3: In the data validation pop out select “Text Month” in the allow field dropdown.

Limit text Step 2

Step 4: Set a minimum and maximum length. Note that it is measured in characters so for our example, we would set the maximum limit at 20 characters.

Limit text Step 3

Result: If anyone inputs information above 20 characters into any of the cells, the person gets an error notification. Remember you can customize the error message as discussed in the previous section.

Limit text Step 4

Specify Range of Date & Time

With time sensitive models or templates, you may want to specify the allowed range of dates as input in the model. An example is when you are preparing the annual budget and you are expecting equity injection or debt drawdown sometime within the year. This singular input could have been designed to flow into two of the three financial statements (Cashflow Statement & Balance Sheet), and selecting an equity Injection date or debt drawdown date outside this annual budget period may result in errors for the user of the model. To avoid this, you can limit the user’s selection to a specific range of dates.

Our example in this section has a snapshot of the dashboard of a budget. Let us assume that management has communicated both an equity injection and new debt finance drawdown within the budget year, but we do not know the specific date. In order to see how the injection and drawdown dates affect the whole model, we have provided an allowance for the user to play with the dates to ascertain the perfect time to seek relevant finance(equity injection or new debt finance) based on the funding gap. However we know that our budget is ending by 31st of December 2022, so a drawdown of injection after this date is irrelevant as it would have no effect on the model.

Date Data Validation

What do we then do? Simple, allow only dates between the model start date and the model end date. To do this, highlight the Injection date and drawdown date cells and follow the steps in the first section to pop out the data validation box.

Date Data Validation Step 2

Step 2: Select date from the dropdown in “Allow:”

Date Data Validation Step 3

In the start date and end date fields, select your preferred dates. In our example, I select the model start date as my start date and the model end date as my end date then click on OK.

Data Validation Step 5 2

Result: When we try to input a date outside this range, we get an error notification

Data Validation Step 6

Validation Based on Another Cell Value

Excel gives you an opportunity to make your drop down list more dynamic, for example if I have two selections to make, it would be way cooler and neater if the second selection (which is assumed to be dependent on the first selection) changes in accordance with what we select in the first selection. Yes, this is possible through the use of the Indirect function. Let’s go over the steps.

Step 1: Organize your list starting with the dropdown you want to appear in the first selection and then progressing to a sub list for the options in the first selection. Confusing right? Dont worry, it would be easier to understand with a visual representation. In our example today, let’s assume that we are planning a party and we want users to select between two meal choices but these 2 choices have different variants.

Dropdown based on other cell Step 1 2

We can see that there are 2 categories of food items we are making available but each one has different variants. Create your list as seen in the above picture and proceed to step 2. Don’t worry if you have more items in the first dropdown, just extend it and create new column for each item.

Step 2: For each list in the previous step, create an individual named range in the name box. To do that in our example, first select Cells A4:A5, and in the name box type in Item and press enter. Then select Cells B4:B8 and input “Pizza” in the name box, and finally select cells C4:C7 and input “Shawarma”

Dropdown based on other cell Step 2a
Dropdown based on other cell Step 2b
Dropdown based on other cell Step 2c

Step 3: Create a worksheet where you would ask the user to select from the options (as seen below)

Dropdown based on other cell Step 3

Step 4: In cell B3 containing food type, open the data validation box and select list, then type in the named range “Item” that we created in step 2.

Dropdown based on other cell Step 4
Dropdown based on other cell Step 4b

Step 5: In cell C3, open the data validation box again and this time around type in =Indirect($B$3)

Dropdown based on other cell Step 5

Result:

Dropdown based on other cell Step 6a 1
Dropdown based on other cell Step 6b

Display an Input Message When the Cell is Selected

Add an Input Message

You can also add an input message to notify your users of the requirements. This input message would show as the user selects the cells. To add an input sheet, reopen the data validation box and click on input message.

Input message Step 1

Now type in details in the title field and input message field, then click on OK

Input message

Result: The input message you set is displayed once the user selects the concerned cells.

Input message Step 2

Display a Warning Message When Incorrect Data has been Entered

Error Alerts

You can specify the error message to be displayed to users that do not fulfill the set requirements. For example, let us specify the requirements of between 1000 and 4000 in the data validation we have created. To do that, reopen the data validation box and click on error alert.

Setting Error alert

Type in your message in the error message box and click on OK to register it.

Setting Error alert Step 2

Now let’s try to input a number outside our range in the Order ID column

Setting Error alert Step 3

Voila, our message appears to inform the user what is required of him or her. Please note that you can use these steps to set error messages for every other type of validation we would touch on in this post.

Leave a Comment