Written by Allen Wyatt (last updated June 29, 2024)**This tip applies to** Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021

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.

**Save Time and Supercharge Excel!** Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out *Excel 2010 VBA and Macros* today!

Excel works with decimal values very easily. It is more difficult for the program to work with non-decimal values, such ...

Discover MoreLooking for a formula that can return the address of a cell containing a text string? Look no further; the solution is in ...

Discover MorePeople often use Excel to maintain lists of information that they need to track. This tip shows several ways you can ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2024-07-03 05:35:26

Rene

An extra advantage of the above is that you can give the data a range name, including the empty rows/columns, knowing that insertion/deletion will always be within the range.

2024-07-01 08:10:51

Rick Keevill

2024-06-30 07:44:28

Alex Blakenburg

Typically you would convert the whole Data Range to a table not just the amount column.

Also since the first value is in E4 you would convert from E3 ie E3:E76 to a table, and tick "my table has headers" with E3 being the heading.

My preference is to use Ctrl+L as mentioned above since it works in all languages and is the default for Excel Online but most people are using Ctrl+T. (in VBA Tables are known as ListObjects hence the Ctrl+L)

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments