Problem

When using ExcelTemplate to import data, a new row is inserted into the worksheet for each row of data in the data source. Any cell references to the cells where the new rows are being inserted will be updated to reflect that new rows have been inserted. This includes relative cell references (e.g. A5) and formulas (e.g. SUM(A5:A7)).

This is native Excel behavior: whenever a row (or column) is inserted or deleted, all cell references to that row/column will be updated.

In some cases, having the formulas or references updated may not be the desired behavior.

Solution

There are two ways to keep cell references absolute:

Use ‘$’ to denote absolute references

In Excel, absolute references are denoted with ‘$’. If a reference is absolute, then ExcelTemplate will not update the reference when rows are inserted. Here are some examples of absolute references:

• $B5 – The column A is absolute and will not change, even if a new column is inserted between columns A and B. Rows will still update if new rows are inserted.
• B$5 – The row 5 is absolute and won’t change if rows are inserted or deleted. If a column were to be inserted, then the column reference would update.
• $B$5 – The cell reference is absolute and will refer to B5 even if rows/columns are inserted/deleted.

Use INDIRECT to preserve formula references

If a cell reference is pre-pended with INDIRECT, Excel treats the reference as a string and does not change it. For example, the formula =AVERAGE(INDIRECT(“Sheet1!E1:E10”)) will always refer to that particular range of cells.

Additional Reading

• Contextures: Excel INDIRECT function
• CPearson: INDIRECT function

Follow

Related posts: