Adjusting Formulas for Top-Added Rows

by Allen Wyatt
(last updated September 26, 2015)

10

Bill has a formula that sums a column in rows 2 through 15, using the SUM function. If he adds a row anywhere at rows 3 through 15, the formula adjusts properly. If he adds a row at the top (just above row 2), the formula does not adjust at all to include the inserted row. Bill wonders if there is a way to make the formula adjust properly and automatically when he adds a row at the top of what is being summed.

There are many schools of thought on how this can be done, each implementing a different technique to accomplish the same thing. One approach is to simply convert your data into a formal table in the worksheet. The easiest way is to select a cell within your data and then press Ctrl+T. Excel recognizes your data as a table and if you insert any rows at the top of your table, just under your headings, they are automatically reflected in the summing of the table.

Another approach is to adjust your SUM formula. You proabably are doing a sum of rows 2 through 15 because row 1 has a column heading in it. If this is the case (and if the column heading contains text), then simply adjust your SUM formula so it references rows 1 through 15. Now, when someone adds a new row at row 2, it is automatically included in the sum. Further, the text in the column heading won't affect that sum in the least.

If your headings contain numbers that may actually mess up your sums, then you can try a variation. Insert a blank row at row 2 and adjust your SUM formula so it references rows 2 through 16. (It will be row 16 instead of 15 because you added the new, blank row 2.) Then, hide row 2 so it isn't visible. Now when people add rows at the top, they will actually be adding them at row 3 and the SUM formula will still reference the hidden row 2—adjustments will occur correctly.

You could also use an approach where you adjust the SUM formula to use either the INDIRECT or OFFSET function. Assume, for a moment, that your current SUM formula looks like this:

=SUM(A2:A15)

Change it so that it looks like one of the following:

=SUM(INDIRECT("A2"):A15)
=SUM(OFFSET(A1,1,0):A15)

Both of these formulas "anchor" the top cell of the range. When you insert cells at the top of the summed range (before row 2), Excel won't adjust the cell references in either the INDIRECT function or in the OFFSET function. The result is that your SUM formula will always reference the top range cell you want.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10999) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using the CONCATENATE Worksheet Function

The process of combining string (text) values to make a new string is called concatenation. Excel provides the CONCATENATE ...

Discover More

Understanding Cell Indenting

Formatting a cell could, if you desire, also include the indentation of information within the cell. This tip examines what ...

Discover More

Deleting a Comment from a Cell

If you no longer have a need for a particular comment in a cell, you'll want to get rid of it. Here are a couple of ways you ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Finding the Address of the Lowest Value in a Range

Uncovering the lowest value in a range is relatively easy; you can just use the MIN worksheet function. Discovering the ...

Discover More

Incrementing References by Multiples when Copying Formulas

You can easily set up a formula to perform some calculation on a range of cells. When you copy that formula, the copied ...

Discover More

Using a Formula to Replace Spaces with Dashes

If you need a formula to change spaces to some other character, the SUBSTITUTE function fits the bill. Here's how to use it.

Discover More
Subscribe

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

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 4?

2015-09-28 16:32:33

Paul S.

David:
If you only insert rows, your idea should work fine. However, if you ever delete a number of rows and add only a few, at some point, your formula range may be shorter than you need.


2015-09-28 16:29:16

Paul

I regularly do very similar to Col Delane. I put a row of dashes in row 2 and a row of dashes just above the total and anchor my formula on these two rows. Unless I need the worksheet to look pretty (which is rare), I leave the rows with the dashes showing. This gives me an visual of where I need to add the rows and it gives me the immediate visual that my formula is already anchored and I do not need to worry about changing the formula as long as I insert a row between the rows of dashes.

About the only thing you need to watch out for is if you do a countif, in which case you can just subtract two rows.


2015-09-28 10:43:53

Gary Lundblad

Good ideas! For me, if the first row is a number, I would just change my formula at the bottom to include it and then subtract it out, i.e. =sum(A1:A15)-A1.

