Adding a message to grouping icons

A SUBTOTAL trick

It is best practice to use grouping to hide and unhide rows in Excel. I recently saw a technique that also displays a message.

This technique was shared by Ken Puls from www.excelguru.ca. Ken is a legend of Excel, Power Query and Power BI.

His formula displayed a message whenever rows were hidden by grouping.

I have tweaked the technique to always display a message that explains how to use the grouping icon buttons on the left.

See the image below.

In the top section of the image the workings rows are hidden and a message explains how to unhide them.

At the bottom of the image the workings are visible and the message explains how to hide them.

The formula in cell A4 is

="<<< Click "&IF(SUBTOTAL(103,A1:A3)=0, "plus sign to see","minus sign to hide")&" workings"

The formula is long due to the text for the messages.

The SUBTOTAL function is using the 103 option, which means it performs a COUNTA calculation on the VISIBLE cells in the range A1:A3. See image below

SUBTOTAL has the ability to perform calculations on visible cells only. All the 3 digit codes in the above image perform their calculations on visible cells only. Only the SUBTOTAL and AGGREGATE functions can do this type of calculation.

In this case if the visible cells count equals zero it means the rows are hidden and the message explains how to unhide them.

If the SUBTOTAL count doesn’t equal zero it means the rows are visible and the message explains how to hide them.

I shortened the formula by including text at the start and the end that was consistent in both messages.

The IF function then creates the variable text in the middle of the message.

The & symbol joins text together.

This technique may be useful for files that are used by people not familiar with Excel’s row grouping feature.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.