Don’t Make this Mistake Using Excel’s OFFSET Function

Business Data Analysis with Excel

Excel’s OFFSET function will give you errors if you follow some instructions on the internet. Even the Microsoft Support page found here makes this mistake,

How to use defined names to automatically update a chart range in Excel

In their example, Microsoft puts all data in one column and references that data column in the name range using the entire column reference, e.g.,$A:$A. The COUNTA function counts all filled cells, text or numeric, in the column. Then, they account for the text header by subtracting 1 from the COUNTA function that counts filled cells in $A:$A.

While it is quick and easy to use an entire column, it can cause problems. If someone types anything in column A other than data adjacent to chart data the count of filled cells is wrong and the chart displays wrong.