Put a dynamic hint for showing or hiding rows or columns hidden by Data Group

This is about using SUBTOTAL and CELL function to detect whether a row and a column is hidden respectively.

When rows are hidden by Data–>Group, you will see a +/- button on the leftmost of  spreadsheet to remind you that you may expand or collapse the data you are looking at.Excel Tips - Dynamic Hint for showing or hiding hidden rows

To me, this +/- button is obvious enough.  However, my experience told me that it is NOT.  Some users simply have no idea what is the button for.  And for some reasons I cannot understand, they do not even try to click on it to see what will happen.

That gives me a thought of putting a hint on the cell next to it to remind user.  Like the screenshot below:

Excel Tips - Dynamic Hint for showing or hiding hidden rows 2

Yes.  This can be done by using SUBTOTAL function, together with a simple IF statement

In our example, input the following in C17:

=IF(SUBTOTAL(103,A16)=0,"<== Click the |+| to expand", "<== Click the |-| to collapse")

The SUBTOTAL 103 performs a COUNTA function to the range A16.  If A16 is visible, SUBTOTAL counts it; if A16 is hidden, SUBTOTAL ignores it. (This is what the function number 103 designed for).  In other words, SUBTOTAL(103,A16) gives a result of either 1 (if A16 is visible) or 0 (if A16 is hidden), provided that A16 is a non-blank cell.

Then the rest of =0 and the IF statement should not be difficult to understand.  It means:IF A16 is hidden, then “<== Click the |+| to expand”, else <== Click the |-| to collapse”

Want to learn more about SUBTOTAL?  Please read SUM vs. SUBTOTAL.

What about hidden columns?  SUBTOTAL doesn’t work for columns…

Excel Tips - Dynamic Hint for showing or hiding hidden columns

Yes. You are right.  SUBTOTAL is designed for visible rows, not columns.

We need CELL function to help.

In our example, input the following in L1:

=IF(CELL("width",K1)=0,"↑ Click the |+| above to expand", "↑ Click the |-| above to collapse")

Thanks CELL function for making the trick possible.

The syntax:

=CELL("info type",[Ref])

There are 12 different “info type” for the function.  For details, please visit: https://support.office.com/en-US/article/CELL-function-A9B32472-8DA9-4123-90FB-F89A3271B922

“Width” will give you the column width of the reference cell rounded off to an integer.  By definition, the column width of a hidden column is 0 (zero).   That makes sense,  right?

Now we know that CELL(“width”,K1)=0 means column K is hidden.  Then the formula returns “↑ Click the |+| above to expand”, “↑ Click the |-| above to collapse” depending whether column K is hidden or not.

You may download a Sample File HERE.

With this little trick, we may find a way to SUM visible columns only – a situation when SUBTOTAL does not work.  Let’s talk about it next week.  Stay tuned. 🙂

Just in case you do not know how to GROUP Data…

Select the rows (or columns) you want to group, then go to Data tab –> Outline group –> Group

Excel Tips - Dynamic Hint for showing or hiding hidden rows 3


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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