Excel Series: Drop-Downs
What are drop-downs?
Drop-downs? What are those? Ever seen little triangles appear with suggested content in Excel? Yup, those are the drop-downs we are referring to today.
adjective: drop-down or dropdown
dropping down or unfolding when required.
“a drop-down bed”
(of a menu) appearing below a menu title when it is selected, and remaining until used or dismissed.
Users add drop-downs to limit the text added to a specific cell. This could be useful when:
- other users need to add information to your sheet
- spelling and consistency is important
- pre-determined options exist
- using cell content a formula or function
For example, if you wish for users to choose between two options, such as “Yes” or “No”, or if specific department names are used, a drop-down allows the user to pick from the pre-determined list of names.
In Excel, this tool is called “Data Validation”. It is located on the Data Ribbon under the Data tools group.
How to use them
Firstly, you need to populate cells with your list of items that you wish for the user to choose from. This is often done in a separate sheet that is protected, and / or hidden, from user changes.
Secondly, click on the cell where you want the data validation to apply. Navigate to the Data Validation tool and under “Allow”, select “List”.
Now, select your “Source”. This is is the range of cells that contain the list of items you populated in the first step. Lastly, click OK.
And that’s it! You’ve created your first drop-down list! Easy peasy.
Now, if you wish to apply this data validation on other cells, simply copy and, using paste special (refer to the next blog post), apply the data validation on any other cells.
You could now use this information in other formulas, such as VLOOKUPs and SUMIF functions as shown in the screen recording below.
Important tips to remember
- When selecting your source, make sure you apply absolute references to your range of cells
- Using name manager together with an “INDIRECT” function, you will be able to make your data validation dynamic based on your cell content
- When you add additional lines to your list, make sure you update your data validation source
- There are other data validation options as well:
- by default, “any value” is selected, which means there is no data validation on the specified cell. This is also how you would remove data validation from a cell.
- whole numbers are useful for product codes
- decimals can be used to validate the input of percentages
- dates and times allow you to select a range (i.e. lower and upper limits) between which the dates and / or times should fall
- If you are sharing this file with another user and wish to provide instructions for the content of the cells, you can explore the “Input message” tab, which brings up a tooltip when the user clicks on a specific cell. This tooltip is similar to a cell comment, however it only appears if the cell is selected.
- The third tab contains an option to add an error message – this message will appear when the user has input the incorrect information based on your validation settings
- Note that users cannot type the incorrect information if there is data validation on the specific cell, however they could accidentally add the incorrect information if they copy and paste contents from a different cell. For this reason, it would be important to consider protecting the sheet.
There you have it, a basic look at adding data validation, and specifically drop-down lists to a cell. Book your spot at our Excel Advanced workshop to learn more about some other features of data validation.
Finally, we are looking forward to hear how you have used drop-downs in your workplace. Make sure to add a comment below ?