Как сделать dropdownlist в excel?

Create a drop-down list

You can help people work more efficiently in worksheets by using drop-down lists in cells. Drop-downs allow people to pick an item from a list that you create.

In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table. If you don’t, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T.

Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don’t need to do anything else.

Now is a good time to Sort data in a range or table in your drop-down list.

Select the cell in the worksheet where you want the drop-down list.

Go to the Data tab on the Ribbon, then Data Validation.

Note: If you can’t click Data Validation, the worksheet might be protected or shared. Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try step 3 again.

On the Settings tab, in the Allow box, click List.

Click in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don’t want that to be a selection option:

If it’s OK for people to leave the cell empty, check the Ignore blank box.

Check the In-cell dropdown box.

Click the Input Message tab.

If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.

Click the Error Alert tab.

If you want a message to pop up when someone enters something that’s not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.

Not sure which option to pick in the Style box?

To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, click Information or Warning. Information will show a message with this icon and Warning will show a message with this icon .

To stop people from entering data that isn’t in the drop-down list, click Stop.

Note: If you don’t add a title or text, the title defaults to «Microsoft Excel» and the message to: «The value you entered is not valid. A user has restricted values that can be entered into this cell.»

After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries.

If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.

If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.

To delete a drop-down list, see Remove a drop-down list.

You can download an example workbook with multiple data validation examples like the one in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples.

Data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.

Start by making a list of valid entries on a sheet, and sort or rearrange the entries so that they appear in the order you want. Then you can use the entries as the source for your drop-down list of data. If the list is not large, you can easily refer to it and type the entries directly into the data validation tool.

Create a list of valid entries for the drop-down list, typed on a sheet in a single column or row without blank cells.

Select the cells that you want to restrict data entry in.

On the Data tab, under Tools, click Data Validation or Validate.

Note: If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.

Click the Settings tab, and then in the Allow pop-up menu, click List.

Click in the Source box, and then on your sheet, select your list of valid entries.

The dialog box minimizes to make the sheet easier to see.

Press RETURN or click the Expand button to restore the dialog box, and then click OK.

You can also type values directly into the Source box, separated by a comma.

To modify the list of valid entries, simply change the values in the source list or edit the range in the Source box.

You can specify your own error message to respond to invalid data inputs. On the Data tab, click Data Validation or Validate, and then click the Error Alert tab.

In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table.

Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don’t need to do anything else.

Now is a good time to Sort your data in the order you want it to appear in your drop-down list.

Читать еще:  Отчет по устойчивости excel как сделать

Select the cell in the worksheet where you want the drop-down list.

Go to the Data tab on the Ribbon, then click Data Validation.

On the Settings tab, in the Allow box, click List.

If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. However, do not include the header cell. Just include the cells that should appear in the drop-down. You can also just type a list of entries in the Source box, separated by a comma like this:

If it’s OK for people to leave the cell empty, check the Ignore blank box.

Check the In-cell dropdown box.

Click the Input Message tab.

If you want a message to pop up when the cell is clicked, check the Show message checkbox, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.

Click the Error Alert tab.

If you want a message to pop up when someone enters something that’s not in your list, check the Show Alert checkbox, pick an option in Type, and type a title and message. If you don’t want a message to show up, clear the check box.

After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries. If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list. To delete a drop-down list, see Remove a drop-down list.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Create a Drop Down List in Excel

I love using drop down lists in Excel! They are extremely simple to create and are a great way to make a spreadsheet easier to use. In this article, I’ll first show how to create an in-cell drop-down list using data validation, and then I’ll show some examples that demonstrate awesome things you can do with drop downs.

To see some of the examples from this article in action, download the Excel file below.

Watch the Video

Create a Simple Drop-Down List

You can create an in-cell drop down list in Excel by following these 4 easy steps:

  1. Select the cell, or range of cells, where you want to add the drop-down list.
  2. Go to Data > Validation > Settings tab (see image below)
  3. Select «List» from the Allow: drop-down box
  4. Enter your list in the Source: field using a comma to separate the items, or select a range of cells from your worksheet.

This approach is great for simple Yes/No options and other lists that appear only once in your spreadsheet.

The problem with this approach is that if you use this in a lot of cells and later want to update the list, you have to update all cells that use the list and there is a good chance you’ll miss one. The more elegant approach is to use a reference to a range, or even better than that — a named range.

Defining a Drop-Down List using a Range

