Detect content in hidden columns

**Barren_Wuffett** · 06-11-2020, 01:25 PM

Let's say you have content in rows 1-3, and a "total" sum in row 4. For optics, you may want to hid rows with zeros. Let's say you accidentally hid row 2, but it actually had data. You can detect if you erroneously hid a row by taking the subtotal of all the rows and comparing it to the actual total (in row 4) because the subtotal function excludes hidden rows.

My question: is there a way to do this for columns as well? If I have data in columns A:C, and I erroneously hide column C because I thought it was all zeros--is there a way to recognize that it needs to be unhidden?

Thank you,

**oeldere** · 06-11-2020, 01:45 PM

subtotal(9,A1:A4) will also count the hidden row.

subtotal(109,A1:A4) won't count the hidden row

**Barren_Wuffett** · 06-11-2020, 01:54 PM

That works for hidden rows, but not hidden columns. My question is how can I also recognize hidden columns that have data.

**CK76** · 06-11-2020, 01:55 PM

Actually, SUBTOTAL can't be used across column (i.e. won't detect hidden columns).

Only way I can think of is to use Cell function. However, this requires that you have some mechanism to trigger recalculation of Cell function. As it does not auto-calculate on status change.
It will only re-evaluate when you change some cell value (manually, not via calculation).

See attached sample. Hide some columns, then in O2. Put some value. Or double click on the cell and click out.