Change highlighted rows depending on your dropdown list choice


By Kara Hiltz


Microsoft Excel 2007/2010/2013/2016

Operating System:

Microsoft Windows


Sometimes finding the data you need quickly presents a challenge, especially when working with large Excel data sets. But by combining data validation and conditional formatting you canselect one of the categories in a column of data and highlight only the rows matching that category—automatically.

To change what rows Excel’s conditional formatting highlights when we make a dropdown list selection, we’ll:

  • Add a list of possible category choices to our spreadsheet.
  • Use data validation to translate our category choices into a dropdown list.
  • Apply conditional formatting based on the selection made in the dropdown list.


Excel offers many ways to showcase specific kinds of data. Conditional formatting gives you tools to draw attention to certain data or to visually compare and contrast data. But what about worksheets in which you may want to focus on different data in different circumstances?

This technique, which utilizes both data validation and conditional formatting, allows you to choose from a list of column categories. Your current selection determines what rows of data Excel highlights, as shown in Figure A.



The highlighted rows update automatically when we select a new category from the dropdown list.

Set up your categories

First, you’ll need to let Excel know what categories to look for in a column on which to base the conditional formatting. This technique works best for data with fixed categories that you want to highlight. In our example, we have inventory items in a few different store departments, as shown in Figure B. You could also use this conditional formatting to highlight employees in a specific job role or data from a specific month or year.

Download: To follow along with our example, download and extract the file changehighlight.xls from the URL given in the Download link.



The categories in Column F are for reference only, and we will hide the column later.

To create and hide a list of categories for reference:

  1. Choose a location for your categories list that doesn’t interfere with your data and is easy to hide without affecting your data. We chose F3:F7.
  2. Enter the categories matching the criteria you want to use for highlighting rows in your data, one in each cell. In our example, we added four inventory department categories: décor, hardware, lighting, and plumbing.
  3. Right-click on the column header and choose Hide from the shortcut menu.


Your categories list should now be hidden from view, but you will still have the category list available for reference as you set up data validation and conditional formatting.

Use data validation to create the dropdown list

You’ll need a way to let Excel know which item from the category list you want to use as criteria for your row highlighting. Data validation will help us create a dropdown list in our worksheet.

To add a dropdown list of category items:

  1. Select cell A1 and type Choose category: in the cell.
  2. Select cell C1, where you will add the dropdown list.
  3. Switch to the Data tab and, in the Data Tools area, click the Data Validation button to open the Data Validation dialog box.
  4. On the Settings tab, choose List from the Allow dropdown list.
    Join NowClose
Return to the ExcelSkillsSociety's homepage