Playing with data is easy if you are adequately skilled in excel. In this blog, we will educate you on how to create a drop-down list in excel. Drop-down lists help users pick one value from a list of items on a spreadsheet easily. Largely used in the design of graphical user interfaces, drop-down lists play an important role in web designing too.
In excel, drop-down lists can be easily created with the help of the Data Validation function. Let us see in detail how this function can be put to use for creating a drop-down list in excel manually.
How to Add a Drop-Down List in Excel:
Applying Data Validation Function
- In Excel, you can open a new spreadsheet and enter the items in a cell range that you want the drop-down list to display.
- You can also choose to convert your list to a table by choosing a cell range and then pressing Ctrl + T together.
- Next, you can select the cell where the drop-down list has to come into effect.
- Then, you can go to the Data tab on the Ribbon (situated on the top portion of the spreadsheet).
- Once the Data Validation window opens; under the Settings tab, you can select the option of ‘List’ in the Allow box.
- Once you have chosen the option of List, further, you will need to select the ‘Source,’ which will be ideally the cell range from which the list will display values.
- You can then click on ‘OK’ for bringing the function into effect.
- You can check the boxes of ‘Ignore blank’ and ‘In-cell dropdown’ too.
Adding an Input Message
- To add an input message, you can go to the Input Message tab and enter a ‘Title’ along with an input message that you wish it to display.
- Remember to check the box which says “Show input message when the cell is selected.”
- Additionally, you can also choose an error message to display when a user tries to input anything in the cell other than the drop-down options.
Adding an Error Message
- To do this, go to the Error Alert tab, and input a Title and Error Message description accordingly.
- Also, ensure that the box is checked which says “Show error alert after invalid data is entered.”
- You can also select an error style.
In the example, we have entered the drop-down list options in column E (E2: E6).
Then Column B should have the drop-down in excel. So, we applied the Data Validation function as described in the above steps, including a display message.
We also incorporated an error message which threw an error when we tried entering a different word other than the items on the list.
Summing It Up
With excel drop-down list, it can be ensured that data entry is more accurate and quicker than manual entry. This can be especially useful when a spreadsheet is handled by multiple members to avoid manual errors.