Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Quickly color/conditional formatting drop-down list in Excel

It’s easy to add conditional formatting based on value in Excel. But how to add conditional formatting based on a drop-down list selection? And what if conditional formatting based on multiple drop-down list selections? Now Kutools for Excel’s Colored Drop-down List feature brings you easy solutions.

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

 Highlight cells based on drop-down list selections in Excel

For example, I have a fruit sales table in Excel as below screenshot shown. And I will apply the Colored Drop-down List feature to highlight cells based on the drop-down list selections easily as follows:

1. Click Kutools > Drop-down List > Colored Drop-down List to open the Colored Drop-down list dialog.

2. In the Colored Drop-down list dialog, please configure as follows:
(1) Tick the Cell of Drop-down list option in the Apply to section;
(2) In the Data Validation (Drop-down List) Range box, please specify the range you have set data validation (drop-down list) for;
(3) All items of the drop-down list are listing in the List Items section, and please select one item you will highlight cells based on;
(4) In the Select Color section, please choose one color you will highlight cells with;
(5) Repeat above (3)-(4) steps to specify other items and highlight colors.  

Tip: If the specified data validation (drop-down list) range contains cells that you have not set drop-down list for, the items of the drop-down list will not list in the List Items box. And you need to specify the range again.

3. Click the Ok button.

Now when you change the items from the drop-down list in the specified data validation (drop-down list) range, the cells will be highlighted or unhighlighted automatically based on the chosen drop-down list items.


 Highlight rows based on drop-down list selections in Excel

The Colored Drop-down List feature also can highlight rows based on specified drop-down list items in Excel easily. Please do as follows:

1. Click Kutools > Drop-down List > Colored Drop-down List to open the Colored Drop-down list dialog.

2. In the Colored Drop-down list dialog, please configure as follows:
(1) Tick the Row of data range option in the Apply to section;
(2) In the Data Validation (Drop-down List) Range box, please specify the range you have set data validation (drop-down list) for;
(3) In the Highlight rows box, please select the rows you will highlight based on drop-down list items;
(4) Now items of the drop-down list are listing in the List Items section, please select the one you will highlight rows based on;
(5) In the Select Color section, please choose one color you will highlight rows with;
(6) Repeat above (4)-(5) steps to specify other items and highlight colors. 

3. Click the Ok button.

When you change items from the drop-down list in the specified data validation (drop-down list) range, the specified rows will be highlighted or unhighlighted automatically based on the chosen drop-down list items.


 Notes

1. If you need to stop cells or rows from being highlighted automatically, please select the cells or rows, and then click Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells or Clear Rules from Enter Sheet.

2. If you need to clear the data validation of drop-down lists from selections, you can apply the Clear Data Validation Restrictions feature of Kutools for Excel.


 Demo:Highlight cells/rows based on drop-down list selections in Excel


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.