FREE CHEAT SHEET
All the key points in one handy PDF
Plus, collect a FREE copy of our 30 Excel Power Tips.
Enter your details for immediate access.
Assign a 'primary' menu
How To Use Conditional Formatting To Highlight Rows Of Interest (Automatically)
Conditional Formatting is a very useful, powerful and versatile tool. As its name suggests, it has two parts - a condition and a format. When the condition is true, the format is applied.
You can check for all kinds of things. You can also apply a multitude of different formatting options ranging from basic cell colours and font colours to coloured data bars, colour scales (heat maps) and even icon sets.
Moving past the basics you can set a condition based on a formula and that’s what we’ll be doing in this cheat sheet example.
The Conditional Formatting tool is available on the Home tab.
Example: Highlighting every ‘Category A’ row
Consider this worksheet. Every ‘Category A’ row has been highlighted automatically.
Figure 01 - In action: Highlighting cells using a conditional formatting formula
Here’s how it's done.
1. Select cells A2:D10 (the cells that you want to highlight when appropriate).
2. Select the Home tab, if necessary.
3. Click the Conditional Formatting icon.
4. Choose New Rule.
Figure 02 - Selecting New Rule
5. Choose ‘Use a formula to determine which cells to format’.
6. Write a formula. When creating a formula to be applied across a cell range, write the formula as if you were only checking the first cell. In this case, that is cell A2. The formula is
A2="A"
However, you need to adapt the formula so it works correctly for every row and every column.
The row number must be relative (not prefixed with $). This means that for each subsequent row, the formula will check the cell in column A for that row.
But the column letter must be absolute (prefixed with $). This means that every cell on a row check the cell in column A. If that cell is category A, then the current cell will be formatted.
Therefore, you need to change the formula to:
$A2="A"
7. Set the formatting, e.g. set the cell colour to yellow.
Figure 03 - Write a Conditional Formatting formula
That’s it.
To test the formula, whenever the category in column A is "A" every data cell in that row is highlighted automatically.
Figure 04 - Try out any combo
Have you used conditional formatting in interesting ways?
Did this clear something up for you? If so, please share on your socials and/or post a brief comment below.
Likewise, if something didn't make sense or you have a question, pop it into the comments below. I'll be waiting ...
Cheers. Here's to your learning and success. Enjoy the rest of your day.
FREE CHEAT SHEET & VIDEO
Watch the video to see it done and download a printable PDF cheat sheet of all the key steps
PLUS, GET A FREE COPY OF MY 30 EXCEL POWER TIPS
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
