How to build a drop-down list in Excel

The drop-down list is one of the most useful items in Excel. The drop-down list simplifies data analysis because it allows users to choose items from a list instead of wasting time typing the values. This tutorial will reveal the simplest method of creating a drop-down list on Excel.

1. Here comes the first step in creating a drop-down list. Locate the sheet that you wish the drop-down list to show and type all the items that you wish to include in the drop-down list. Create a list of items you want to include in the drop-down

2. Click on an empty cell 

3. Check for the data tab. Go through the ''Data tools group" and click "Data validation."

4. The dialog box for "Data validation" will appear immediately.

5. Locate the "Allow box" and click "List."

6. Locate the "source box" on the second sheet and click the range $D$2:$D$7

7. click ok, and the result will display.

You can copy or paste a drop-down list. To do this, select the cell that contains the drop-down list and hit "CTRL + C". Go ahead and click a new cell and hit "CTRL + V."

8. You may decide not to use a range reference; hence, you have to enter the items into the "source box

It is pertinent to note that the drop-down list is case-sensitive. A pop-up will show up indicating an error if a user types "yes."

Excel will allow you to customize your drop-down list so that you can enter other entries on the list. Now, let's see how it goes:

Excel displays an error alert whenever you enter a strange value in the list.

To achieve this, you have to follow the steps below:

Locate the "Data tab" and scroll to the "Data tools group." Select the option, "Data validation." The dialog box will pop up immediately.

Proceed to the "Error alert tab."

9. Uncheck the box named, "Show error alert after invalid data is entered."

Click Ok, and within a jiffy, you will be able to type a strange value into the drop-down list.

Record Menu Options Manually

Manual recording is efficient. Follow the following steps:

1. Highlight the cells you need to insert the drop-down menu.

2. Click on the Data Validation menu in the Data tab.

3. Click on List to allow a dropdown menu 

4. In the source box, record the values and separate them by commas.

5. Click OK to save your data.

Use Data Range Referral

You can use the data range referral method if you want to make changes to your list later.

1. Select the list of values from your excel workbook.

2. Choose cells you want to make a list, and go to the Data Validation option on the Data tab.

3. You will see a settings screen appear, select the option LIST on the ALLOW box

4. Go to the source box, and select the range of cells that make your list.

The data range method will help you to edit and adjust data on your workbook.

5. Press OK to complete the process.

Future Proofing on Excel.

The benefit of an excel table is you can update it. Data can automatically expand if extra data is added. Follow the following steps:

1. Open your excel workbook and enter your list. Enter your heading first.

2. Go to Insert>Table and create an excel table. You can type CTRL+T.

3. Click OK after the dialog box appears.

4. After data is updated, an outline around the data will form with a triangle on the lower right, and Table Tools will appear in the excel ribbon.

5. Click OK on the Data Validation table.