Use the Excel function Data Validation to create an in-cell drop-down list. This is a quick and easy way to control what can be entered in a cell and thereby avoid input errors. It can also be used for customers to choose yes or no, choose a date, etc. or for the user to select an option when you build an Excel dashboard.
Download the workbook and test it out yourself! Link: How to create a dropdown list in Excel
How to create the drop-down list
Let’s test it out by creating an interactive invoice like in the image above. The user will select an account number from a dropdown and Excel automatically populates the name and address field using basic vlookup formulas.
- Start by creating your list of valid entries (A1, A2, etc.) and giving it a name. The list should be in a single column (or row) and shouldn’t have any blank cells. It’s also a good idea to keep your validation lists on a separate worksheet.
- Naming the range makes things easier later on. This will also make it possible to add items to the range at a later stage, without having to go back and update all the cells containing the drop-down lists. They will automatically point to the updated list.
Select the list of drop-down options, type a name in the Name Box and press Enter, as follows:
For this example we’ll also add the other information (name and address) we want to bring back with vlookups when the account number from the first column is selected from the dropdown:
- Go back to your document and select the cell that you want to contain the drop-down list. Now select the function Data Validation, found in the DATA tab on the ribbon:
- In the Data Validation dialog box, in the Settings tab:
In the Allow field click List.
In the Source field type equals (=) and the name you just created.
Tick “in-cell dropdown” and select “ignore blank” if it is fine for the user to leave the cell blank.
- Select the Input Message tab
If you enter a message here it will show up in a little box when the cell is selected. If you don’t want this, untick the checkbox:
- In the Error Alert tab:
- Tick the check box “Show error alert after invalid data is entered” if you want an alert to pop up if an invalid response is entered.
The default Style is “Stop”. This prevents users from entering an invalid entry.
If you change the Style to “Warning” or “Information” the user can still enter whatever they want, but they get a message telling them that their entry is invalid (or whatever message you’ve typed into the “Error message” box.
The difference between the two is the icon shown in the box:
That’s our drop-down done!
- The last step is to automate the name and address section:
This is done with a vlookup formula. The vlookup syntax is: Vlookup(what to look for, where to look for it, what column to bring back, FALSE). You add the FALSE to make sure it doesn’t just bring back the closest match if it can’t find the exact value you’re looking for.
So the following formulas will bring back columns 2,3 and 4 respectively in the range of data:
- Done! We have created the basics of an interactive invoice:
Remember to download the Excel workbook and test it out yourself