Formula to decide if cell is visible

3
votes

Can I use a formula to determine if a cell is not in a hidden row/column?

I know that there are related formulas like subtotal and aggregate, but I don't quite understand those. And subtotal only seems to care about filtered row, not about hidden rows in general.

I'm looking for something like:

=ISVISIBLE(A1)

Is it possible combine formulas that arrive at that functionality?

3
excel
Visible as currently on screen? Or not in a hidden column / row? - aioobe
Visible as not in a hidden row / column. I clarified the question - user1283776
it is easy to search for this on the Internet... but anyway Pingu's answer is excellent - Paskins Loe

3 Answers

6
votes

If by subtotal() you refer to this option:

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

From this post... then that's your answer. Not being able to use it outside of a table mostly means that your data should be stored in a table, not that there's a critical limitation of that formula.

I would say - create tables for your data instead. It's always worth it using tables, whether or not you use VBA. I am yet to encounter a workbook where it wouldn't be better to use tables instead of raw ranges. Even if it's a small document, it ends up being faster.

5
votes

There is no built-in formula for this. However, you can use VBA / write your own function to achieve this. 

Function isvisible(cellname As String)
    isvisible = Not (Worksheets("Sheet1").Range(cellname).EntireColumn.Hidden Or Worksheets("Sheet1").Range(cellname).EntireRow.Hidden)
End Function

Then you can type =isvisible("A1") in a cell to get the result.

If you don't like the double quotes, here is another way:

Function isvisible(rng As Range)
    isvisible = Not (rng.EntireColumn.Hidden Or rng.EntireRow.Hidden)
End Function

Then you can type =isvisible(A1) in a cell to get the result.

1
votes

Since I spent more time than I want to admit searching far and wide for a non-VBA solution that would work for me (since a macro enabled workbook is not a viable option in this enterprise environment), I wanted to share the below answer I came across so that it can help speed the search for the next poor soul.

The below formula returns a 1 if the referenced cell column is visible, and 0 if it is not. Removing the N() portion of the formula returns a Boolean result (TRUE/FALSE) in lieu of Binary (1/0).

=N(CELL("width",A1)>0)

This is where I totally plagiarized this from: Count Visible Columns