How to create drop-down lists in Excel
I love using dropdown lists in Excel! They are very simple to create and are a great way to make spreadsheets easier to use. In this article, I’ll first show how to use data validation to create an in-cell dropdown, and then I’ll show some examples of what awesome things you can do with dropdowns.
Create a drop-down list in Excel video tutorial:
Create a simple dropdown list:
You can create a cell drop-down list in Excel by following these 4 simple steps:
- Select the cell or range of cells where you want to add the drop-down list.
- Go to Data > Authentication > Settings tab
- Select “List” from the Allow: drop-down box
- Enter your list in the Source: field, use commas to separate items, or select a range of cells from the worksheet.
Use the scope to define the drop-down list:
Instead of manually entering a list of items in the data validation dialog, you can reference a range of cells. For example, let’s say I have a separate worksheet with my lists defined in cells A1:A3 as shown below. In this case, I named the scope “myList”. You can hide the sheet that contains your list later to keep your workbook looking clean or to prevent users from changing the list.
Copy and paste drop-down lists in Excel
When you copy and paste cells, data validations are also pasted, but you can’t copy and paste data validations using the Format Painter. Conversely, if you just want to copy and paste the dropdown list (not formulas or formatting), then after copying the cells, use paste special and select the validation option。