How to Check if Cell Is Visible: Three Different Excel Formulas

In some – admittedly rare – cases you might want to check if a cell is visible in Excel. Visible means that neither the row or column is hidden nor the row or column is grouped and collapsed. In this article, you can find three method for this.

Example and goal

Let’s take a look at a simple example: We want to know with an Excel formula, if cell C5 is currently visible or not.

If cell C5 is visible, we want to return TRUE or FALSE if it is hidden. Hidden means that

• that the row or column is hidden or,
• that the row or column is grouped and collapsed.

Method 1: Use the SUBTOTAL function

Excel doesn’t have a direct function to check if a cell is visible or not. But there is a workaround – that comes with some restrictions.

As you can see, the function is not too long. You just have to replace C5 with your cell reference:

=IF(SUBTOTAL(103,C5)=1,TRUE,FALSE)

Explanation: The SUBTOTAL function returns the number of cells that are visible and not empty. The IF function around helps to return the correct values TRUE or FALSE. You can – of course – also return other values.

And this is already one of the major restrictions: If the cell is empty (but visible) the formula also returns FALSE.

The following extension catches this error but does not solve it entirely:

=IF(C5="","Cell empty, check not possible",IF(SUBTOTAL(103,C5)=1,TRUE,FALSE))

The second restriction is that it only works if the row is hidden. It does not regard columns. So, if the column is hidden (or grouped and collapsed), the function still returns TRUE.

Method 2: VBA Macro to check if a cell is visible

The second method uses a short VBA macro. Just copy and paste the following code into a new VBA module (here is are the steps for that).

Function ProfessorExcelCellIsVisible(cell As Range)
    On Error Resume Next
    Application.Volatile

    Dim visible As Boolean
    visible = True
    
    If cell.EntireColumn.Hidden = True Then visible = False
    
    If cell.EntireRow.Hidden = True Then visible = False

    ProfessorExcelCellIsVisible = visible

End Function

In your Excel file, you can now use this function:

=ProfessorExcelCellIsVisible(C5)

Method 3: Use an Excel add-in to check if a cell is visible

The third method is probably the most convenient one: Quickly install Professor Excel Tools, our Excel add-in with more than 120 features, and use the built-in function. You don’t even have to buy a license because the built-in Excel function are free to use. Sounds great, right?

So, how does it work? After you have installed Professor Excel Tools (you will see a new ribbon called “Professor Excel”), just type the following function into an Excel cell:

=PROFEXIsVisible(C5)

Download

Please feel free to download all the examples from above in this Excel file.

Image by Kevinsphotos from Pixabay