How to create a drop-down list in Excel to manage data and prevent mistakes
- You can easily create a drop-down list in Excel to limit the values that can be entered in a column.
- This data validation helps prevent mistakes, such as misspellings.
- Drop-down lists are also useful for managing data when multiple people use the same spreadsheet.
- Visit Business Insider's homepage for more stories.
For data nerds, Excel's drop-down lists are a lovely gift.
They keep entries consistent across multiple rows - no misspelled words or names written without capitalization. Drop-down lists are essential if you need to sort your data or create a pivot table.
For example, Excel sees "Texas" and "Tezas" as two different states, and therefore two different values, but a drop-down list with the names of the states can prevent errors like this.
Check out the products mentioned in this article:
Microsoft Office (From $129.99 at Best Buy)
Lenovo IdeaPad 130 (From $299.99 at Best Buy)
MacBook Pro (From $1,299.99 at Best Buy)
How to create a drop-down list in Excel
1. The first step is to create a list with all the items you want in your drop-down list.
- You can create your list on the same sheet where you will be entering data from the drop-down list.
- Putting the list entries on the same tab can create confusion. The best practice is to create a separate worksheet for your drop-down list. To create a new tab, click the "+" icon next to the last tab in your spreadsheet. Double click the tab to rename it.
- You'll also want to make sure your items are in a table. If they aren't, you can convert your list to a table by holding "Ctrl" + "T" on your PC or "command" + "T" on your Mac keyboard.
2. In your sheet, select the cells where you want the drop-down list to appear. You can also select a whole column.
3. Click on the "Data" tab in the top menu so the Data menu ribbon appears.
4. Click on the arrow next to "Data Validation."
5. Choose "Data Validation" from the drop-down menu.
6. In the "Settings" tab in the top menu, under "Allow," click "List."
7. Click in the Source box, and the window will shrink to show only this field.
8. Highlight the cells that contain your list. If you put your list in a separate tab, you can open that tab to highlight the cells. The cell range will appear in the window. Hit enter or "return" on your keyboard to set the range for your list.
9. The larger window will reappear. Click "OK" to set your drop-down list.
10. You can see if a cell has a drop-down list because an arrow will appear next to the cell. When you click on the arrow, the list appears.
11. Enter your data, using the drop-down list to supply values.
If you need to edit your drop-down list, select the cells where the list appears and choose "Data Validation." The details of your list will appear. You can click on "Clear All" to remove the list or change the source range to add or subtract items from your drop-down list.
Related coverage from How To Do Everything: Tech:
How to make a pie chart from your spreadsheet data in Microsoft Excel in 5 easy steps
How to make a line graph in Microsoft Excel in 4 simple steps using data in your spreadsheet
How to add a column in Microsoft Excel in 2 different ways
How to hide and unhide columns in Excel to optimize your work in a spreadsheet
How to insert multiple rows in Microsoft Excel on your Mac or PC