Creating a drop-down list in a Workbook can make the task of entering data from a set range of options much quicker, both for yourself and for anyone else who has to work with the document. How you create a drop-down list in Excel isn’t immediately obvious, but once you know the process, it will become an Excel skill you will always have at hand.
In This Lesson…
Create Your Drop-down List
In this example, we will be creating a simple spreadsheet for recording bookings at a nail salon, with a drop-down list used to enter some of the booking details. As you can see, our Workbook has four columns for recording booking details. Only the final column will have a drop-down list.
1 – The first thing you need to do is create the list of the different data values you want in your drop-down list. You can do this on the same worksheet as the booking form, but it is better to create a second sheet and input the data there.
2 – Click the + button next to the worksheet name at the bottom of the Excel window to create a new Worksheet. You can rename your second sheet whatever you like. We have called ours “Lists”.
3 – On the new sheet, enter all of the values you want in your first list in a single column. In our example, these are appointment times. Select the full list, and convert it into a table by pressing Ctrl + T.
4 – Your table doesn’t need headers, so leave that box unchecked. Copy the cell range shown in the create table box, then click Ok. You will need the cell range information in a moment, but for now, you can just paste it into an empty cell.
5 – Go back to the bookings sheet, select all of the cells in the column where you want the drop-down list to appear. Click the Data tab, then click Data Validation in the ribbon.
6 – In the Data Validation window, change the Allow option to ‘List’, and then click inside the source box. This is where you need to enter the cell range you saved earlier. Because the data is in a different sheet, you also need to add the sheet name. For our example, this looks like: =Lists!$A$2:$A$11
7 – Select the Input Message tab and enter a title for the dropdown list and an input prompt/message to help anyone using the booking form. The Error Alert tab lets you set up a warning if invalid data is entered.
8 – When you click OK, you will see the drop-down list button appear next to the cell. Click this to see the available input options.
How to Hide Worksheets
Because others may be using the booking form in this example, we don’t particularly want them to be able to access the Lists sheet, as they might accidentally edit information, or even delete the sheet altogether.
To avoid this, you can simply hide the sheet that contains the list data. To do this, click on the sheet name tab and select ‘Hide’ from the menu.
The dropdown will continue to work in exactly the same way, but the sheet containing the list data table can’t easily be seen or edited.
2 Comments
Pingback: How to Create Formulas – Excel for Beginners - Novus Skills
Pingback: Create a Chart From Your Data in Excel - Novus Skills