Explanation
There is no direct way to detect a hidden column with a formula in Excel.
You might think of using theSUBTOTAL function, but SUBTOTAL only works with vertical ranges.
In this example, this range is the named range “key”.
In the example shown, columns C and E are hidden.
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.
Note: CELL function is avolatile 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.
The jot down of information to be returned is specified asinfo_type.
The N function can be used to convert TRUE and FALSE to 1 and 0 respectively.
When given a text value, the N function returns zero.
SUM Function
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.
