
Using Hierarchy Formulas & Conditional Formatting to Color Code Rows in a Sheet
June 16, 2017 7:15 pm
6
I’m always interested to hear if someone else has figured out a better way to do something, so feedback is welcome and appreciated! (:
Example Sheet: Hierarchy Formulas & Conditional Formatting
I like to use colors to help guide me through a sheet and I have settled on a process that works for me. *See Picture*
Minimum Requirements: 2x Text Columns (typically hidden), & 4x Conditional Formatting Rules
Assumptions I’m accounting for
Parent Rows: I want to differentiate between the 1st, 2nd & 3rd levels of hierarchy Parents. (Count of Ancestors = 0, 1 & 2)
Any parent lower than that can be one color. (Count of Ancestors > 2)
Rows with No Children: At the Top Level of the hierarchy I often use a ‘Header’ row that has no children. (Count of Ancestors = 0 & Count of Children = 0)
All other Child rows will not be colored by hierarchy.
Formula Columns
For simplicity and consistency, I name my formula columns A & C (Ancestors & Children) and typically hide them.
Setting it up
Step 1: Add a Text/Number Column named “A” for Ancestors, copy/paste the following formula into row 1 & copy down
=COUNT(ANCESTORS()) 🡨 This formula will occasionally throw an error. Just copy down again to fix it.
Step 2: Add a Text/Number Column named “C” for Children, copy/paste the following formula into row 1 & copy down
=COUNT(CHILDREN())
Step 3: Add the the Conditional Formatting rules from the image in the correct order.
(Change the colors to whatever you prefer)
Step 4: If you prefer, hide the A & C Columns
Results
No matter how or where you indent or outdent the colors will change in a predictable pattern.

Comments
Kara Lumley
June 17, 2017 10:33 am