Insert rows and update sum functions

Question

I have a spreadsheet that has formulas in the last 2 columns and auto sum for totals at the bottom.  I need for it to "rollover" so that depending on the # of rows needed and to make sure that a row with formulas can be added in even after data has been enteredin the last row.  I need the sum to auto include the added rows.  Can a shortcut be set up to add a row in(with formulas)?  I am thinking a macro but have no clue on needed code.

Answer 1

Here's two ways.

1. Let's say that you have values in A2:A10. Instead of putting your SUM formula in A11 and using =SUM(A2:A10), put your SUM formula in A12, and use =SUM(A2:A11). When you want to add new values at the bottom, copy row 10 and select row 11 (which is blank) and insert the copied row. The new value will be included in the SUM result when added to your table in the new row 11.
2. If you convert your range to a table, the formulas at the end of the rows in the last 2 columns will be automatically copied to any newly inserted row. But, if you do use Tables, don't manually insert the SUM functions, but use the table tools to do it after you create the table, and any inserted rows will be included, without using the trick above.