Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Create Drop-down List on Excel

    How to Create Drop-down List on Excel

    jabinBy jabinFebruary 7, 2023 Excel 6 Mins Read

    Rather than manually entering data in individual cells, you can create a dropdown list in Excel to populate cells with pre-defined options. Along with providing the options, you can also display a message to warn users whenever they try to enter a non-valid option.

    You can create various dropdown lists based on the comma-separated delimiter, cell range, named range, and table. Also, you can store them in a different worksheet so that the users can focus on the input only.

    To create one,

    1. Select the cell (s) where you want to insert the list.
      Select-cells-to-include-dropdown
    2. Select Data validation under the Data tab.
      Select-Data-validation
    3. Click the Settings tab.
    4. Under the Allow section, select the List option.
      Dropdown-list-using-Data-validation
    5. Disable the Ignore blank checkbox to warn users from leaving the cell empty while selecting it. However, if nothing is selected by default on the cell and it’s already empty, the user doesn’t get displayed any message.
    6. Enable the In-cell dropdown checkbox.
    7. Click the Up arrow icon under the Source section.
      Dropdown-list-source
    8. Now, choose one of the following methods.
      • Comma Separated Delimiter: This method is particularly useful if you only have a few options like Yes/No, and True/False. Here, you manually enter the options separated by a comma.
        Comma-delimited-dropdown
      • Cell range: Here, you simply drag across the cells which you want to include as options.
        Dropdown-based-on-cell-range
      • Named range: Instead of manually entering or selecting the cell reference of the dropdown list options, you can name such a cell range. Then, you can enter the name instead of the whole cell reference in the source section.
        Create-a-Named-range-dropdown
        To name a cell range, type the desired name in the name box located at the top-right corner of the window.
    9. Click OK.
    Quick Tip: Consider placing the dropdown list options in a separate sheet so that the user can focus on the cell input rather than the list.

    How to Create a Dropdown List Based on a Table?

    While the above two methods mentioned in Step 2 do a decent job of creating a dropdown list, a better approach is to create one that’s based on tables. The benefit of such dropdown lists is that whether you need to add or delete any of the list values later, the table updates them automatically. This means every cell based on that list will have new options.

    To create a list based on the table, 

    1. First, you need to convert your dropdown list values into a table. So, navigate to the worksheet containing the list of options. Then, select them and press the Ctrl + T shortcut. 
    2. On the Create Table window, click the Up arrow icon and select the cells containing the options. Enable the My table has headers checkbox if the options column contains a header and click OK.
      Convert-dropdown-list-options-to-a-table
    3. Additionally, you can even provide a named range for the list. To do it, select the cell range with options and provide a name in the name box.
      Create-a-named-range-for-the-dropdown-options-table

    How to Display a Message while Selecting the Cells with Dropdown Values?

    While creating a dropdown list, you can even display a message to inform what the list contains and what they are expected to enter.

    1. Select the Input Message tab. 
    2. Then, enter the preferred message. 
    3. Additionally, select the Show input message when the cell is selected option to display the message when the active cell contains the dropdown list.
      Input-message-for-Dropdown-list

    Similarly, you can alert a user when trying to enter a different option as follows:

    1. Select the Error Alert tab and enter the error message you want to display. 
      Display-error-invalid-when-entering-a-custom-option
    2. Also, enable the Show error alert after invalid data is entered checkbox.

    How to Protect the Dropdown List Data?

    If you don’t want others to edit your list options, you can protect the cells containing the dropdown lists. Surprisingly, all cells in a worksheet are locked by default on Excel.

    So, you first have to unlock the cells you want others to be able to edit, which in our case is choosing a dropdown option. Then, protect the worksheet with a password to only select unlocked cells.

    1. Select the cell range with dropdown lists and any other cells you want the users to edit.
      Select-cells-allowed-to-edit
    2. Press Ctrl + 1 to open the Format cells window and select the Protection tab.
    3. Uncheck the Locked checkbox and click OK.
      Uncheck-the-Locked-checkbox
    4. Then, under the Review tab, click Protect Sheet.
      Protect-Sheet-under-Review-tab
    5. Enable Select unlocked cells and disable Select locked cells checkboxes.
      Select-the-unlocked-cells-only
    6. Enter a password and note it somewhere safe as you have to use it to unprotect the sheet and edit the contents.
      Enter-a-password-to-protect-worksheet
    7. Click OK.

    To unprotect the sheet,

    1. Select the Review tab and click Unprotected Sheet.
      Unprotect-Sheet
    2. Use the password you set earlier when asked.

    How to Create a Dependent Dropdown List?

    While working with multiple dropdown lists, you may sometimes come across a situation where options selected on one list determines what options to display on another one. 

    In such cases, you need to create a dependent dropdown list.

    For instance, you have two dropdown lists among which one contains the laptop names. And, depending on the laptop, you have to create another list showing the respective laptop models.

    Here, the C9 cell contains the first dropdown list asking for the name of the laptop with two options; Dell and Lenovo. And, we have two names ranges among which DellModels include Dell G3 and Dell XPS. Similarly, the name range LenovoModels includes Lenovo Yoga and Lenovo Thinkpad.

    Here, we need to create a dependent dropdown list so that when the user chooses Dell for “Which laptop do you use?”, they only get options under the Dell Models. Similarly, if they choose the Lenovo option, they can choose options under the Lenovo Models.

    To create a dependent dropdown list, you can use the simple IF function.

    1. Create the first dropdown list with options for laptop names. 
    2. Next, you need to create the dependent dropdown list to show the laptop model options based on the laptop name.
    3. Select the cell where you want to create the dependent dropdown list. Here we are using the C13 cell.
      Cell-with-dependent-dropdown-list
    4. Go to Data > Data Validation.
    5. Under the Source section, use the IF function.
      =IF($C$9="Dell",DellModels,LenovoModels)
      Using-IF-function-to-create-dependent-dropdown-list
    Excel Basics
    jabin

      As a tech content writer, Jabin covers Excel-related articles at InsideTheWeb. His articles mainly involve helping new users to quickly familiarize themselves with the Excel interface and explaining various essential features. While he got introduced to Excel in his early school days, he developed a keen interest in it after working on a college project. He was impressed at how quickly one could accomplish several tasks with built-in functions like the filter function and user-friendly tools like the power query. Keeping beginner audiences in mind, he loves to explain even the most fundamental Excel concepts in detail and break down complex topics with a step-by-step approach. As an avid Excel user, he believes every task can be done a lot quicker if you know the right tools and techniques. When he’s not behind a keyboard, he loves to listen to interesting audiobooks and podcasts.

      Add A Comment
      Table of Contents
      • How to Create a Dropdown List Based on a Table?
      • How to Display a Message while Selecting the Cells with Dropdown Values?
      • How to Protect the Dropdown List Data?
        • How to Create a Dependent Dropdown List?
      Recent Posts
      • How to Lock a Cell in Excel Formula
      • How to Make Gridlines Darker in Excel
      • How to Apply Cell Style in Excel
      • How to Apply Top and Bottom Border in Excel
      • How to Retrieve Last Value in Column Excel
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.