How to edit drop-down lists in Excel
Theoretically, once a drop-down list has been created in Excel, it can work endlessly. That said, over the course of your work, you may wonder “how do I edit a drop down list in Excel?.” We explain what options are available to achieve this.
Excel with Microsoft 365 Business and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 package!
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.
The following instructions apply to both the Excel version of Microsoft 365 and the Excel versions 2021, 2019 and 2016 as well as the Excel version of Microsoft 365!
For this purpose, select the cell (or cells) using the drop-down function and then follow these steps:
- Go to “Data Validation” in the “Data” menu.
- 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-down lists¶
Data Validation provides even more options for editing drop-down lists in Excel. Simply click on the other tabs in the “Data Validation” section:
Input Message: The input message feature allows you to make comments. This is especially important if third parties will be working with the file. In the comment you can explain how to properly use the file in detail or give more information about the drop-down list. If the user clicks on the cell with the drop-down list, the comment appears in a yellow box.
Error Alert: The error alert menu gives you the possibility 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 what the correct procedure is 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 so you start by right-clicking 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.
Microsoft 365 for Business with IONOS!
Powerful Exchange email and the latest versions of your favorite Office apps on any device - get started with our free setup assistance.