Gary


2015-09-28 08:42:49

Frustrated Fisherman

I have another method that I use in a similar situation. I have a table that is downloaded regularly, and can grow or shrink. I need to be able to reference columns in the list not just for SUM functions, but also for MAX, COUNTIF, and cetera.

So I create a range-name that applies to the data, and define that name such that it will grow or shrink as appropriate.

Assume the data are in column "A", starting at row three. Also that the number of record are not realistically going to approach ten thousand.

Using the range-name manager, I create a new name and type the following in "Refers to":

=OFFSET('Spreadsheet Name'!$A$3,0,0,COUNTA('Spreadsheet Name'!$A$3:$A$10000),1)

If I copied that correctly, what happens is that the range-name will apply to all of the data column A.
One adjustment that might be needed is to subtract from the COUNTA part the number of rows in "A" that contain something other than data. So if rows 1 & 2 have headings, and you want a SUM at the bottom, you will need to subtract three, making the formula:
=OFFSET('Spreadsheet Name'!$A$3,0,0,COUNTA('Spreadsheet Name'!$A$3:$A$10000)-3,1)

To use this in a SUM, simply put the range-name in as so: =SUM(range-name)

What I like is that the range-name can be used anywhere in the workbook.


2015-09-28 07:50:33

Col Delane

Tom:
1. The "-" only works if you select the "Transition navigation keys" option under the "Lotus compatibility" section of the Advanced Options - though what this feature has to do with sheet navigation I've got no idea! (I started my spreadsheeting career as a Lotus 123 user so retain a liking for some of its functionality)
2. If the column heading contains a number (e.g. a date) presumably it's there for a reason, so it makes no sense to convert it to text just so the Sum formula will work! That is why I recommend having a row between the header and the first data/value row to which the sum range can be anchored, and populate this cell/row with a text value to describe the unit of measure for the values in the sum range (e.g. $, #, km, lb, kg, etc.)


2015-09-28 06:43:47

Tom Van Dam

I tried the - in a spreadsheet but didn't get it to repeat as Col Delane mentioned but it does sound like a good idea. In the main article Allen says to include the top where (column headings) into the sum formula but that this causes a problem if there is a number in the row. If you put a ' in front of the number in the top row, the sum function won't recognize it as a number and ignores it. This still seems like the simplest method.


2015-09-27 08:16:20

barouh

I think that the best recommendation is to organize the table such way that it will be possible to sum the whole column - SUM(B:B)
(or to use SUMIF or SUMIFS if only some rows should be taken into the sum)


2015-09-27 02:22:15

Peter Moran

Hi Allen,

Your reference to Ctrl T does not work for me in Excel 07. Rows at the top of a Table do not seem to be picked up as you indicated.

Also re David George - Inserting "$" does not work.

A quick way of ensuring the SUM is always correct is to select the Total cell and press "Alt =" which forces the creation of a new SUM formula, or in a Table, a new SUBTOTAL formula.


2015-09-26 09:40:24

David George

I use something that I think is what Col Delane suggests.

Since I'm constantly adding rows, usually withing many rows, I always set up my formulas like this

=SUM($A$1:$A$489)

I think this accomplishes the same thing, doesn't it?


2015-09-26 08:47:08

Col Delane

A better variation on the "adjust your SUM formula" approach is to:
1. Insert a row between your column heading and the first value, enter some text for the unit of measure (e.g. $) of the values in the column and then anchor the first cell reference of the Sum range to this cell.
2. Insert a row between the last value and the Sum formula and use it for what I call “fence” by entering “-“ (which repeats the dash character to fill the width of the cell) and anchor the last cell in the Sum range to this cell. This fence is not distracting, avoids having an "orphan" blank cell just hanging there, makes hiding the row unnecessary, doubles as a border and helps identify the Sum as a sub-total or total.


This Site

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.

Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.