Explanation

There is no direct way to detect a hidden column with a formula in Excel. You might think of using the SUBTOTAL function , but SUBTOTAL only works with vertical ranges. As a result, the approach described in this example is a workaround based on a helper formula that must be entered in a range that includes all columns in the scope of interest. In this example, this range is the named range “key”.

In the example shown, columns C and E are hidden. The helper formula, entered in B4 and copied across B4:F4, is based on the CELL function :

=CELL("width",B4)>0

The CELL function will only return a width for a cell in a visible column. When a column is hidden, the same formula will return zero. By checking if the result is greater than zero, we get a TRUE or FALSE result. The N function is used to coerce TRUE to 1 and FALSE to zero, so the final result is 1 when a column is visible, and 0 when a column is hidden. Nice.

To count visible columns, we use the SUM function formula in I4:

=SUM(key)

where “key” is the named range B4:F4.

Count hidden columns

To count hidden columns, the formula in I5 is:

=COLUMNS(key)-SUM(key)

The COLUMNS function returns the total columns in the range (5) and the SUM function returns the sum of visible columns (3), so the final result is 2:

=COLUMNS(key)-SUM(key)
=5-3
=2

With other operations

Once you have the “column key” in place, you can use it with other operations. For example, you could SUM values in visible columns by using SUM like this:

=SUM(key*B6:F6)

Although each cell in B6:F6 contains the number 25, SUM will return 75 when column C and E are hidden, as shown in the example.

Note: CELL function is a volatile function . Volatile functions normally recalculate with every worksheet change, so they can cause performance problems. Unfortunately, CELL does not fire when a column is hidden or made visible again. This means you will not see correct results until the worksheet recalculates, either with a normal change, or by pressing F9.

Explanation

In the example shown, the formula in B11 is:

=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")

Working from the inside out, the work of setting up a variable range is done by the OFFSET function here:

OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range

OFFSET has five arguments and is configured like this:

  • reference = B$5, begin at cell B5, row locked
  • rows = 0, offset zero rows from starting cell
  • cols = 0, offset zero columns starting cell
  • height = ROW()-ROW(B$5)-1 = 5 rows high
  • width = 1 column wide

To work out the height of the range in rows, we use the ROW function like this:

ROW()-ROW(B$5)-1 // work out height

Since ROW() returns the row number of the “current” cell (i.e. the cell the formula lives in), we can simplify like this:

=ROW()-ROW(B$5)-1
=11-5-1
=5

With the above configuration, OFFSET returns the range B5:B9 directly to COUNTIFS:

=COUNTIFS(B5:B9,"<>") // returns 4

Notice the reference to B$5 in the above formula is a mixed reference , with the column relative and the row locked. This allows the formula to be copied to another column and still work. For example, once copied to C12, the formula is:

=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"<>")

Note: OFFSET is a volatile function and can cause performance problems in large or complex worksheets.

With INDIRECT and ADDRESS

Another approach is to use a formula based on the INDIRECT and ADDRESS functions. In this case, we assemble a range as text, then use INDIRECT to evaluate the text as a reference. The formula in B11 would be:

=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"<>")

The ADDRESS function is used to construct a range like this:

ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())

In the first instance of ADDRESS, we supply row_number as the hardcoded value 5, and provide the column_number with the COLUMN function :

=ADDRESS(5,COLUMN()) // returns "$B$5"

In the second instance, we supply the “current” row_number minus 2, and the current column with the COLUMN function:

=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"

After concatenating these two values together, we have:

"$B$5:$B$9" // as text

Note this is a text string . To convert to a valid reference, we need to use INDIRECT:

=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range

Finally, the formula in B11 becomes:

=COUNTIFS($B$5:$B$9,"<>") // returns 4

Note: INDIRECT is a volatile function and can cause performance problems in large or complex worksheets.