# How to extend excel formula when adding a new column

Question: I need to update and extend a large table of data with a large number of subheadings. Is there a way/workaround to add a new row to the bottom of a range and have the total formula extend.

Example: I have a range of data at A1:A4 and at cell A5 I have the total formula =SUM(A1:A4). Is there a way to add a new row, or to design the formulas, so that I can add a new row between the final value at A4 and the formula at A5 (moving the formula down to A6), so that the formula extends to =SUM(A1:A5)?

Background: I know that if this was held in a table I could add rows to the bottom of the data and the total columns would extend, but for a number of reasons table data doesn't work with my data. Also, there are totals at both rows and columns, and the table functionality is only on the columns.

My current approach is to add a new row at say 3:3, which would add a new row and extend the formula, but as this has split the data, I then and then cut and paste the rows at 4:5 to be above 3:3, rearranging the data to be back in the same order, but with the sum formulas now extended. This does the trick, but it all seems a little convoluted, and I'm sure a more efficient way.

• You can select A4 and insert Row the formula will automatically update – yass Mar 11 '17 at 13:26

Instead of `=SUM(A1:A4)` use the following formula:
`=SUM(A1:INDIRECT("A"&(ROW()-1)))`
`=SUM(A1:INDIRECT("R"&(ROW()-1)&"C"&COLUMN(),FALSE))`