Whole Column Referencing

As a rule of thumb, referencing a whole column in Excel isn’t considered good practice. It’s better to include only the cells you need in a range. And it’s even better to use tables so you’re working with structured references.

That said, tables don’t expand and contract like dynamic array formulas, and you might be dealing with a growing range of values or generating them with VBA.

Sometimes it’s just easier to reference everything, so you’re not faffing about altering references later on.

Regardless, let’s look at the examples and compare them in Excel to Google Sheets.

10 random numbers are present in A2:A11, but this list is likely to grow, so we use =COUNT(A:A) to find the number of non-empty cells in column A, which is 11.

This works in both; however, it includes the header cell, which we don’t want.

The solution in Sheets is to use =COUNTA(A2:A), which begins the range at A2 and continues for the rest of the column, returning 10 instead.

However, Excel doesn’t allow this — it only acknowledges the starting cell. In fact, this formula will return 1 even if A2 is blank.

Without resorting to other functions, the only way around this is to explicitly reference the range using =COUNTA(A2:A1048576), but that looks a bit awkward, doesn’t it?

Needless to say, I like the way you can reference columns in Sheets’, and I’d welcome it in Excel.

Does anybody else think the same?