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:
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:
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.
If you want to count the number of visible items in a filtered list, you can use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter. The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, and more...
If you want to sum only the visible rows in a filtered list (i.e. only those rows not filtered out), you can use the SUBTOTAL function with function number 9 or 109. What makes SUBTOTAL especially useful is that it automatically ignores rows that...
The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the...
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.