Instead of manually entering the list of items in the data validation dialog box, you can reference a range of cells. For example, let’s say I have a separate worksheet with my list defined in cells A1:A3 as shown below. In this case, I’ve named the range «myList». You can later hide the worksheet containing your list to keep your workbook looking nice and clean or to prevent a user from changing the list.

In the data validation dialog box, instead of entering the list manually, you enter a reference to the named range in the Source field as shown below:

You could use a reference for the Source field like =Sheet2!$A$1:$A$3, but I usually prefer to name the list. Why? If you want to change the range, you only need to edit the defined name (via Formulas > Name Manager) rather than finding and editing all cells that use that particular data validation.

Note: When using a named range for a data validation list, the named range must be defined as a reference to a range of cells, or it must be a formula like OFFSET or INDIRECT or INDEX that returns a reference. If you’re thinking of getting fancy and want to define a name without a cell reference such as =<"Yes","No">, the drop-down list won’t work.

Another bit of trivia: In old versions of Excel, using a named range was the only way for a drop-down list to reference a range on a different worksheet.

Check Boxes and Star Ratings with Excel Drop-Down Lists

The font used in the drop-down list cannot be changed, so it is always just a black sans serif font. This means that you can’t show different colors and fonts within the drop-down list. What I think is awesome, though, is using Unicode Character Symbols to do fun things with drop-down lists, such as star-ratings using ★ or checkboxes using the characters √, ✔, ☐, ☑ or ☒.

Important: One of the main reasons I like to use checkbox-style drop-down lists is for compatibility and ease-of-use with Excel Online and the mobile Excel apps (Form Field checkboxes don’t work in Excel Online or mobile apps). Also, when using a touch screen device, I think the drop-down checkbox is easier and more fun to use than entering an «X». By the way, if you’d like to see Microsoft add a checkbox as a data validation option, vote for that here.

Example 1: Using a Drop Down List to create a Checkbox field

This example comes from one of my Task List templates. The Source field is just «☐,√» (without the quotes).

Example 2: Choose a Star Rating using a Drop Down Menu

For a star rating, you can use «★★★★★,★★★★,★★★,★★,★» in the Source field. This example comes from the Feature Comparison template.

Including a Blank Value and Using Relative References

An in-cell drop down will ignore blanks if you enter text manually into the Source field (like » ,Yes,No»). So, if you want a blank value as an option, use a reference to a range as in the examples below.

Usually, you will use absolute references like $C$76:$C$77 for the Source in your drop-down list. However, there may be times when you want the drop-down Source to change when you copy and paste the cell. In the example above, the drop-downs use a relative reference in the Source field (no $ signs in the reference). This makes it easy to create other checkbox examples by just copying the cells to the right.

Читать еще:  Как в excel сделать округление до 10?

Using a relative reference is important when creating dependent lists which will be shown a little later in this article.

Copying and Pasting Drop-Down Lists in Excel

When you copy and paste cells, the data validation will also be pasted, but you can’t use the Format Painter to copy and paste data validation. Instead, if you only want to copy and paste the drop-down list (and not formulas or formatting), then after copying the cell, use Paste Special and select the Validation option as shown in the image.

Customizable Drop Down Lists Using Dynamic Ranges

I like creating templates that allow a person to customize lists, such as meals for a Meal Planner or accounts for an Account Register or products for a PO with Price List.

To allow for a variable number of items within the Source range, you could use a very large range like =$A$1:$A$1000, but the drop-down would end up having a crazy amount of blanks. Instead, you can create a dynamic range that extends the list to the last value in the range.

Here is a basic example:

You can see that even though the list range $C$127:$C$133 includes two blank cells, the drop-down only extends to row 131 (the last text value in the Categories column).

Here’s how to do it:

Step 1: Create a Dynamic Named Range.

Go to Formulas > Name Manager and create a range named category_list using the following formula in the Refers To field, replacing label_cell and list_range with the appropriate cell references.

Here is the specific formula used in the example.

Step 2: Use the Named Range in the Source field for the drop-down list.

See my article «Dynamic Named Ranges» to learn more about the various formulas you can use. The formula used in the above example works well for lists that include only text values.

Dependent Drop-Down Lists Using CHOOSE or INDIRECT

A dependent drop-down list is a list that changes based on the value of another cell, which might also contain a drop-down list of its own. The Source for a drop-down list can be a formula, and that is the key to making the dependent list. As I mentioned before, the formula must return a reference, so there are only a few types of formulas that will work for drop downs. I personally prefer using CHOOSE or INDIRECT.

