Excel function determine if a cell is visible

In conditional formatting I want to detect if the row above the current cell is hidden or visible. How can I detect if a cell is visible or not?

The only hack I can think of is:

1. Create a column with all 1 values.
2. Use a formula like subtotal(109,c2:c2)=1 (i.e. just on the cell I want to check) to determine if it's visible or hidden.

Is there a way do do this without a temporary column that has to be left visible when the row is shown?

To avoid an XY problem, what I want to do is have a column that is the category for the row. The first VISIBLE row with a particular category should have a different style; later rows with the same category are slightly different. In ASCII:

cat.   item
+AAA+  aaaa
(AAA)  bbbb
(AAA)  cccc
+BBB+  dddd
(BBB)  eeee
(BBB)  ffff

If my filter hides the row with dddd then I want the row with eeee to have the +BBB+ styling instead.

Instead of subtotal using a sum on another column, you can use subtotal using counta to see if a (known-non-blank) cell is hidden or not. For example, if column A will normally be visible (unless the row is hidden)

= IF( SUBTOTAL(103,A2)=1, "VISIBLE", "HIDDEN (or blank)" )

You can put this formula in a column that may be hidden, and it will still work.

In conditional formatting, then, you can just use: = SUBTOTAL(103,$A2)=1 to determine if the row is visible.

As an addendum to Phrogz's answer, if you need to check whether a cell in a column is hidden, try either of the following,

Conditional Formatting

=CELL("width",TargetCell)=0

This updates automatically as soon as a column is hidden.

Formula Check

=IF(CELL("width",TargetCell)=0, "Cell is hidden.", "Cell is visible.")

This formula will not update automatically and you would have to direct Excel to "Calculate Now" by choosing the menu option or pressing "F9".

This is similar to Gary’s Student’s approach.  Define the following VBA function:

Function MyRowHidden(ref As Range)
    MyRowHidden = Rows(ref.Row).Hidden
End Function

See How do I add VBA in MS Office? if you need help with that.  Now you can use MyRowHidden(cell) to check whether the row containing cell is hidden.

The way I devised to solve the problem uses a helper column, but you can hide it.  Assuming that your data begin in Row 2, with the categories in Column A, enter

=OR($A1<>$A2, AND(H1,MyRowHidden(H1)))

into cell H2, and drag down.  This formula evaluates to TRUE if

• the category in this row (A2) is different from the category in the preceding row (A1); i.e., this is the first row of a new category, or
• the previous row should be highlighted, but is hidden.

Then simply use Conditional Formatting to highlight cell A2 if =H2 is true.

Example: raw data:

Yeah, I’m a traditionalist; I still count Pluto as a planet.  Here it is again with the prime numbered rows (2, 3, 5, 7, 11, and 13) hidden:

Of course you’ll have to enable macros in your workbook.