Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»How to Create Drop down List in Excel

How to Create Drop down List in Excel

Jabin ManandharBy Jabin ManandharNovember 5, 2022
how-to-create-drop-down-list-in-excel

Dropdown lists are particularly useful when you want to restrict the type of values a user can input on a specific cell. Additionally, you can even display an error message when someone tries to enter a custom value.

You can create a dropdown in different ways; with a cell range, named range, table, or manually. No matter which method you use, all of them can be done using the Data Validation feature.

Once you create a dropdown list, a user can choose between different options by clicking the arrow icon on the cell’s right side. So, without further delay, let’s get into the nitty-gritty of how you can create one and edit the options once you create it.

Table of Contents

  • How to Create a Drop-down List in Excel?
  • How to Edit the Dropdown List Options?
    • For Cell Range
    • For Named Range
  • How to Display an Error Message if the User Enters an Invalid/Custom Value?
  • Related Questions
    • How to Create a Searchable Drop-down List?

How to Create a Drop-down List in Excel?

To create a drop-down, you need to have a list of values you want to display. For your convenience, you can even place the dropdown values in a separate worksheet and refer to cells from your worksheet.

Here’s how to do it.

  1. Open the Excel worksheet. If you use Excel online, sign in to your Office 365 account.
  2. Select the cell (s) where you want to have the dropdown menu.
    Select-cell-range
  3. Click Data Validation under the Data tab (inside Data tools)
    Data-validation-option-in-Excel
  4. Select the Settings tab, and choose the List option below the Allow field.
    Allow-List-Option-on-Excel-Data-Validation
  5. Use one of the following methods to choose dropdown values.
    • Manually enter values: This method is particularly useful when your list contains very few options. For instance, Yes/No, True/False, etc. 
      Here,  you manually enter the dropdown values separated by a comma. However, the options are case-sensitive. (Apple and apple are not the same things.)
      Manually-type-dropdown-options
    • Using a cell range: You can select a cell range to include all cells inside it under the dropdown options. Here, you click the Up arrow icon on the Data Validation window.
      select-cell-values
      Then, select the range of cells that already contains values you want to display and press Enter.
    • Using an Excel table: If you have the dropdown list values inside a table, select the cells inside it that you want to include as dropdown values. To convert a particular column into a table, select it and press Ctrl/Command + T.
      Select-cell-range-inside-Excel-table
      In this figure, you see Regions instead of cells because we named the cell range as Regions.
  6. Click OK to close the Data Validation window.
  7. To use the dropdown values, click the down arrow icon and select the preferred value from the list of options.
    Choose-an-option-from-dropdown-list

How to Edit the Dropdown List Options?

Once you have a dropdown list, you can add/remove options inside it anytime later. However, you should have access to the worksheet containing those values. Also, it depends on what type of dropdown list you are using.

For instance, if you are using a dropdown inside an Excel table, you can enter the new value at the end of the table, and the dropdown options are automatically updated. Likewise, if you want to delete an option, select the cell and press the Delete key.

On the other hand, you have to update the selection of the cells manually for the dropdown list using cell range or named range.

For Cell Range

  1. To add a new dropdown value, right-click on the cell inside the list and select the Insert option.
    Insert-new-dropdown-option-in-Excel
  2. Then, select Shift cells down on the Insert window prompt. You can also choose other options according to your preferences.
    Shift-cells-after-insert-new-dropdown-value
  3. To remove a dropdown list option, select the cell and choose the Delete option.
  4. Then, choose the List option under Allow and select the new cell range with the updated dropdown list options.
    Select-new-cell-range-with-updated-dropdown-options

For Named Range

  1. Add or delete the dropdown values similar to the cell range method above (Steps 1,2,3).
  2. Now, select the worksheet that contains the dropdown list values.
    Select-named-cell-range-with-dropdown-values
  3. Then, click Name Manager under the Formulas tab.
    Select-Name-Manager-under-Formula-manager
  4. On the Name Manager prompt, click the small Up arrow icon under the Refers to field.
    click-Up-arrow-on-Name-Manager
  5. Select the cells you want to include as the new dropdown list values.
    Select-cell-range-with-updated-dropdown-values
  6. Click the Tick icon when done.
Note: To convert a cell/cell range with dropdown options into a normal cell, select it and go to Data > Data Validation. Then, click Clear All under the Settings section.

How to Display an Error Message if the User Enters an Invalid/Custom Value?

By default, Excel doesn’t display any error even if someone starts typing a custom value on the dropdown cell. However, you can alert them with an error message to prevent it.

  1. Upon the Data Validation prompt, select the Error Alert tab.
  2. Enable Show error alert after invalid data is entered checkbox.
  3. Provide a title and error message you want to display upon custom input.
  4. Choose one of the following options under the Style field.
    Stop-error-message-on-entering-custom-dropdown-value
    • Stop: It doesn’t allow the user to enter an invalid value. (only takes valid value).
    • Warning: It displays a warning prompt where users can choose to stick with the value they entered or re-enter a valid value.
    • Information: It displays the error message, but users can continue inputting any value they want.

Additionally,  you can pre-inform the user about the type of values they are expected to enter with the help of a display message.

  1. Select the Input Message tab.
  2. Tick Show input message when cell is selected checkbox.
  3. Then, enter a title and the message you want to display when the user tries to enter a value.
    Display-message-beforing-choosing-any-dropdown-value
  4. Click OK.
  5. Now, you get a little pop-up message when you hover over the cells with dropdown values.
    Popup-message-before-entering-any-dropdown-value

Related Questions

How to Create a Searchable Drop-down List?

The dropdown is supposed to help the user input an option quickly. However, when you have too many options, you have to manually scroll to search and select the preferred option from the list.

Once you start typing on the searchable dropdown list, Excel autocompletes options that match or contain parts of your search keywords. Then, you simply select the correct one.

If you use Excel online, the dropdown is already made searchable. However, on the Desktop version, only a particular group of people who are part of the Office Insider beta channel have access to this feature as of now. For those who don’t have it, you need to create a formula with functions like FILTER and SEARCH to create a searchable dropdown list.

how to
Jabin Manandhar

Jabin is an IT Graduate. Working for almost a year as a tech writer, Jabin has covered a gamut of articles, ranging from Windows troubleshooting, Android, iOS to Internet-related issues. Besides them, he is experienced in Microsoft Office programs and has written numerous articles on Outlook, Excel, and Word. Most of his work includes resolving various Outlook issues and general software fixes. You can contact him at jabin@technewstoday.com

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
how long does a cmos battery last

How Long Does a CMOS Battery Last

January 25, 2023
thunderbolt vs usb c

Thunderbolt Vs USB C: What’s the Difference

January 25, 2023
how to find hidden folder in laptop

How to Find Hidden Folder in Laptop

January 24, 2023
You may also like
how-to-connect-headphones-to-tv

5 Ways to Connect Headphones to TV

January 29, 2023
connect-airpods-to-samsung-tv

How to Connect AirPods to Samsung TV? (Step-by-Step Guide)

January 29, 2023
AIO Pump Not Working

AIO Pump Not Working? Here’s How to Fix It

January 29, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday.

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