The example below is based on an account register where the idea is to choose categories for each transaction. The Type column contains a drop-down list that references cells C179:D179 (the labels «Expense» and «Income»). We want the dependent drop down box in the Category column to use the list of expenses if the Type is «Expense» and the list of income categories if the Type is «Income.»

Here is an example showing the CHOOSE method:

The formula uses a relative reference for the type cell and absolute references for the type_values, expense_range and income_range like this:

Alternatively, we could create dynamic named ranges called Expense_range and Income_range and then use the following formula for the Source:

You can use named ranges within the CHOOSE formula as well, so I’m not sure whether one method is better than the other. Some may argue that CHOOSE is better because INDIRECT is a volatile function, but I don’t think that matters for drop-down lists.

See the Grocery Price Book template for a practical example of how dependent drop-down lists can be used in a spreadsheet.

Fancy Dynamic Drop-Down Lists

If the previous examples aren’t fancy enough for you, my article «Dynamic Drop-Down Lists» explains how to create drop-down lists that change based on user input, the date, check number, etc.

Searchable Drop-Down Lists

If your drop-down list is really long, it can be difficult to find the item you are looking for. Google Sheets provides a great solution, though not a perfect one (yet). In Sheets, you can start typing into the cell and the drop-down list will automatically filter based upon what you type . as long as it is the start of one or more of the words in the list.

For example, let’s say your list contains the names Abe Lincoln, George Washington, Harry Truman, and J. Edgar Hoover. As soon as you type «h», the list will be shortened to Harry Truman and J. Edgar Hoover, but Sheets does not recognize the «h» in Washington.

Excel: Using a fairly complicated trick, you CAN create a searchable drop-down list in Excel. See this youtube video. If you’d like to encourage Microsoft to add a searchable drop-down list as a feature, vote for this suggestion on excel.uservoice.com.

More Examples

My article Add Cool Features to Your To Do Lists in Excel shows a few other examples, like using conditional formatting combined with a drop-down box to select a priority value in a to do or task list.

Example 1: This Homework To Do List allows you to choose a High, Medium, or Low value in the Priority column. There are also examples of this on the Task List template page.

Example 2: This Task List Template uses conditional formatting icon sets for the Priority column and a drop-down list to choose a value between 1 and 4.

More Examples: Drop-down lists are a common feature in many of my templates, including the Meal Planner, Money Manager, and many financial calculators. You can download the templates to see how the drop-down lists are created.

The Customer List Template page explains how to copy a customer list worksheet into a spreadsheet, create a drop-down list showing customer names, and then add lookup formulas to display the information for the chosen customer.

Creating a drop-down list in Excel using tools or macros

A drop-down list means that one cell includes several values. When the user clicks the arrow on the right, a certain scroll appears. He can choose a specific one.

A drop-down list is a very handy Excel tool for checking the entered data. The following features of drop-down lists allow you to increase the convenience of data handling: data substitution, displaying data from another sheet or file, the presence of the search and dependency function.

Creating a drop-down list

Path: the «DATA» menu – the «Data Validation» tool – the «Settings» tab. The data type – «List».

You can enter the values from which the drop-down list will consist, in different ways:

  1. Manually through the «Comma» in the «Source:» field.
  2. Enter the values in advance. Specify a range of cells with a list as a source.
  3. Assign a name for a range of values and enter the name in the «Source:» field.

Any of the mentioned options will give the same result.

Читать еще:  Как сделать постоянную строку в excel?

Drop-down list with data lookup in Excel

It is necessary to make a drop-down list with values from the dynamic range. If changes are made to the available range (data are added or deleted), they are automatically reflected in the drop-down list.

  1. Highlight the range for the drop-down list. Find the «Format As Table» tool in the main menu.
  2. The styles will open. Choose any of them. For solving our task, design does not matter. The presence of the header is important. In our example, the header is cell A1 with the word «Trees». That is, you need to select a table style with a header row. You’ll get the following range:
  3. Put the cursor on the cell where the drop-down list will be located. Open the parameters of the «Data Validation» tool (the path is described above). In the «Source:» field, write the following function:

Let’s test it. Here is our table with a list on one sheet:

Add the new value «Spruce» to the table.

Now delete the «Birch» value.

The «smart table», which easily «expands» and changes, has helped us to perform our task.

