2 Ways How To Create A Drop Down List In Excel and How to Edit It

Excel has ‘one thousand and one’ formulas and tools which can make your job easier. One of which is drop down lists, which makes you do not need to enter data one by one. Just one input, then choose according to what was created. For example, you can give ‘Yes’ and ‘No’ options with drop down lists, just create the options without typing them one by one.

How to create a drop down list in excel is easy. There are two tricks you can apply to get the scroll menu on your sheet. Come on, just follow the steps!

1. How to create a drop down list in excel manually

To make drop down list in Excel manually, you just need to enter the required category on the tools provided. The feature referred to as combo box This can be created without entering a formula via the ‘Data Validation’ option. So, so as not to be confused, just follow the steps below.

1. Run the Microsoft Excel application on your device and then open the document. It can be a new document or an existing document
2. Select cells according to the location you want to add drop down list
3. In the top menu bar, go to the ‘Data’ menu
4. Swipe sideways until you find ‘Data Tools’
5. Select the ‘Data Validation’ option to display the settings window
6. After appearing option box ‘Data Validation’, in the ‘Settings’ subsection, the ‘Allow’ or ‘Allow’ menu options will appear
7. Select the ‘List’ option
8. In the ‘Source’ section, fill in the categories as needed, then separate them with a semicolon (;). For example, for multiple choice, write a; b; c; d; etc.
9. Put a check mark on the ‘Ignore blank’ and ‘In-cell drop down’ options next to the column source
10. Set the ‘Input Message’ or ‘Error Alert’ subsection if needed. Both, are short texts that show the features drop down list active on a cell and if there is an error that does not match the validation settings
11. If so, click ‘OK’ to save the settings.

You will find an inverted arrow to the right of the selected cell. It is a sign that the cell has drop down list as arranged. The choice of category will be in accordance with the data you enter in the source column.

Don’t forget to always separate category options using a comma (,). Because, if you make a typo, the data will not appear as an option, instead it will become just one category. Besides that, you also have to check the ignore blank and in-cell dropdown options so that its inverted check mark appears in the selected cell.

2. How to create a drop down list in excel with Data Range Source

Have a lot of data and want to create drop down list? Of course, it will be complicated if you have to input one by one. Excel also has tools to shorten the data input. The trick, just use the ‘Data Range Source’ feature as a substitute input source manually in the previous point.

How to make drop down list in Excel using range source by retrieving the source of the column containing the data. You can also apply this option if the data source comes from sheet different. Here are the steps:

1. Run the Microsoft Excel application and open the document
2. After the loading process is complete, make sure you have the data that will be used as the preferred category. If not, you can make it in advance or prepare it from sheet other
3. Select cells according to the location you want to add drop down list
4. In the top menu bar, go to the ‘Data’ menu
5. Swipe sideways until you find the ‘Data Tools’ menu group
6. Select the ‘Data Validation’ option to bring up the settings window
7. Just like the previous way, after appearing option box ‘Data Validation’, in the ‘Settings’ subsection, the ‘Allow’ menu options will appear
8. Select the ‘List’ or ‘List’ option
9. To enter the ‘Source’, just type the column and row code that contains the data. For example, the data selection is in column C from rows 2 to 7. So in the source box it is filled with ? =$A$2:$C$5. Adjust column and row locations with your data.
10. Check the ‘Ignore blank’ and ‘In-cell dropdown’ options next to the column source
11. Click ‘OK’ to save the settings. All right.

You have drop down list by category of data. There are some important notes that you should pay attention to when setting up drop down list in Excel using data ranges. Well, here are the notes:

• At stage 9, always start source box with the equals symbol (=)
• Insert dollar sign ($) to lock selected columns and rows
• Use a colon (:) as an ‘until’ instruction for Excel. If you write a comma or semicolon, Excel will recognize it as an ‘and’ instruction.

Bonus: how to edit drop down list

What if you want to add category choices? You can change it in the ‘Data Validation’ window and enter additional categories in the source fields. For more details, consider the following method:

1. Select the cell you want to categorize
2. In the top menu bar, go to the ‘Data’ menu
3. Swipe sideways until you find the ‘Data Tools’ menu group
4. Select the ‘Data Validation’ option to bring up the settings window
5. In the ‘Source’ section, enter additional categories. If you follow how to make drop down list in Excel manually, then just type the additional category. If using data range, change the column and row codes according to the additional categories. For example =$A$2:$A$7 becomes =$A$2:$A$8
6. If you want to change the settings in all cells, then check the box ‘Apply these changes to all other cells with the same settings’
7. Click ‘OK’ to save the settings.

When you click on the down arrow next to a cell, you’ll find a new category that has been added. You can use this method whenever you need a new category in all cells or certain cells.

That was the way to make drop down list in Excel manually and using data range. How do you think it’s easy or difficult? Very suitable for increasing knowledge about Excel functions. Hope it helps your work.