Home » Excel Tips & Tricks » Highlighting rows in which data appears using the conditional formatting formula function
Feb 01 2012

Highlighting rows in which data appears using the conditional formatting formula function

by in Excel Tips & Tricks

Question:     When doing a conditional formatting command, I frequently wish I could extract/highlight the rows in which my data appears.  Is there a way to do this?

Answer:        Yes, by using the conditional formatting formula function. 

Why:            To highlight/extract the rows in which the Product Category item “Bath” appears by formatting the background color to yellow.         

Applies To: Excel 2003, 2007, 2010

  1. For this example the screen shot given below will be used

2.  Select the range A2:H20 (Do not highlight the headings)

3.  Select as follows:

4.         Then select as below

To select a formatting color

  • Select the format button as given above
  • Under the list of background colours select Yellow and then OK

5.         The result will be that all the rows where the product category item “Bath” appears will be highlighted in yellow

If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.

 

 


 

Print Friendly Print Get a PDF version of this webpage PDF

Further Reading:

  1. Conditional Formatting
  2. Conditional Formatting
  3. Data Validation with Formula
  4. AND Function
  5. Highlighting Duplicates

  • Enjoyed this Excel Tip? Subscribe now to our free Excel Tips & Tricks e-newsletter:

– who has written 56 posts on
The BI Blog – Powered by Alchemex.

Save time and work smarter every day with Excel on Steroids Tips & Tricks, designed as a continuous learning tool to help you become an Excel ‘power user’. If you would like to receive these informative tips in your inbox each week, please subscribe here.

  • Fred

    Your solution explains how to highlight the records with “Bath.”
    The question posed also asks how to extract them.
    I would like to see the answer to that as well.
    Fred Goldstein

    • Alchemex Marketing

      Hi Fred,

      Thank you for picking up on that and informing us. To extract the records with “Bath”, you can follow the steps below.

      • Select any cell within the data list
      • Select Data-Filter
      • Select the drop down arrow in cell E1(Product category heading)
      • From the resultant window – select filter by colour
      • Select the colour used for highlighting the records(Yellow in the example)
      • You will see that only the records with “bath” as the product category will be displayed

      We appreciate your feedback, hope to hear from you soon.

Previous post:

Next post: