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.

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:

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…

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