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

KrisWalsh

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. 

Kara Lumley

You are awesome.

Thanks Kris - hope you are doing well.

Best,

Kara

Ben Donahue

Kris,

I'm not sure it is better, but I believe the same result can be reached using the following formula:

=IF(COUNT(ANCESTORS()) = 0, 1, IF(COUNT(CHILDREN([Task Name]71)) = 0, 0, IF(COUNT(ANCESTORS()) < 1, 1, IF(COUNT(ANCESTORS()) < 2, 2, IF(COUNT(ANCESTORS()) < 3, 3, IF(COUNT(ANCESTORS()) < 4, 4, IF(COUNT(ANCESTORS()) < 5, 5, 6)))))))

This requires only one leverage column. I have 6 conditional formatting rules set up, one for each blue available and one for white, to be used on rows with no children, "COUNT(CHILDREN([Task Name]28)) = 0, 0."

One functional difference is that, if the task has no ancestors, it is given the tier level assignment of 1, triggering the formatting such that it will get that tier's color, even though it has no children. Something I like, you may not want. Remove this functionality buy removing the initial "IF(COUNT(ANCESTORS()) = 0, 1," and the final ")" so the formula looks like this:

=IF(COUNT(CHILDREN([Task Name]71)) = 0, 0, IF(COUNT(ANCESTORS()) < 1, 1, IF(COUNT(ANCESTORS()) < 2, 2, IF(COUNT(ANCESTORS()) < 3, 3, IF(COUNT(ANCESTORS()) < 4, 4, IF(COUNT(ANCESTORS()) < 5, 5, 6))))))

I did not write this formula, and I forget to whom I should give credit, but it came from the community, now I'm giving it back.

Andrée Starå

Hi Ben,

Nice job!

Thanks for sharing!

Here's another option I use all the time.

Try something like this.

=IF(COUNT(CHILDREN([Project / Task Name]@row)) > 0; COUNT(ANCESTORS()) + 1)

The same version but with the below changes for your and others convenience.

=IF(COUNT(CHILDREN([Project / Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Hope that helps!



Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

AleDL

This is perfect. You're awesome.

Thank you very much!