Adding new row and automatically calculating the total amount at the bottom

eazyyexcel

New Member
Joined
Mar 1, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hello there,

Is there a way I could add a new row at the bottom of the table (just before the total sum), key in the new data and the final total will be automatically calculated? Currently I need to add a new row manually, then I copy and paste the previous row and I paste in the new row, delete all the data, and key in the amount. But I notice the total sum at the bottom is not being automatically calculated. I need to reformat the formula to add the calculations of the new row.

Would really appreciate anyone's help on this.

Thank you
 

Attachments

  • excel 1-min.jpg
    excel 1-min.jpg
    144.1 KB · Views: 8
  • excel 2-min.jpg
    excel 2-min.jpg
    143.7 KB · Views: 8

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
159
Office Version
  1. 2010
Platform
  1. Windows
Hello! Check if Automatic Calculations in the Formulas tab > Calculation group is selected:
 

Attachments

  • excel-calculation-options.png
    excel-calculation-options.png
    6.8 KB · Views: 2

eazyyexcel

New Member
Joined
Mar 1, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hello! Check if Automatic Calculations in the Formulas tab > Calculation group is selected:
Hi LazyBug

Thanks for the reply. I have checked and yes the calculation options is listed as automatic.
 

eazyyexcel

New Member
Joined
Mar 1, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I found another solution but I need to keep doing this which is annoying.

I need to keep clicking "update formula to include cells". Is there a way around this??
 

eazyyexcel

New Member
Joined
Mar 1, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Attached is the picture
 

Attachments

  • Image 12-min.jpg
    Image 12-min.jpg
    84.1 KB · Views: 4

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
159
Office Version
  1. 2010
Platform
  1. Windows
Unfortunately I can't repro your problem. Could you post a sample of your table with XL2BB to understand the structure?
 

eazyyexcel

New Member
Joined
Mar 1, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Unfortunately I can't repro your problem. Could you post a sample of your table with XL2BB to understand the structure?
Hi I have attached my file.

Now when I insert a new row at row 16, I copy the row 15 and paste it on the 16. Then I make the changes. I need the highlighted green total amount to capture the new numbers that I have added at row 16 but it isnt capturing the numbers. The new row doesnt capture the blue color "retained earnings" as well.

test.xlsx
ABCDEFGHIJKLMNOPQRST
14,000.00
2
3
4May-2017/6/2020002-1PC400.004004,000.00100100200Y
5Jun-2017/6/2020002-2PC400.00400RM 4,000.00100100200Y
6Jul-2024/8/2020002-3PC400.00400RM 4,000.00100100200Y
7Aug-2024/8/2020002-4PC400.00400RM 4,000.00100100200Y
8Aug-20002-5PC100.00100252550Y
9Sep-202/11/2020002-6PC400.00400RM 4,000.00100100200Y17/11/2020
10Oct-204/12/2020002-7PC400.00400RM 4,000.00100100200Y17/12/2020
11Nov-204/12/2020002-8PC100.00100RM 4,000.00252550Y17/12/2020
12Nov-209/12/2020002-9PC200.00200RM 4,000.005050100Y17/12/2020
13Dec-2028/12/2020002-10PC400.00400RM 4,000.00100100200Y17/1/2021
14Jan-212/2/2021002-11PC400.00400RM 4,000.00100100200Y13/2/2021
15Feb-211/3/2021002-12PC400.00400RM 4,000.00100100200N
164000.000.001000.001000.002000
17retained earnings + principal =2000
18
test
Cell Formulas
RangeFormula
G4G4=SUM(B1-F4)
G10:G15,G5:G7G5=SUM(G4-F5)
G9G9=SUM(G7-F9)
D16,H16:J16D16=SUM(D4:D15)
F16F16=SUM(F4:F14)
J17J17=SUM(J4:J15,F4:F15)
Cells with Data Validation
CellAllowCriteria
M4:M15ListCash Deposit,Cheque,Cash,Online Transfer


Thank you
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,051
Office Version
  1. 2010
Platform
  1. Windows
If you use a true Excel table you can insert a new row and everything will move down as desired.
Here's a couple of links to member sites dealing with tables that can assist you
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Adding new row and automatically calculating the total amount at the bottom
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

eazyyexcel

New Member
Joined
Mar 1, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Adding new row and automatically calculating the total amount at the bottom
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Sorry about that I wasnt aware. I will not do it again in the future
 

Watch MrExcel Video

Forum statistics

Threads
1,130,041
Messages
5,639,710
Members
417,106
Latest member
rbahena

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top