How to reference column by header name in excel ?

Updated on Fri Sep 16 2022

SHARE

If you want to refer all rows in a given column by using column header name or letter, type the column header name or letter twice and a colon in between, for example A:A.

Depending on the nature of the formula you have used, behavior or return value of the referred column changes.

If you have used the column reference in a formula that accepts a range of cells instead of a single cell, column referenced by header name is considers as all the cells in the given column.

For example, if you use this type of column header reference in a formula like SUM (), AVERAGE (), output value gives the summation or average of all the cells in the given column.

But if you used such type of column reference in a formula that accepts single cell at a time it will act accordingly to output the value in the relevant row that you have typed the formula.

For example, if you input the formula =D2*E:E, in cell F4, the formula evaluates as =D2*E4. You can see that it automatically got the row number in column E: E relative to the cell you have input the formula.

SHARE