How to add a drop down list to speed up data entry in Excel – STL

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

By Richard Bailey