How to add drop down menu in excel

Learning how to add drop down menu in excel will give users a select number of options to choose from. Excel is a versatile and powerful tool that offers different functions to improve your work.

A drop-down menu or list is a valuable feature that provides guidelines and allows you to create forms for other users. There are different methods to add a dropdown list to your worksheet. Below you will be able to know the best options of how to add drop down menu in excel.

How to add drop down menu in Excel?

If you want to learn how to add drop down menu in excel mac, you should know that the process is simple and fast. Additionally, you can ensure that users can select an item from a list without having to type their values.

1. Open your Excel file, and on the second sheet, you will need to type the items you want to appear in the dropdown menu.

Note: If you don't want users to access items on Sheet2, you can also hide Sheet2. Right-click on the sheet tab of Sheet2 and then click Hide.

2. On the first sheet, you need to select cell B1.

3. Then, in the data tab, in the Data Tools group, you will need to click on Data validation.

4. The Data Validation dialog box will appear. In the Allow box, you will need to click List.

5. Click the Font box and select the range A1:A3 on Sheet2.

6. Then, you will need to click OK.

If you want to copy and paste a dropdown list, you need to select the cell with the dropdown list, press Ctrl + C, select another cell, and press Ctrl + V.

Instead of using a range reference, you can type items directly into the Source box.

Note: This will make your menu or dropdown list case-sensitive. If a person types yes, Excel will display an error alert.

How to add drop down menu in excel allowing other entries?

You can also create a dropdown list in Excel that allows other entries. If you want to learn this method of how to add drop down menu in excel online, you need to follow these simple steps:

1. The first thing to do is type a value that is not in the list; Excel will display an error alert.

2. To allow other entries, you will need to go to the Data tab in the Data Tools group and click on data validation.

3. A Data Validation dialog box will appear. In the Error Alert tab, you need to uncheck Show error alert after invalid data is entered.

4. In this step of how to add drop down menu in excel 2016, click OK. Now you can enter a value that is not in the list without a problem.

How to add drop down menu in Excel?

You will also be able to use a formula to update your dropdown list automatically when you add an item to the end of the list. If you want to know how to add drop down menu in excel 2019 dynamic, you need to execute the following steps:

1. On the first sheet, you need to select cell B1.

2. In the Data tab, in the Data Tools group, you will need to click on data validation.

3. The Data Validation dialog box will appear. In the allow box, you will need to click list. To continue, click the “Source” box and enter the formula: =OFFSET(Sheet2!$A$1,0,0,COUNT(Sheet2!$A:$A),1)

Note: The OFFSET function takes 5 arguments:

Reference: Sheet2!$A$1

Rows to offset: 0

Columns to offset: 0

Height: COUNTA(Sheet2!$A:$A) and Width: 1

COUNTA(Sheet2!$A:$A) counts the number of values in column A on Sheet2 that are not empty. When you add an item to the list on Sheet2, COUNTA (Sheet2!$A:$A) increases. The range returned by the OFFSET function is expanded, and the dropdown is updated.

4. Click OK. On the second sheet, you will need to add a new item to the end of the list

5. As a result, you will have your dynamic dropdown list.

Have you learned how to add drop down menu in excel? With any of the above options, you can effectively have your dropdown list in your spreadsheet.

Also, it will let you download WPS Office to create, edit and process your PowerPoint, Word, and Excel documents for free.