Adding Rows without Changing a Cell Reference

At the bottom of a column of data James has a SUM formula, such as =SUM(E$4:E76). When he inserts a row at row 4, the formula is automatically changed by Excel to =SUM(E$5:E77). James thought the dollar sign was supposed to "anchor" the row reference so it didn't change. He now wonders how he can insert rows at row 4 without having Excel modify the first cell reference in the formula.

There are a few ways to handle this, which I'll get to in a moment. First, however, it is important to understand how Excel views ranges and how it adjusts those ranges when inserting rows.

James has a range that extends from row 4 through row 76, a total of 73 rows. When you insert a row, Excel inserts the row (or rows) above whatever row you currently have selected. Thus, if you select row 10 and insert one row, then whatever was at row 10 (and all rows below that) is moved down to accommodate the new row. And, the range now extends from row 4 (above row 10) through row 77, for a total of 74 rows. It now goes through row 77 because, again, everything from the selected row (row 10) was moved down by one row.

Similarly, if you select row 4 and insert a single row, then the new row is inserted above row 4 and is, technically, outside the original range referenced in your SUM formula. This is why the range reference is modified.

Another way to look at this is what you would want to have happen to your formula if you deleted a row above your range of cells. If your data starts in row 4, what would you want to SUM if you deleted, say, row 2? After the row deletion, your data now starts in row 3, so you would want the range reference in the SUM formula to reflect the new range, not to be anchored to row 4.

This obviously brings up the question, then, of the dollar sign in the range reference. Dollar signs do, in a sense, "anchor" either the row or column that follows the dollar sign. However, the dollar sign, as anchor, only comes into play if you copy the formula to a different cell, not if you insert or delete rows.

So, this brings me to the different ways you make sure that the E4 cell reference doesn't change. Most of the methods involve the modification of the SUM formula. You could change it to this:

=SUM(INDIRECT("E4"):E76)

The INDIRECT function converts the string "E4" to a real Excel address. Excel won't change this because, as far as it is concerned, it is just a string. So, you're effectively hardcoding the cell reference to E4.

A second approach is to modify the formula in this manner:

=SUM(E$3:D76)

Notice that the first reference in the range is now E3 instead of E4. As long as cell E3 doesn't contain a numeric value, this will work great. (For instance, E3 could contain text or be blank, and there would be no deleterious effect on your sum.) You can now insert rows at row 4, and your range will expand as desired.

Finally, you could select your original range (E4:E76) and press Ctrl+L to convert the range to a table. Now you can insert rows at row 4 and your table will remain intact and behave as you desire.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13926) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.