Using Hierarchy Formulas & Conditional Formatting to Color Code Rows in a Sheet

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.