How to build a drop-down list in Excel

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 to type 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.

 

 

 

 

 

 

 

 

 

 

Now, you may not want other users to view the items on the new sheet. You can hide it by moving the cursor to the sheet and click the right side of the mouse. Check the options, select and click on ”Hide.”

2. Go back to the first sheet and click B1

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 ”A1:A3.’

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 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.”

3. 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.