Excel function determine if a cell is visible

Question

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.

Answer 1

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.

Answer 2

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".

Answer 3

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.

Answer 4

To detect if the row above the active cell is Hidden, run this macro:

Sub WhatsAboveMe()
Dim r As Range
Set r = Selection
With r
    If .Row = 1 Then
        Exit Sub
    End If
    If .Offset(-1, 0).EntireRow.Hidden = True Then
        MsgBox "the row above is hidden"
    Else
        MsgBox "the row above is visible"
    End If
End With
End Sub

Answer 5

This thread's a little old, but in case it's helpful to anyone, here is a way to conditionally format duplicates on a filtered table without having to use VBA.

1. Make a column populated with 1's

2. Make another column and put a formula like this in it

   =IF(SUBTOTAL(103, [@ColumnWithOnlyOnesInIt])=1, [@ColumnYouWantToCheckForDuplicates], "")

3. Put in normal duplicate conditional formatting on the column you want to check.

The formula from step 2 will copy the value from the column you want to check but only when the row is visible. That way, when duplicates are checked for, you only get the ones applicable to the filtered table. I think this may not work for zeros (or "" or whatever you choose as the "else" value in your if statement). So it may be possible to get a line zero value in your list that is highlighted as a duplicate. Other than that I'm having good luck with this method.

Answer 6

I'd propose to use the following formula (on a range e.g. $A:$A):

=AND(A1=OFFSET(A1;-1;0);SUBTOTAL(103;OFFSET(A1;-1;0))=1)

What that does:

If both

1. the cell is equal to the one above: A1=OFFSET(A1;-1;0)
2. the cell above is visible: SUBTOTAL(103;OFFSET(A1;-1;0))=1

then the result is True thus the cell is a duplicate of a visible cell right above and should be e.g. grayed out.

Sidenote: Using the OFFSET function makes the conditional formatting will not break when an additional row is inserted.

Answer 7

In the conditional formatting in, say, cell A11, use this rule

=AGGREGATE(3,3,A10)=0

Note the rule looks at the row directly above. The 3,3 parameter in AGGREGATE in plain English is

show the COUNTA of visible cells.

When A10 is hidden, the rule is TRUE and then you can set the conditional formatting in A11 on that. By the way, this works only if cell A10 has content.

So that you can copy this formatting down the column, there should be no absolute reference in the row in the rule. No helper column is needed for this approach.

Answer 8

It's an old task but the issue still appears today: how to detect hidden rows, especially in a bit more complicated situation.

Searching on the web shows a SUMPRODUCT construct, which is very slow in case of many rows.

A nice and fast formula to set a flag if a cell is visible or hidden, depending on a filter set in same or another sheet:

=IFERROR(SUBTOTAL(3,INDIRECT("'Sheet1'!B" & XMATCH(B2,'Sheet2'!$B$1:$B$10000,1))),0)

It sets a 1 if field's referenced value is visible and 0 if not. That allows to handle filtered rows in various sheets. Example: Master Sheet 1 defines main filters, sheet 2 then can easily be filtered too.

Answer 9

Here is the solution I just used:

I Created a new column C (and HID the original (Column B)). In the new column I used the formula =SUBTOTAL(9,B2) Which SUMS the ONE row you are interested in. I then copied it all of the rows!

Now, when you filter using advanced filter. The values are all ZERO in this column unless they are visible (not filtered).

Then, the normal =SUMIF() works like a champ. Just don't accidentally use the hidden column to sum across. Sum across the SUBTOTAL() column you just created.