December 4, 2020

How to Create Drop Down in Excel

First create a list which you want to show in the drop down. The list can be created in the same sheet or different sheet.

Now select the cell where you want to make the drop down(here it’s in cell C2) and Go to Data -> click ‘Data Validation’

Now in the pop up select ‘List’ in Allow and select the column containing the list of values in Source. If you provide the entire column in source, you can add any other value in the column and that will be automatically appear in the drop down.

Click OK.

Go to ‘Error Alert Tab’. By default ‘Show error alert after invalid data is entered’ is selected. It prevents user to type any other value in that cell except selecting from drop down.

If you want to allow user to type any value other than the values present in drop down list only, then uncheck this checkbox.

Here comes the drop down in cell C2.

In this process if you change any item in the list, the cell containing drop down will be automatically updated with the new or modified list value. Like here we have updated the value ‘Option 3’ to ‘Option 30’. As you can see in the drop down list also it has been reflected accordingly.

In this process Blank value can also be added in drop down list. As in below example we introduced a blank cell(A2) in the source list in column A and as you can see the drop down list has also been updated with a blank value.

Now here is an alternate way of creating drop down as follows.

In the data validation Settings select ‘List’ in ‘Allow’ and in the ‘Source’ enter your drop down items separated by comma. Like here we are creating another drop down in cell E2 and we want there should be 4 items in our drop down list which we have added in source separated by comma.

See below the drop down list has been created as expected.

But in this way the drop down list items will be static. It cannot be changed until you again go to the data validation and change it manually.

We will also learn here to create a dynamic drop down list in excel. 

  1. To do that we first need to create a table which should contain the source list. Like in column A we have the source list of drop down items. We selected A1:A5 and in ‘Insert’ tab Clicked ‘Table’. The cell range for table data has been Pre-Filled. Check ‘My table has headers’ as we have provided the column header name as ‘List’. Press OK.

2. Now name your table. To do that first select your table and go to ‘Design’ tab. Then on the top left side of the tool bar you will find the place to enter your table name. Here we have given the name to our table as ‘SOURCELISTTABLE’.

3. Now select the cell where you need the drop down. In our case it’s cell C2. and then click ‘Data Validation’ in ‘Data’ tab.Here in ‘Allow’ select ‘List’ and in ‘Source’ enter the below formula.

=INDIRECT("SOURCELISTTABLE[List]")

This is using the INDIRECT function to get the list. The table name(SOURCELISTTABLE) and the column name(List) of the table from where to fetch the list has been used as argument of the INDIRECT function.

Now click OK.

4. As you can see below, the drop down has been created as expected by populating the items from the mentioned table.

5. The main benefit of this technique is if you add any additional item in the source table, it will be updated in the drop down dynamically as the entire table has been referred as source of the drop down list instead of any fixed number of values or cells.

Here we have added two additional values in the LIST column of our SOURCELISTTABLE table. As you can see in the Drop down, automatically the 2 new values ‘Option 5’ & ‘Option 6’ have been displayed.

Now we will see how to Create Drop Down List with Color in Excel. 

  1. First select the cell containing drop down and Click ‘Conditional Formatting’ in Home tab.
  2. Click New Rule

3. Now first select the Rule type as ‘Format only cells that contain’

4. Then in Edit Rule Description select ‘Specific text’ in the leftmost drop Down and then here we have selected cell A1 because we want the value ‘Option 1’ present in cell A1 should appear in yellow.

5. Now click ‘Format’

6. In the Format pane click ‘Fill’ tab

7. Then select the desired color as we have chosen here yellow and click OK.

8. Now as you can see below, the value ‘Option 1’ has been selected in drop down and it’s appearing in yellow.

9. In the same way we can add color for other drop down values as well.

10. We have repeated the same process but in ‘Edit Rule Description’ selected the ‘Cell value’ ‘between’ A2 to A4 to appear in different color as chosen below.

11. Now for the last 3 values in the dropdown which are Option 2, Option 3 & Option 4 coming in different color as follows.

12. Now if you select the cell containing Drop Down and Go to ‘Manage Rule’ in ‘Conditional Formatting’ then you should be able to see all of the conditional formatting rules you applied on your Drop Down.

13. From here you will be able to Delete/Edit Rules as well.

14. Now you would like to display the drop Down value ‘Option 3’ in Blue. to do that we will repeat the same steps as described above but in Rule type we selected ‘Use a formula to determine which cells to Format’. This is an alternative of the rules we mentioned before.

15. In Rule Description we added below :

=$C2="Option 3"

C2 is the cell containing drop down and ‘Option 3’ is the value for which we want to apply the new conditional formatting rule.

16. Then we clicked ‘Format’ and chose the desired color which is blue here in the ‘Fill’ tab.

Now we will learn how to copy drop down list in excel.

Copying drop down is simple in Excel. It’s just like normal copy paste of a cell content.

  1. First copy the cell containing drop down
  2. Then paste the same in your desired destination cell.
  3. The destination cell will contain same drop down list as source cell.
  4. In our below example we have copied our drop down list from cell C2 to E2. As you can see the entire list has been copied accurately. But if there is any conditional formatting rule present in source drop down that won’t be applied automatically in destination drop down cell.

Leave a Reply

Your email address will not be published. Required fields are marked *