How to edit drop-down lists in Excel
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?
Register a domain name
Build your brand on a great domain, including SSL and a personal consultant!
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 emphasizing 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 Europe, HiDrive secures your data in the cloud so you can easily access it from any device!
Related articles
UndreyShutterstock Creating an Excel drop-down list
If you regularly use spreadsheet programs to process large datasets, you will welcome anything that helps. This is where drop-down lists in Excel come in. These allow you to conveniently add content with just one click so you don’t have to type the same thing again and again. We show you how to insert a drop-down in Excel.
The Excel SUMIF function explained
Get more out of Excel: SUMIF makes it easier to work with balance sheets and analyses. Add only the values you need – completely automatically. From simple calculations to complex formulas, it’s all possible with the SUMIF function in Excel. How do you use the function? And what does the syntax look like?
Faster with Excel: the VLOOKUP function explained
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,…
ChinnapongShutterstock Deleting Duplicates in Excel: How it Works
The Microsoft program Excel makes storing and processing data a piece of cake. But the more entries are added to a table, the greater the chance that some values are duplicated in the process. Excel offers a function for removing duplicates to solve this problem with ease. This allows you to delete redundant double entries with just a few clicks.
UndreyShutterstock Excel: Find and Replace – made simple
Microsoft’s favorite spreadsheet software offers a plethora of useful features and tools. Among the most important commands are “Find” and “Replace.” We’ll show you how they work step-by-step. In addition, this article offers tips and tricks to search for unknown values using placeholders.