Apply Data Validation in Excel – STL

Save time by avoiding repetition and errors

Excel is great for lists; sales figures, staff rota’s, stock control, to name a few. But an easy trap to fall into is repetitive error prone data-entry that leads to inaccurate business reporting and lost time in troubleshooting.

If you create a drop-down list in Excel, you can avoid all of this. Imagine the time saved short and long term especially if multiple people are using the same spreadsheet.

How to create a drop-down list in Excel

excel_drop_list — Here’s one we made earlier

Step 1 Assign the values for your drop-down list. In a new worksheet, just start your list and order it if you wish (better now than later!)

Step 2 Now select the data and right click, select Define Name.

Step 3 In the New Name dialogue box you need to give your data name (this is a named range), making sure not to have any spaces in the name. Example, Commute

Step 4 Now go to the worksheet where you wish create a drop-down list in Excel, and click a cell. Go to the Data tab and select Data Validation

Step 5 In Settings tab we need to do the following:

Select List from the Allow box.
Ensure In-cell dropdown is ticked. If you are okay for blank entries to be made just leave the Ignore blank ticked.
In the Source box we need to type in the name of our list making sure to start with an =. In this case, =Commute

Now click OK, your drop-down list is ready to go. You may have noticed two other tabs within the Data Validation box. The Input Message and Error Alert give you even more options to control how data is entered and also what messages appear to users when they have not entered data correctly.

To create a drop-down list in Excel is pretty straight forward giving us some major advantages in saving time from data entry as well as data error. Data validation in it’s own right can really help businesses adopt more consistent and efficient use of Excel spreadsheets.

More related information:

Excel data validation in business

A real world example of assigning values to a drop-down list in Excel

A further look at Input Message & Error Alert

Anything that can cut down the time it takes to add data and improve accuracy is good in my book.

Creating a drop down list isn’t as tough as I thought, so here is how to set up a drop down list using Data Validation.

I have a set of customers, that come under four regions, I have regular aftercare appointments and I want to allocate each customer to one of my aftercare team.

Here is my basic worksheet. I can type each piece of information into the appropriate cell.

data-validation-list-advanced-excel-courses — The basic worksheet…do I enter the data manually or is it quicker to set up a drop down menu?

As each column has a clear choice of options, I could speed up data entry using drop down lists.

For my Sales Region, I have four options: north, south, east and west.

region-list-data-validation-excel-advanced-cours — I have created the list of regions in column G

To set these up as a list, I type them in another range of cells. These cells are for Excel to refer to, so I put them in my worksheet where they won’t interfere with my actual data. ( I have put them in the G column).
To set up the drop down list, I select the cells where I want the drop down lists to appear – in this example, I want to apply it to B2 to B6.
I go to the Data tab and select Data Validation, and in the Settings menu. Here I select List.
I select “List” so that Excel knows what I want it to do!
I then need to select the range of cells where my options are set up. I click and drag over the range of cells G2 to G6).
I find it easier to click and drag over the my reference cells rather than type them in manually).
I make sure that I have ticked the boxes for Ignore blank and In-cell drop down. Then press ok.
The drop down list is now applied and when I click into the cell, a grey down arrow appears, and I click on the choice to apply it. So 2 clicks, and I’ve added the region, rather than typing it manually.
When you click on the cell’s down arrow box, the options appear, and it takes one click to add it.

I can add drop down lists to the other parts of my worksheet. Using the same method as above, I type in my reference lists (from column G to column I) then use the Data Validation to apply drop down menus to all my columns. In a few clicks, I’ve set up my worksheet and saved time each time I update my worksheet.

worksheet-data-validation-excel-advanced-courses — Data validation has saved me lots of time when entering data in my worksheet.

For more information on how to speed up data entry and get more out of your data, have a look at our Excel advanced courses, https://www.stl-training.co.uk/excel-2010-advanced.php

Blog Categories