The Conditional Formatting Method Nobody Talks About, by Excel Off The Grid on YouTube. 8 min 36 s.
- Video length
- 8 min 36 s
- Time to do it
- 15 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Decide the hierarchy of your report: which rows are level 1 totals, which are level 2 subtotals, and which are level 3 detail lines.
Add a helper column beside your data and enter the level number for every row, such as 1, 2 or 3. Keep this column outside the printed report area.
Select the report range you want to format, starting at the first data row. For example, select A5:F60, not the header row.
On the Home tab, click Conditional Formatting, then New Rule.
Choose Use a formula to determine which cells to format.
Enter a formula that points to the first level cell, such as =$C5=1. Lock the helper column with a dollar sign and leave the row number relative.
Click Format, choose bold text and a fill color for level 1, then click OK twice.
Repeat New Rule for level 2 and level 3, changing the number in the formula and using lighter formatting each time.
Open Manage Rules and tick Stop If True on the top rule if your rules overlap.
Change a level number in the helper column to check that the formatting follows the hierarchy.
Tips and common mistakes
Keep the helper column in the same rows as the data so relative references stay aligned.
Use mixed references like $C5, not absolute $C$5, or every row will compare against the same level.
Apply rules in level order and use Stop If True to stop lower level rules overriding higher ones.
Test the rules on a copy of the report before applying them to a live workbook.
Questions
Can I use this on a profit and loss or balance sheet report?
Yes. It works well on fixed format reports where the row hierarchy stays the same each period.
Why does my whole range format the same way?
Your formula probably uses an absolute row reference such as $C$5. Change it to $C5 so each row checks its own level.
Do I need a helper column?
A helper column is the simplest way. You can also use a formula that calculates the level, but it is harder to maintain.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
