Excel drop down lists offer a way to validate data and ensure that a user selects a value from a set of predefined options.
These drop down lists are a handy feature to have when it comes to inputting and managing data within a spreadsheet. They make it quicker to mine insights from a dataset and easier for several team members to collaborate on the same spreadsheet.
The task of data capture and categorisation is also far easier for the person collecting the information. There’s no need to input data manually and run the risk of a spelling mistake, so there’s less chance of human error – and that ultimately results in higher levels of productivity.
How to add a drop down list in Excel
If you’re looking to create drop down lists in Excel yourself, we’ve put together this simple guide to help you do just that:
1) Input data and create a named table
-
- Write a title for your list (e.g. Staff)
- List your data entries directly below the title cell (e.g. John, Marie, Steven etc.)
-
- Select the title of the list and insert named table (Insert > Table)
-
- Drag to desired size
- Tick ‘My table has headers’ and create
2) Create a named list
-
- Click one of the employee name cells
-
- Enter a name for the list (Formulas > Define Name)
-
- Leave the scope as ‘Workbook’
-
- Click in the ‘Refers to’ box and select all names in the list
-
- Hover over the heading cell and when a black arrow appears click to highlight all fields
- Enter a name for the selected range
3) Add the drop down list
-
- Select the cells that you want to include in the drop down list
-
- Click Data Validation (Data > Data Validation)
-
- Choose ‘Allow: List’
-
- Click in the Source box and press F3
- Select a named list and click ‘OK’
Once you have completed these steps, you will have the option to click an interactive arrow next to each cell and select a chosen value from a dropdown menu. Of course, while the method of creating a dropdown list is relatively straightforward, the task of utilising them effectively throughout any given spreadsheet presents more of a challenge.
If you’re still looking to build your knowledge of dropdown lists and the numerous ways in which they can optimise data management, it’s worth getting in touch with an expert Excel consultant. Learning to control how information is stored and accessed in workbooks in this way is crucial to ensure that all company data is accurate and free from error.
