In Excel How to Create a Drop Down List

Leave a comment

When working with Excel, you may need to create a drop-down list to make a category easier. Instead of typing manually in a cell, a drop-down list will make the users choose text or values from a list of options. Moreover, it actually works to control all data entries for important cells.

Making a drop-down list is pretty straightforward, but if this is your first time making it, it seems to be complicated. If you’re now looking for the guide to make a drop-down list, you’re at the right page, as we’ll show you some easy ways to create a drop-down list in Excel. Let’s check out our post below!

Creating a Drop-Down List in Excel, Here’s How!

Making a drop-down list in Excel requires you to do the following steps:

    • First, you need to type the entries in a new worksheet that you would like to appear in your drop-down list. That’s called the list items that you will have in an Excel table.
      Creating a Drop-Down List in Excel, Here’s How
    • Then, choose the cell in the worksheet where you want to drop-down list.
    • After that, you need to go to the ‘Data’ tab on the Ribbon and then click on the ‘Data Validation’.
    • After clicking it, a dialog box will appear and require you to choose the settings from your drop-down list.
    • On the ‘Settings’ tab, make sure to click ‘List’ in the ‘Allow’ box.
    • If you already create a table with drop-down entries, you can click in the ‘Source’ box. Click in the Source box
    • Then, you need to choose your list range.
    • You can leave the cell empty by checking the ‘Ignore Blank’ box.
    • Make sure to check the ‘In-cell drop-down’ box and then click on the ‘Input Message’ tab.
    • If you want to bring up a message when the cell is clicked, you can check the ‘Show Message’ checkbox and type a little message in the boxes. While if you don’t want to bring up a message, you can clear the check box.
      Show input message when cell is selected box
    • In the next step, you need to click on the ‘Error Alert’ tab. If you want to bring up a message when someone enters something that is not in your list, you can check the ‘Show error alert after invalid data is entered’ box. You can then pick an option from the ‘Style’ box and then type a title and message. While if you don’t want to bring up a message, you can clear the check box. Show error alert after invalid data is entered box
    • Last, click ‘OK’. Once you click ‘OK’, your drop-down list will be ready for use.

Congratulations! You successfully create a drop-down list in Excel. Once creating your drop-down list, ensure it works the way you want.

Error on Creating a Drop-Down List on Excel

For beginners, there are common issues that trigger your drop-down list showing ‘Error Alert’. Instead of choosing data entry easily, if the error alert appears, the drop-down list will not work at all.

The ‘Error Alert’ will appear if you entered an invalid data. However, the default error message will not give a great indication of what the users can actually write in the cell. To solve this error, you just simply go to the ‘Error alert’ tab once clicking ‘Data Validation’.

From here, you need to ensure that there is a checkmark in the ‘Show error alert after invalid data is entered’. After that, you can add the title and error message and select an icon if you do not like the default. Last, you can click ‘OK’. Now, the error messages will appear whenever invalid data is entered into the cell.

To make it easier for you to create data entry and more bulletproof, the ‘Input Message’ is your perfect option. So, when making a drop-down list, you can then add an input message from the ‘Input Message tab’ in the data validation dialog box.

You just simply type your input message. Then, it will appear when the users choose the cell containing the drop-down list.

How to Add or Remove Items from a Drop-Down List?

After creating a drop-down list, you may want to add or delete more items. Adding or deleting the items is pretty easy, if you set up your list source as an Excel Table, as Excel will automatically update any associated drop-downs for you.

Here’s how to add or remove items from a drop-down list!

    • To add an item, you just need to go to the end of the list and then type the new item.
    • To remove an item, you just simply press ‘Delete’.

It’s important to note, if the item you’d like to delete is somewhere in the middle of your list, you can right-click its cell and click ‘Delete and then ‘OK’ to shift the cells up.

How to Edit a Drop-Down List?

You definitely can edit your drop-down list in Excel in some ways. Here are they:

1) Based on a named range

If you want to edit a drop-down list based on a named range, here are some steps you need to do:

    • First, choose the worksheet which has the named range for your drop-down list.
    • Make sure to either add an item by going to the end of the list and type the new item or remove it by pressing the ‘Delete’ button.
    • Go to ‘Formula’ and select ‘Name Manager’.
    • In the ‘Name Manager’ box, you can click the named range you want to update.
    • Then, click in the ‘Refers to’ box.
    • On your worksheet, you need to select all of the cells containing the entries for your drop-down list.
    • Then, click on the ‘Close’ button and ‘Yes’ to save your changes.

2) Based on a range of cells

    • First, choose the worksheet which has the range of cells for your drop-down list.
    • Make sure to either add an item by going to the end of the list and type the new item or remove it by pressing the ‘Delete’ button.
    • Go to ‘Data’ and select ‘Data Validation’.
    • On the ‘Settings’ tab, you can click in the ‘Source’ box.
    • On your worksheet, you need to select all of the cells containing the entries for your drop-down list.
    • You will also view the list range in the ‘Source’ box change as you choose.
    • To update all cells which have the same drop-down list applied, you can then check the ‘Apply these changes to all other cells with the same settings’ box.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *