Business, Career, Technology/Software

How to create a drop down list in Excel: step by step guide

how to create a drop down list in Excel

Image via: Pexels

The spreadsheet program Excel sometimes looks a bit complicated. But with functions such as a drop-down list, clarity can be created. Follow the illustrated guide to find out how to create a drop down list in Excel with different methods in just a few steps.

A Microsoft Excel dropdown list is worthwhile if you always want to enter the same values ​​in one or more cells or only want to allow certain values. Once the drop-down list has been set up, the desired cell entry can simply be selected from a drop-down menu.

Overview

Knowing how to create a dropdown in Excel spreadsheets can help make your work more efficient. This can be useful especially if you want your employees to provide certain information that may be relevant to the company. An Excel drop-down list gives you precise control over what can be entered into a cell by allowing users to choose from a predefined list. Also read how to use VLOOKUP in Excel with our easy guide for VLOOKUP and HLOOKUP

When you add a drop-down list to a cell, an arrow appears next to it. Clicking this arrow opens the list and allows the user to select one of the items in the list. Not only does this save you space in your table, but it also makes you look like a superuser and impresses your coworkers and boss. A drop-down menu enables you or other users to select certain values ​​or content from an existing list. This has several advantages:

  • The different values ​​can be selected with a click of the mouse, so that you do not have to type in the complete term or the whole number each time.
  • The dropdown choice means that there are no typing errors .
  • Since the values ​​are specified, it is also prevented that other users generate errors by entering values that are not intended .
  • The creation of drop-down lists gives you and other users a considerable gain in convenience and also makes many forms appear much more professional.

How to create a drop down list in Excel: 2 methods

Excel offers several ways to create a drop-down list. We will take you through the 2 most popular methods. Follow our step-by-step instructions below for how to create a drop down in Excel or go straight to the method 2: quick start guide. By the way: You can design the cells around the drop-down menu as you like. For example, you can put a heading in the cell above. Also read how to hide columns in Excel: hide, show, outsource and lock columns

How to create a drop down list in Excel: method 1

Step 1: Define the content of your list

A selection list is always in a cell. This means that it has to be formatted so that a drop-down list can be used in Excel. At the same time, however, you also need a table with values that should then appear in the selection list. It is advisable to create these on a separate sheet in the same workbook.

In this way you have the entries available at all times, but you can hide them cleverly. It also makes sense to format this list as a table. This way Excel can handle your list better and a change in the table is immediately reflected in the linked drop-down list. Also read how to enable macros in Excel: complete step-by-step guide

  1. Open a new Excel worksheet and write down the heading of the content that you want to display in your list. Make sure that each entry occupies one cell and that all entries are vertically aligned in the same column. Also, make sure that there are no blank cells between entries. In our case, our drop-down menu will open a list of cities to choose from.
  2. When you have finished compiling the list, select all entries, right-click on them and select the “Define Name” option in the menu that appears.
  1. This will open a new window titled “New Name”. Choose a name for your list and enter it in the “Name” text field.
  1. Click on “OK”.

Step 2: adding your drop-down list to a table

The next step is to add your drop-down list to a table. Find out how to do it.

  1. Open a new or existing worksheet where you want to place your drop-down list.
  2. Select the cell in which you want to place the drop-down list. Click the “Data” tab, find the “Data Validation” icon in the “Data Tools” section and click it.
how to create a drop down list in Excel
  1. A data review box will appear with three tabs: Settings, Input Message, and Error Warning. On the Settings tab, select List from the Arrow drop-down list. A new option called “Source” will now appear at the bottom of the window. Click in the text box and enter an “=” sign followed by the name of your drop-down list. In our case it should read =cities.
how to create a drop down list in Excel

The “Ignore spaces” and “Drop down in cell” check boxes are selected by default. If the Ignore Empty Cell option is checked, it means that people can leave the cell empty. However, if you want each user to select an option from the cell, clear the check box.

  1. Click OK. That’s it. You have added a drop-down list to your table.
how to create a drop down list in Excel

After this is done, you can now move on to the Input Message tab.

Step 3: Define the input message for the data verification (optional)

Sometimes you might want a message (with a description) to appear when the cell with the drop-down list is clicked. In this case, you will need to click the “Show input message” box on the Input Message tab. You must also fill in the title and input message in the appropriate fields. Your drop-down list should now look something like the following image.

how to create a drop down list in Excel

The last tab is for the error warning. Once defined, it sends an error message if someone enters invalid data – data that is not in the list.

Method 2: how to create dropdown in Excel with data review

Here we explain to you how you can do it the simplest way and that is via the “data review”.

  • Select the cells in the table in which you want to include the drop-down menu. Then click on ” Data verification … ” in the ” Data ” tab.
  • In the new window, click the arrow in the drop-down menu under ” Allow: ” next to ” Any value “. Then select the ” List ” option.
  • In the line under ” Source: ” you must add your options for the drop-down list, separated by a semicolon. Otherwise you can also specify the cell range directly.
  • Confirm with ” OK “. If you now click on one of the fields with a drop-down selection, an arrow appears to the right of it. Clicking on it shows you the choices you have entered.

How to create a drop down menu in Excel on the PC:

  • Open a new Excel spreadsheet and enter the names of the participants in column A of the document, for example.
  • On the worksheet, select the cells where you want the drop-down list to appear. In this example cells ” B 2 to B 14″ .
  • Go to the Data tab on the ribbon and then to Data Validation .
  • On the Settings tab, in the Allow box, click List .
  • Enter the five cities from the example here, each separated by a semicolon.
  • Confirm by clicking OK.

Excel: Create drop-down list on Mac

  • Creating a drop-down list in Excel on a Mac works in a very similar way. That is how it goes:
  • Open a new Excel table and enter the names of the participants in column A of the document, for example.
  • On the worksheet, select the cells where you want the drop-down list to appear. In this example cells ” B 2 to B 14″ .
  • Go to the Data tab at the top and then to Validity.
  • On the Settings tab, in the Allow box, click List.
  • Enter the five cities from the example under “Source”, each separated by a semicolon.
  • Confirm by clicking OK.

Generate dependent dropdown menus in Excel

In Excel, instead of a very long list in rows or columns, it can make sense to create a drop-down list that is based on a data source and that adapts dynamically. However, you may also want to create 2 or more lists, with each subsequent list depending on the previous one. Also read how to use COUNTIF in Excel with our guide and examples.

  1. Create a dynamic table in Excel

So that our drop-down list adapts dynamically to the data source, we first have to dynamically design the data source (s) itself. In our first example, we create a list of states and the cities belonging to them.

  • We mark our respective list.
  • Then click in the register: “Insert” on “Table”
  • And check the box next to “Table has headings” .
  • Then we assign a name to each of our tables, which should match the heading of the respective table.
  1. Create a drop-down list in Excel
  • See the step higher up in this article. To create our first drop-down list for the selection of the federal states, click in the desired cell in which the selection should be available, and then click in the “Data” tab on “Data verification”
  • There we first select “List” as the data check option, mark our first list with the states as the associated data source, and confirm with “OK” .
  • In the next step we limit the selection options of the dependent drop-down list by referring to our previously set dynamic tables.
  • To do this, we first mark the cell again in which our 2nd drop-down list should be.
  • Go to the “Data check” again via the “Data” tab
  • And select “List” there.
  • In order to set the data source depending on the selection made previously, we have to use a small function and enter “ = INDIRECT (G3) ” in the data source field. (we select the cell in which the upstream dropdown selection takes place).

It is important that we enter the cell as a relative and not as an absolute reference!

Correct: = INDIRECT (G3)
Incorrect: = INDIRECT ($ G $ 3)

With this function, the list refers to the tables we created previously.

So we select a table in the first drop-down list, and the second drop-down list looks via the “Indirect” function which entries are available in this table (exclusive heading) and only makes these available for selection according to the preceding entry.

  1. Extending a dynamic table in Excel

The big advantage of dropdown menus that relate to dynamic lists is that they automatically adapt to the length of the list entries. As soon as we add an entry, it is available for selection. And if we remove an entry, it disappears just as quickly.

To refer to the above example, we would have to create a new table (again with a name), for example to add another federal state , and simply include this in the reference to the drop-down selection.

To add new cities to the existing ones, we expand the table either by:

  • Press the TAB key in the last cell of the table.
  • By dragging the list at the lower right (colored border) while holding down the left mouse button.
  • Or by a new entry in an empty cell below the table.
  • (Here the table is usually expanded automatically, and a query appears as to whether this was desired.)
  • This procedure for the dependent drop-down lists can of course be continued.

So we could next filter for example depending on the respective city, still on the municipalities, and then on street directories.

Customization and changing the format

The width of the Excel dropdown list is determined by the width of the associated cell. Long list entries may be “cut off”. To prevent this, adjust the column width.

Tip: Temporarily select the longest entry and set the optimal column width by double-clicking the column boundary. Format cells that provide a dropdown like conventional cells. The formatting only affects the cell content, not the list.

Add accessibility and error messages

If third parties use a table with an Excel dropdown, help texts can be useful. This is made possible by the Input Message and Error Message tabs of the Data Verification dialog. Under input message you can enter a title and a message with up to 255 characters. In the error message tab, there are also three error types (stop, warning, information) available.

Tip: Test the messages by deliberately entering error values.

Removing data verification

To remove the Excel dropdowns, select the desired cell. In the Data Verification window, click the Delete All button and click OK to confirm .

Tip: It’s even faster if you copy an empty cell and paste it into the cell with the Excel dropdowns.

Formatting with colors

You would like to be able to select a value from the drop-down list in Excel from “OK / Check / Invalid”, whereupon the line turns green, yellow or red.
Solution: It’s actually very simple.

Do you want the cells to appear in green, yellow or red depending on the selected entry? First the basics: First we only edit one line and assign it all three possible conditional formatting. Only then is this formatting set copied into the remaining lines.

  • Mark the cells in the first row that contain any data that you need to color, for example cells A2 to C2. In the Start tab you go to New Rule via Conditional Formatting. For the rule type, select the entry Use formula to determine the cells to be formatted . Click in the empty field «Format values ​​for which this formula is true». Then click in the cell in which the first status entry is or could be, e.g. C2 . At first = $ C $ 2 appears in the field .
  • Important: If Excel also puts a $ sign in front of the number 2 for you, remove the $ sign! Otherwise the formula won’t work.
  • Then type in the string = “OK” so that the formula reads something like = $ C2 = “OK” . Now it’s time to format . Select the way in which the cells should be formatted in the event of the value «OK». For example, choose the background color green under Fill . Click OK .
  • Stay right on the first line. Open Manage Conditional Formatting / Rules . Click Use new rule / formula to determine which cells to format . Another click in the formula field, followed by a click in the field with the first status value (e.g.) C2 . This time you complete the formula differently. It is now, for example, = $ C2 = “check” . Via formatting you can now get about the background color yellow . Click OK .
  • In exactly this way you miss a third rule: Use a new rule / formula to determine the cells to be formatted , click in the formula field and in the first status field, this time add the entry for red: = $ C2 = “invalid “. Format this variant appropriately, for example in red. Click OK . It now looks something like this.

These rules initially only apply to the first row or, in our example, to cells A2 to C2. Is the already formatted line still marked? If not, select the cells. Then click on the tab starting at the very beginning when the field “clipboard” on brush icon for the Format. This means that conditional formatting can also be transferred. If you mark the cells below, they will be colored according to the value in column C.
If the cells all appear the same color, you forgot to delete the $ sign in the right place.

You will probably also not like ugly complete coloring. So …. Instead of a background color, you can also choose a font color, if necessary together with a font such as “bold”. That hurts the eyes less!