macos - Update sum when new rows added in Excel

I'm using Excel for Mac. I have this range of numbers in Sheet1 of a spreadsheet:

I want to sum A2:A11 in Sheet2, which works just fine:

However, if i add a new value into A12 of Sheet1, then sum in Sheet2 does not update. How can I get the sum in Sheet2 to update when new values are added at the bottom of column A in Sheet1?

Answer

There are several ways to approach this. Pick the one that works with how you plan to use column A of Sheet1.

=SUM(Sheet1!A:A)

This will add up all the numbers in column A. As long as the header in A1 is not a number, it will not affect the total. This is a good choice if you don't plan to put anything else in Column A

=SUM(Sheet1!A2:A100)

This will add up all the numbers from A2 to A100. empty cells will not affect the total. Change A100 to the address of the last cell that might be part of the data. It would be a good idea to use formatting on Sheet1 (borders or fill colours) to indicate the range that you have set aside for this use.

=SUM(Sheet1!A2:A11)

This is the formula you are already using. You can mark Sheet1 (using borders of fill colours) to remind you which cells can be used. When you want to insert another cell of data, do just that - use Insert to add another cell to the range A2:A11) The range will now be A2:A12, and the formula in Sheet2 will be automatically changed to SUM(Sheet1!A2:A12)