Drop-down menu in Excel: help with the program, instructions, features

Microsoft's spreadsheet editor from Microsoft has a huge list of features and capabilities. Therefore, it is not surprising that in the cells of the created table you can easily create a drop-down menu in Excel with data substitution. This article will discuss the options for performing this task.

How to make dropdown menu in Excel

You can create a drop-down list in various ways. Below we will consider four methods that are somewhat different from each other. For example, you can enter list values ​​manually by writing them through the punctuation mark "Semicolon", or you can select values ​​from a pre-created table by specifying the appropriate range of cells. It is possible to make this a more complicated method, which implies the creation of a Smart Table or a named range. In general, now everything will be discussed in order.

Method 1: enter the values ​​manually

To add a drop-down menu to a cell in Excel, you can manually enter data into the list. This method is relevant when you are sure that during the entire use of the table the data does not have to be modified.

Algorithm of actions:

1. Select the cells in which you want to create a drop-down menu.
2. Click on the "Data" tab.
3. On the toolbar, click "Data Validation", this button is located in the "Work with Data" group.
4. In the window that appears, select "List" from the "Data Type" list.
5. In the "Source" column, enter the values ​​that will be displayed directly in the drop-down menu. Be sure to enter them separated by semicolons.
6. Click OK.

After that, drop-down lists with the specified values ​​will appear in the selected cell range.

Method 2: select a range of values

You can create a list in Excel with data substitution in another way, which implies using data from a specified range of cells as values. The necessary algorithm of actions:

1. Select the cells in which you want to create a list.
2. On the ribbon, click "Data Verification".
3. From the "Data Type" list, select "List".
4. In the "Source" column, click on the button to the right and select the block of cells with the cursor, the data in which will be displayed in the list.
5. Click OK.

After that, drop-down lists with the selected values ​​will immediately appear.

Method 3: select a named range

In order not to select a range of cells, constantly switching from the input mode, you can create a named range and select it. This is done easily:

1. In any place of the sheet or on another sheet, create a table with a list of values ​​that you want to add to the drop-down list.
2. Highlight it.
3. To the left of the formula bar, click on the namespace.
4. Enter a new name for the range to create.
5. Press Enter.

The named range has been created, now it needs to be applied to display the drop-down lists. To do this, when creating the list itself, in the "Data Validation" window in the "Source" column, put an equal sign (=) and immediately after it enter the name of the range that you just created. After that click “OK” and the lists will be created.

Method 4: use a smart table

If desired, you can create a "smart" table. It is good because in the future it will be easy to make adjustments to it. It is done as follows:

1. On another sheet of the book or in another book in general, enter the values ​​that will be displayed in the drop-down list in the column.
2. Select them.
3. On the main page in the "Styles" section, select the "Format as table" item.
4. In the window that appears, select your favorite style.
5. A window will appear in which you will need to uncheck the “Table with a heading” item if it is not.
6. Click OK.

Having selected the created table, you will see that it was automatically assigned a name. This is what you need to enter in the "Source" field in the "Data Validation" window.

Working with a dropdown menu in Excel

So, we examined four ways how to create a drop-down list, now we’ll figure out how to use it. This is possible in the following way:

1. Hover over the combo box and do nothing. A cloud will appear in which a hint will be written. However, this will only happen if you enter values ​​in the data validation window.
2. Pointing the arrow at the cell, you will also see a downward arrow to the right of it. After clicking on it, the list itself with the given values ​​will be displayed directly.
3. To select the desired option from the list, you just need to click on it with the left mouse button.
4. Values ​​from the list can also be entered manually. However, if an error is made, and it occurs when you enter an item that is not in the list, a window will appear informing you of this.

It's all. As you can see, using the drop-down menu is not so difficult, but sometimes there is a need to perform some special functions, for example, adding or removing items. Now it will be told how to do it.

Adding New Values ​​to the List

If you plan to use the created table for a long time, then at some point you may need to add new values ​​to the drop-down menu in Excel, because creating it again is not the most reasonable solution. However, depending on what method you used to create the list, the procedure for adding new values ​​is different, so we will analyze each one individually.

Method 1: Manual Entry

If you have chosen the manual method from the manual on how to create a drop-down menu in Excel, then to add new values ​​to this menu you need to do the following:

1. Select cells that have drop-down lists.
2. On the toolbar, click Data Validation.
3. In the window that opens, to add new arguments to the list, you need to enter these same arguments in the "Source" field through a semicolon (;), and then click on the "OK" button.

Now you can verify that the newly entered values ​​have been successfully added.

Method 2: add to the normal range of values

If when creating the menu as a source you specified directly a separate range of cells, then to add new values ​​you need to do the following:

1. Go to the sheet on which these same values ​​are located.
2. Select the cell that is in the center of this list.
3. Click the right mouse button.
4. From the list that appears, select "Paste."
5. In the new window that opens, select "Line" and click "OK."
6. A new blank line will be added.
7. Enter the new value that you want to see in the drop-down list.

After that, in all the drop-down menus you can select the item just added. Thus, you don’t even have to open the “Data Validation” window.

Method 3: add to a smart table or named range

If you created a drop-down menu using a named range or a smart table, then adding new values ​​will be as easy as shelling pears:

1. Go to the table with the values ​​themselves.
2. Place the cursor in the first cell immediately after the list.
3. Enter there a new menu item.
4. Press Enter.

That's all, the new item will be added to the drop-down list.

Removing values ​​from a list

With the addition of new values ​​figured out, but what if you need to remove some value from the drop-down menu in Excel. Like last time, the instructions completely depend on what method you used to create this menu.

Method 1: Manual Removal

In fact, deleting a value from a manually created list is practically no different from adding values. Here is what you need to do:

1. Select cells with drop-down menus.
2. On the toolbar, select "Data Validation."
3. In the window that appears, in the "Source" field, delete the unnecessary value. It is important to delete it along with a semicolon.
4. Click OK.

Once you do this, check the list and you will find that the deleted item has disappeared.

Method 2: remove from range and smart table

If removing a value from a manually created list is simple, then it is even simpler from a list created using a smart table or named range. Here's what you need to do:

1. Open the sheet on which there is a list of items directly in the drop-down menu.
2. Highlight an unnecessary value.
3. Click the right mouse button.
4. From the drop-down menu, select "Delete".
5. In the submenu that appears, select "Table Rows."
6. Click OK.

The row will be deleted. Accordingly, the item from the menu will also be deleted.

Complete removal of the dropdown menu

Now we will figure out how to remove the drop-down menu in Excel completely. In fact, there are a huge number of ways to do this, but now we will consider one that involves deleting the drop-down lists themselves directly, while the formatting and already selected values ​​will remain untouched. So here is what you need to do:

1. Holding down the left mouse button, select the entire range of cells of the drop-down menu that you want to delete.
2. Click on the "Data" tab.
3. On the tool ribbon, click on the "Data Validation" button.
4. In the window that appears, select the "Clear All" item.
5. Click on the “OK” button or simply close the window by clicking on the red X.

After that, all lists will be deleted, and the previously selected values ​​will remain in their place. Now you know not only how to create and manage a drop-down menu in Excel, but also how to remove it.