• Always Refer to the Cell Above

Introduction
How many times have you built a table of data, and put totals on it. You ship it out the door, and another user comes along and inserts a new row right before the totals row? If you have seen this in play, you'll know that the new row is actually inserted between the end of the data range and the totals row, meaning that your sum formula no longer picks up the entire range of data!

Fortunately, the issue with missing rows in totals can easily be fixed so that a user can insert a row immediately above your totals without missing the last line(s). Here's how:

Create a named range
The first thing we need to do is select a cell on our worksheet other than cell A1. So select cell A2 now, and create the name in your version of Excel as shown below:

Excel 97-2003
• Go to the Insert menu, and choose Name --> Define
• Enter the name CellAbove
• Change the formula to =A1
• Click Add

Excel 2007+
• Go to the Formulas tab, and choose Define Name from the Defined Names group
• Enter the name CellAbove
• Change the Scope to the current sheet
• Change the Refers To formula to =A1
• Click OK

Test the output
If you've followed the steps above correctly, you have now created a named range that can be used as a formula anywhere on the sheet. The key to this was removing the \$ signs from the formula, as those would create an absolute cell reference. Without them, we have a relative cell reference that can be used anywhere you are in the worksheet. As you had your cursor in cell A2, and referred to cell A1, the relative reference really points to the cell above the selected cell.

Let's try it out.
• Go to any cell on the sheet, and enter some data.
• In the cell immediately below the one you entered data into, enter =CellAbove and press Enter.

You should now see the same data (numbers, text, or whatever you entered,) in the two cells. Try it again with different cells!

Practical Use
Let's mock up a scenario like our original problem.
• Create a table from A1:E10 with some random numbers in it;I
• In cells A11, enter the following formula: =Sum(A1:A10)
• Copy the formula in cell A11 to cells B11:E11

We now have a table which, as shown in the formula bar of the image below, has a formula which encompasses all the cells above.

Now, right the row number for row 11 and choose Insert. You will notice that your totals row shifts down, but look in the formula bar now? See how it still only refers to A1:A10? In the picture below, I've clicked inside the formula so that it outlines the area that the formula covers (the blue outline):

Now, let's fix this little problem using our new named range. Change the formula in cell A12 to read =Sum(A1:CellAbove). Notice that now when we click in the formula for this cell it shows us the starting point highlighted in one color, and the ending point of the sum range shown in another colour.

Try entering a number in cell A11 (which is currently blank), and you'll see that it adds into the sum formula.

Now, let's prove that it works... Right click row 12 and choose Insert. The totals row will move down another row. Enter a number in row 11, and voila! It adds into the total!

Caveat
One thing to be aware of is that named ranges created in this way are local to each worksheet. So if you want to use this formula throughout your workbook, you'll need to reproduce the same named range on each worksheet.

As always, if you run into issues with named ranges, and you need to track down a rogue named range, I recommend Jan Karel Peiterse's Name Manager.

I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

3 Comments
1. Ranek -
Thanks. Found this really useful!
1. JeffreyWeir -
Ken, don't know if you're still monitoring this, but it turns out that in Excel 2013 when you insert a new row, and then fill it with data, the references adjust automatically.
1. AceOne -
Hi Jeffrey Weir, don't know if you're still monitoring this, but client spreadsheets often contain a new row /cell and the sum-formula hasn't kept up. I get such a variety of spreadsheets - ods, mac, 2003, etc.

If it weren't for the clients' unknowingness, I wouldn't have a job. Thank you Ken. Dec2018 This will now make my job easier...thank you Ken.
• Recent Forum Posts

Check cell format is Date ignore blank cells

Try this

HTML Code:
```Private Sub DateFormat()
Dim myRange As Range
Dim Celda As Range

Set myRange = Range("B2:B14")```
...

RET Today, 09:24 PM

Populate worksheets from master worksheet based on column contents

A pivot table would work. If not, you need to have a macro as with VLOOPUP you do not kown how many rows will have each sheet....

RET Today, 07:02 PM

How to perform a VLookup inside of a Protected Worksheet?

Which information are you puling? If it is not thw alloed data type, you should unprotect the sheet. If not, try to pull information to a new spreadsheet....

RET Today, 06:58 PM

Consolidate paired data sets

Start by identifying each row, not only one out of 3 and then use SUMIF...

RET Today, 06:53 PM

Formula Will Not Copy Correctly When I Pull The Formula Down the Worksheet

You are using fix reference to row by using \$ jusf before row number so it will keep fixed....

RET Today, 06:45 PM