- On a new worksheet, type the values that you want to appear in your drop-down list. They should be in a single column or row without any blank cells (for easy to use, it is recommended to sort your values).
- Select all of your values, right-click, and then click 'Define Name'
- Then it will prompt up a 'New Name' window. Type a name for the values of your drop-down list and then click 'OK'. (No space in the name. )
- Select the cells that you want to have drop-down list.
- Click Data Validation.
- In the settings tab, select 'List' from Allow box
- From the source field, type equal sign (=) and immediately followed by the name you defined in step 3.
- Click OK to confirm.
Now make sure to note down which cell you put the first dropdown list in because you will need it later on, i.e. Now, click on another blank cell and go to Data Validation again. Choose List again, but this time type in “ =indirect(E2) ” into the Source. Excel 2010 Drop Down List. List Data Sources. Manually entering the source data for the Excel 2010 drop down list is probably the least desirable method. A better way is to put the list in a range, then refer to the range.
- Highlight the cells you want to have drop-down list.
- Then click 'Conditional Formatting' --> New Rule.
- Select 'format only cells that contain'
- Edit your condition in the session called 'Edit the Rule Description'.
- Click 'format' button to select your desired color in cell.