Now let’s make it possible to enter new values directly into the cell with this list and have data automatically added to the range.

  1. Form a named range. Path: «FORMULAS» — «Define Name» — «New Name». Enter a unique name for the range and press OK.
  2. Create a drop-down list in any cell. You already know how to do this. Source – name range: =trees.
  3. Clear the following check boxes: «Error Alert», «Show error alert invalid data entered». If you do not do this, Excel will not allow you to enter new values.
  4. Launch the Visual Basic Editor. To do this, right-click on the name of the sheet and go to the «View Code» tab. Alternatively, press Alt + F11 simultaneously. Copy the code (just insert your parameters).
    Private Sub Worksheet_Change( ByVal Target As Range)
    Dim lReply As Long
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = «$C$2» Then
    If IsEmpty(Target) Then Exit Sub
    If WorksheetFunction.CountIf(Range( «trees» ), Target) = 0 Then
    lReply = MsgBox( «Add entered name » & _
    Target & » in the drop-down list?» , vbYesNo + vbQuestion)
    If lReply = vbYes Then
    Range( «trees» ).Cells(Range( «trees» ).Rows.Count + 1, 1) = Target
    End If
    End If
    End If
    End Sub
  5. Save it, setting the «Excel Macro-Enabled Workbook» file type.

When you enter a new name in the empty cell of the drop-down list, the following message will appear: «Add entered name Baobab?».

Click «OK» and one more row with the «Baobab» value will be added.

Excel drop-down list with data from another sheet / file

When the values for the drop-down list are located on another sheet or in another workbook, the standard method does not work. You can solve the problem with the help of the =INDIRECT() function: it will form the correct link to an external source of information.

  1. Activate the cell where we want to put the drop-down menu.
  2. Open the Data Validation options. In the «Source:» field, enter the following formula:

The name of the file from which the information for the list is taken is enclosed in square brackets. This file must be opened. If the book with the desired values is stored in a different folder, you need to specify the path completely.

How to create dependent drop-down lists

Take three named ranges:

It is an indispensable prerequisite. Above you can see how to turn a normal scroll in a named range (using the «Name Manager»). Remember that the name cannot contain spaces or punctuation.

  1. Create the first drop-down list, which will include the names of the ranges.
  2. Having placed the cursor on the «Source:» field, go to the sheet and select the required cells alternately.
  3. Now create the second drop-down menu. It should reflect those words that correspond to the name chosen in the first scroll. If the «Trees», then «Linden», «Maple», etc. should correspond to it. Enter the following function: =INDIRECT(А1) in the «Source:» field. A1 is a cell with the first range.

Selecting multiple values from a drop-down list in Excel

Sometimes, you need to select several items from the drop-down list. Let’s consider the ways of performing this task.

  1. Create a standard ComboBox using the «Data Validation» tool. Add a ready-made macro to the sheet module. The way how to do this is described above. With its help, the selected values will be added to the right of the drop-down menu.
    Private Sub Worksheet_Change( ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range( «E2:E9» )) Is Nothing And Target.Cells.Count = 1 Then
    Application.EnableEvents = False
    If Len(Target.Offset(0, 1)) = 0 Then
    Target.Offset(0, 1) = Target
    Else
    Target. End (xlToRight).Offset(0, 1) = Target
    End If
    Target.ClearContents
    Application.EnableEvents = True
    End If
    End Sub
  2. For the selected values to be shown from below, insert another code for processing.
    Private Sub Worksheet_Change( ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range( «H2:K2» )) Is Nothing And Target.Cells.Count = 1 Then
    Application.EnableEvents = False
    If Len(Target.Offset(1, 0)) = 0 Then
    Target.Offset(1, 0) = Target
    Else
    Target. End (xlDown).Offset(1, 0) = Target
    End If
    Target.ClearContents
    Application.EnableEvents = True
    End If
    End Sub
  3. For the selected values to be displayed in the same cell separated by any punctuation mark, apply this module.

Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & «,» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Do not forget to change the ranges to «your own» ones. Create scroll in the classical way. The rest of the work will be done by macros.

Searchable drop-down list in Excel

  1. On the «DEVELOPER» tab find the «Insert» tool – «ActiveX». Here you need the button «Combo Box (ActiveX Control)» (focus your attention on the tooltips).
  2. Click on the icon – «Design Mode» becomes active. Draw a small rectangle (the place of the future scroll) with a cursor that transforms to a «cross».
  3. Click «Properties» to open a Combobox1 of settings.
  4. Enter the range in the ListFillRange row (manually). The cell where the selected value will be displayed can be changed in the LinkedCell row. Changing of the font and size can be done in Font row.

When you enter the first letters from the keyboard, the appropriate items are displayed. These are not all the pleasant moments of this instrument. Here you can customize the visual representation of information, specify two columns at once as a source.

Ссылка на основную публикацию
Adblock
detector