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
- 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.
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
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.