The previous post showed how to add a drop down list using a short list of terms. Entering a drop down list can be even quicker when applying yes/no choices to Excel.
I want to add “yes and no” options to this worksheet
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 E2 to E6.
In the Data tab select Data Validation, and in the Settings menu, select List
I can add yes and no options, straight into the Sourcesection.
Adding the text options directly into the source section is quicker than creating a reference list.
I tick the boxes for Ignore blank and In-cell drop down. Then click ok.
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.
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.
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.
Data validation has saved me lots of time when entering data in my worksheet.
