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

Check if cell is visible in Excel

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.

Example: Goal is to check with an Excel function if cell C5 is currently visible.
Example: Goal is to check with an Excel function if cell C5 is currently visible.

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)


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


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?

Use Professor Excel Tools to check if a cell is visible or hidden.
Use Professor Excel Tools to check if a cell is visible or hidden.

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)

Professor Excel Tools Box

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.


Download

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

Image by Kevinsphotos from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

2 comments

  1. Thanks for info but following is not working for hidden column
    IF(SUBTOTAL(103,C5)=1,TRUE,FALSE))

    Also, would you advise how constant 103 apply to hidden column when MS Help shows following”Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.”

  2. A nice and fast formula to set a flag if a cell is visible, depending on a filter set in same or another sheet:
    =IFERROR(SUBTOTAL(3,INDIRECT(“‘Sheet1′!B” & XMATCH(B36,’Sheet2’!$B$1:$B$10000,1))),0)

    It sets a 1 if field is referenced value is visible and 0 if not.
    Due to it allows to handle filter rows on various sheets.

    It’s much faster than the SumProduct formula you may find on the web.

Leave a comment

Your email address will not be published. Required fields are marked *