Introducing how to create, link, add, edit, and cancel items in an Excel pull-down

In some cases, the types of words that can be entered when creating tables in Excel, such as product management tables and lists, are limited. In such cases, use the pull-down function to prevent input errors and improve work efficiency. Ever wondered how to create or edit a pulldown function? In this article, we will explain in an easy-to-understand manner how to use the pull-down function in Excel.

Table of contents

What is a pull-down menu (drop-down list) in Excel?

Excel’s pull-down menu is a function that allows you to enter data simply by selecting one of the input candidates prepared in advance. If the data to be entered in a cell is limited to some extent, it is possible to prevent input errors such as typos and conversion errors, which helps improve work efficiency.

How to create a pull-down menu in Excel

How to create a pull-down menu in Excel.

How to create 1. How to create from scratch

Here’s how to create a pull-down menu from scratch.

STEP1. Select the cell to set the pull-down menu

For example, let’s say you want to create a pull-down list in C3. Select any cell. Empty cells are fine.

STEP2. Select “Data” > “Data Validation Rules” > “List”

Select “Data” > “Data Validation” > “List” from the menu bar.

STEP3. Enter the options from the list in the “Original value” field

Enter the choices in the Original value field, separated by commas. (Example) “Tokyo, Kanagawa, Chiba, Saitama”

STEP4. Click the specified cell to select and enter data

If you select the specified cell, “▼” button will be given. The list shows the choices you entered in STEP3.

Creation method 2. How to prepare and then create a list item of items

The method of preparing an arbitrary item list first and creating a pull-down menu is as follows.

STEP1. List the words you want to use as a pull-down menu

For example, I entered the name of the fruit in column I, rows 2-5. Of course, this can be done not only in the same sheet but also in another sheet.

STEP2. Select the cell to set the pull-down menu

Select any cell.

STEP3. Select “Data” > “Data Validation” > “Original Value”

As before, select “Data” > “Data Validation” > “Original Value”.

STEP4. Drag the list in STEP1 and select the range

If you select the range from I2 to I5, it will be displayed with absolute reference to the original value item. Click OK at the end. You are now ready to go.

STEP5. Click the specified cell to select and enter data

When you click the specified cell, you can select it from the pulldown

How to operate using shortcuts

The pull-down can also be operated with the keyboard shortcut function. This is convenient when you want to select and enter characters from the same character string as before in a column in which some character strings have already been entered. There is no need to prepare a separate list.

The commands are:

How to add/edit items in Excel pull-down menu

Next, we’ll look at how to add and edit pull-down menu items. Select the cell where the pull-down menu is set, and check the input method of the list with “Data” > “Data Input Rules”. At this time, if “List” is selected and data has been entered in the “Original Value” column, you can add or edit the necessary items there. Also, if the pull-down menu is listed, add or edit the list. If the range of cells has expanded due to the addition of items, select “Data” > “Data Validation” > “Original Value” and select the range of the list again.

How to link pull-down menus in Excel

You can also use functions to link multiple pull-down menus. Some people may not be good at using functions, but the method using the INDIRECT function does not require any difficult knowledge. This time, I made an item about a restaurant menu as an example.

If you select “set meal” for the first cell, select from “grilled fish set meal”, “yakiniku set meal”, and “sashimi set meal”. If you choose “Don”, you can choose from “Beef bowl”, “Oyakodon” and “Unadon”. If you choose “Noodles”, you can choose from “Kitsune Udon”, “Kakesoba”, and “Ramen”. For example, if you select “set meal”, “beef bowl” and “ramen” will not be included in the pull-down menu.

INDIRECT function × How to create using the name definition

Here’s how to create one using the INDIRECT function and a name definition:

STEP1. Create a list to use

This time, specify “set meal”, “rice bowl” and “noodles” in “menu”.

STEP2. Drag the range and register “Formula” > “Define Name” > “New Name”

First, register the name of the “Menu”. Drag and select “set meal”, “rice bowl”, and “noodles” that are the breakdown of the menu. Register the name of the data range with “Formula” > “Define Name” > “New Name”.

STEP3. Select the range of the list of individual products in the same way as STEP 2 and register “New name”

Drag “grilled fish set meal”, “yakiniku set meal”, and “sashimi set meal” in “set meal”. Follow the same procedure as in STEP2, and name it “set meal”.

Follow the same procedure to register “Donburi” and “Noodles”.

STEP4. Confirm list registration with “Formula” > “Define Name”

