Then click 'Conditional Formatting' -> New Rule.Highlight the cells you want to have drop-down list.From the source field, type equal sign (=) and immediately followed by the name you defined in step 3.Īpply the Conditional Formatting to color coded the drop-down list values.In the settings tab, select 'List' from Allow box.Select the cells that you want to have drop-down list.Type a name for the values of your drop-down list and then click 'OK'. Then it will prompt up a 'New Name' window.Select all of your values, right-click, and then click 'Define Name'.They should be in a single column or row without any blank cells (for easy to use, it is recommended to sort your values). On a new worksheet, type the values that you want to appear in your drop-down list.
