It can often be incredibly time-consuming to search for a specific entry in an Excel table by hand, which is where VLOOKUP comes into play. This practical function allows you to find the exact value for a specific search criterion. The VLOOKUP function is indispensable for managing price lists, members directories, and inventory catalogues. To ensure you can benefit from this practical function,...
Excel drop-down lists make working with spreadsheets easier. Instead of having to type the same values and items repeatedly, you can quickly click the right value from a drop-down list. This also simplifies the use of forms you create for other users. The drop-down lists prevent other users from making calculations impossible as a result of incorrect entries.
Theoretically, once a drop-down list has been created, it can work endlessly. That said, over the course of your work with Excel, you might occasionally need to expand drop-downs or otherwise change them. What options are available to you for this?
Editing drop-down lists in Excel
Every drop-down in Excel requires you to compile a list in the same workbook. This list should ideally be formatted as a table so that when you change the entry in a table cell, this change is immediately visible in the linked drop-down list. It is a different matter when you want to insert or delete cells. For these changes to also be visible in the drop-down list, the linked area has to be adapted.
For this purpose, select the cell (or cells) using the drop-down function and go to Data Validation in the Data menu area. Under Source, you then have to reconfigure the area. This is done by either directly typing in the new reference or highlighting it using the select function. The changes will then also be visible in the drop-down list.
Changing error alerts and input messages in Excel drop-downs
Data Validation provides even more options for editing drop-down lists in Excel. To use these, you have to access the validation options’ other tabs: Input Message and Error Alert. The input message lets you display information. This is particularly important when third parties are going to work with the file. For instance, you can use the message to explain how to use the drop-down list correctly or what its purpose is. When the user clicks the cell containing the drop-down list, the information will appear in a yellow box.
In contrast, the Error Alert enables you to provide feedback on incorrect entries. In principle, a drop-down list prevents invalid entries because users are meant to select the desired value from the list. However, different entries can be made through the formula bar. If a value is written in the row and is not in the list, an error will occur. The error alert gives you the option of explaining to other users why the error occurred and the correct procedure to follow. You can also choose from three different types of error alert:
- Stop: The error alert is displayed and the incorrect entry is not permitted.
- Warning: The error alert is displayed and the user is given the option to allow or undo the error.
- Information: The error alert is displayed and the incorrect entry is adopted in the cell anyway.
Adapting the appearance of drop-down lists in Excel
The appearance of drop-down lists in Excel can also be adapted. This is done like with any other cell: First, right-click the cell containing the drop-down list. Then select Format Cells… in the context menu, where you will be able to adapt the cell’s appearance:
- Number: If your drop-down list should include numbers, you can change how these are displayed.
- Alignment: If the text isn’t going to be aligned horizontally, this option lets you change the alignment and direction of the text.
- Font: The font, font style and font size can be adapted in this menu item.
- Border: If you want to make the drop-down list stand out with a border, you can change the size and appearance of the line here.
- Fill: Filling the cell with a color of your choice is another way of emphasising it.
For more information on "Google Sheets: how to add a drop-down list", consult our Digital Guide article on the topic.
HiDrive Cloud Storage with IONOS!
Based in Germany, HiDrive secures your data in the cloud so you can easily access it from any device!