A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement. But with the help of Calculated Field of a PivotTable, you can only calculate Field with Field. It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell. Because a PivotTable acts like a Table format where you can work with Field or Column Name. A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative. In this article I will show you, how you can do it.
Calculating with Formula:
Assume that, you have an Excel file with Debit and Credit transaction of an Account.
In a Bank Statement, Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calculate the cumulative balance. The formula in F5 cell for the above image is:
Copy Down the above formula to F17 Cell. The result will show you Closing Balance after each transaction. This will looks like below image:
After Calculate with Formula, it is very easy to find the Closing Balance of each transaction and also of each month. But it is difficult to view Month wise Cumulative Closing Balance.
Calculating with PivotTable:
To Calculate with PivotTable, assume that, your Bank Statement looks like this:
To view Month wise Cumulative Closing Balance, Select A3:E17 cell and Click on Insert ➪ PivotTable. No matter the location of your PivotTable. You can place the PivotTable in the same Sheet or completely New Sheet.
Place the Date field and move itto Rows box. Then place the Deposit and Withdrawal field into Values box.
Click on the Count of Deposit and Count of Withdrawal button in Values box, and Click on Value Field Setting menu. This will show a dialog box. Click on SUM and Click on Ok.
Now the Report will looks like this:
Insert a Calculated Field named: Balance and the calculation is =Deposit - Withdrawl. After adding a Calculated Field, the report will looks like:
Right Click on D4 cell and Select Show Values As | Running Total In.
This will show a Dialog box "Show Values As (Sum of Balance)". Click on drop down list of Base Field: and Click on Ok.
This will calculate the Cumulative Closing Balance like a Bank Statement that relates with previous balance:
Image 1: Sample Bank Statement
In a Bank Statement, Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calculate the cumulative balance. The formula in F5 cell for the above image is:
=(F4+D5)-E5
Copy Down the above formula to F17 Cell. The result will show you Closing Balance after each transaction. This will looks like below image:
Image 2: Calculated Closing Balance with Formula
After Calculate with Formula, it is very easy to find the Closing Balance of each transaction and also of each month. But it is difficult to view Month wise Cumulative Closing Balance.
Calculating with PivotTable:
To Calculate with PivotTable, assume that, your Bank Statement looks like this:
Image 3: Sample Bank Statement
To view Month wise Cumulative Closing Balance, Select A3:E17 cell and Click on Insert ➪ PivotTable. No matter the location of your PivotTable. You can place the PivotTable in the same Sheet or completely New Sheet.
Place the Date field and move itto Rows box. Then place the Deposit and Withdrawal field into Values box.
Image 4: PivotTable Field Setting
Click on the Count of Deposit and Count of Withdrawal button in Values box, and Click on Value Field Setting menu. This will show a dialog box. Click on SUM and Click on Ok.
Image 5: Value Field Setting SUM
Now the Report will looks like this:
Image 6: PivotTable Report
Insert a Calculated Field named: Balance and the calculation is =Deposit - Withdrawl. After adding a Calculated Field, the report will looks like:
Image 7: Calculated Field
Right Click on D4 cell and Select Show Values As | Running Total In.
Image 8: Running Total In
This will show a Dialog box "Show Values As (Sum of Balance)". Click on drop down list of Base Field: and Click on Ok.
Image 9: Show Values As (Sum of Balance)
This will calculate the Cumulative Closing Balance like a Bank Statement that relates with previous balance:
Image 10: Cumulative Closing Balance like a Bank Statement