You can check whether the previous list registration has been completed without problems by selecting “Formula” > “Define Name”.

STEP5. Select the cell (B3 in this case) from the pull-down menu, select “Data” > “Data Input Rules” > “Type of Input Value” as “List” and enter “Original Value”

Let’s assume a case where a menu name is entered in B3 and products are entered in C3 according to the contents. First, select the cell (B3 in this case) in the pull-down menu. Set “Data” > “Data Validation Rules” > “Type of Input Value” to “List”, and select “Original Value” from the name you registered earlier. This time, I used “= menu”. Now you can select “set meal”, “rice bowl” and “noodles” in this column.

STEP6. Next, select the product cell (C3 this time), “Data” > “Data Input Rules” > “Input Value Type” set to “List” and “Original Value” set to “=indirect( B3 ) “

Next, select the product cell (C3 in this case). Set “Data” > “Data Validation Rules” > “Input Value Type” to “List”. This time, I used ” =indirect(B3)” to link with “Menu”. Inside the parenthesis, specify the cell selected in STEP5. Then, the product options are displayed according to the selected menu as follows.

How to create using OFFSET function × MATCH function

If it is difficult to define the list name each time because there are many lists, it is convenient to use the OFFSET function and the MATCH function together.

STEP1. Create a list to use

This time as well, we will prepare a list to express the products linked to the menu. Created by E2:H5. The previous list has been slightly modified, and one menu is linked to products from 1 to 3. For example, you can confirm that the E3 set meal is linked to the “grilled fish set meal”, “yakiniku set meal”, and “sashimi set meal”.

STEP2. Create a pull-down list for major menu items

Select B3, set “Data” > “Data Entry Rules” > “Input Value Type” to “List”, drag “Original Value”, and select the “Menu” category “Set Meal” and “Rice Bowl”. Select “Noodles” by dragging.

STEP3. Select a product that is a sub-item

For example, if you select the “set meal” menu in cell B3, you want a grilled fish set meal, a yakiniku set meal, or a sashimi set meal. Also, if the menu is changed to a bowl, I would like the menu linked to the bowl to be displayed. To display multiple elements like this, use the OFFSET and MATCH functions.

In this case, it will be “=OFFSET($E$3,MATCH($B$3,$E$2:$G$2,0)-1,3)”. The OFFSET function selects numerical values ​​referring to the data found by the MATCH function. Then, the MATCH function searches the specified list for the one that corresponds to the character string in B3.

How to color the pull-down menu in Excel by condition

Here’s how to color the drop-down menu.

STEP1. Drag to select the range of cells you want to color

Drag to select the range of cells you want to color.

STEP2. Select “Home” > “Conditional Formatting” > “New Rule”

Select Home > Conditional Formatting > New Rule.

STEP3. Select “Format only cells that contain” from the “New Rule” menu.

Select “Format only cells that contain” from the “New Rule” menu.

STEP4. Under “Format only the following cells”, select the following in order from left to right:

“Specific string”, “Contains the following value”, and in the right cell, enter the word you want to change the color in the pull-down menu.

STEP5. Select “Format”, open “Format Cells” > “Fill” and add your favorite color

Select “Format”, open “Format Cells” > “Fill” and add a color of your choice.

For example, I chose red this time. I think that the color change was confirmed as follows.

How to cancel (delete) the pull-down menu in Excel

Let’s see how to remove the pull-down menu when it is no longer needed.

STEP1. Select the cell to release the pull-down menu

First select the cell where you want to unlock the pull-down menu.

STEP2. Select “Data” > “Data Validation” > “Clear All”

Select Data > Data Validation > Clear All to remove validation rules.

Excel’s pull-down menu function is also available in Google Sheets

Google Sheets also has a pull-down feature. Next, the specific usage in the spreadsheet is as follows.

STEP1. Select the cell you want to use the pull-down menu

First select the cell you want to use the pull-down menu.

STEP2. Select “Data” > “Data Validation Rules”

Select “Data” > “Data Validation Rules” from the top tab.

STEP3. Select “Condition”

At this time, you can also select with “Specify the range of the list”. If you select “Specify list directly”, you can directly enter the conditions.

STEP4. Select “Save”

Select “Save” and you’re done.

Summary: Let’s make full use of Excel’s pull-down menu

In this article, I explained how to use pull-downs in Excel and spreadsheets. The pull-down function is very useful when entering data with limited options. It also helps to improve work efficiency and prevent input errors. Coloring by condition makes the table easier to see. By all means, let’s master the pull-down function by referring to this article.