Hi,
I have a trading statement in pivot table.
Issue, the grand total sums the row, only want opening balance at beginning of the year to display in grand total/ total column, and same with closing stock.
I have provided an example of data below and display of pivot table.
Your assistance is appreciated.
Pivot table has the following headings with row items:
Revenue items
Cost of Goods Sold items
Operational Expense items
Grand Total
In the Cost of Goods Sold items, i have a row for opening stock and a row for closing stock.
Monthly pivot
2021 Total | |||||||||||||
January | February | March | April | May | June | July | August | September | October | November | December | ||
Opening Stock | 3521 | 4000 | 3000 | 2000 | 3000 | 4000 | 0 | 0 | 0 | 0 | 0 | 0 | 3521 |
Closing Stock | -4000 | -3000 | -2000 | -3000 | -4000 | -3000 | 0 | 0 | 0 | 0 | 0 | 0 | -3000 |
Yearly pivot
2020 | 2021 | |
Opening Stock | 2950 | 3521 |
Closing Stock | -3521 | -3000 |
Data entered in worksheet:
Account Name | Transaction Date | Amount |
Opening Stock | 1/01/2021 | 3521 |
Closing Stock | 31/01/2021 | -4000 |
Opening Stock | 1/02/2021 | 4000 |
Closing Stock | 28/02/2021 | -3000 |
Opening Stock | 1/03/2021 | 3000 |
Closing Stock | 31/03/2021 | -2000 |
Opening Stock | 1/04/2021 | 2000 |
Closing Stock | 30/04/2021 | -3000 |
Opening Stock | 1/05/2021 | 3000 |
Closing Stock | 31/05/2021 | -4000 |
Opening Stock | 1/06/2021 | 4000 |
Closing Stock | 30/06/2021 | -3000 |
Opening Stock | 1/07/2021 | 3000 |
Closing Stock | 31/07/2021 | 0 |
Opening Stock | 1/08/2021 | 0 |
Closing Stock | 31/08/2021 | 0 |
Opening Stock | 1/09/2021 | 0 |
Closing Stock | 30/09/2021 | 0 |
Opening Stock | 1/10/2021 | 0 |
Closing Stock | 31/10/2021 | 0 |
Opening Stock | 1/11/2021 | 0 |
Closing Stock | 30/11/2021 | 0 |
Opening Stock | 1/12/2021 | 0 |
Closing Stock | 31/12/2021 | 0 |
Opening Stock | 1/01/2022 | 0 |
Closing Stock | 31/01/2022 | 0 |
Hello Diane,
If you can provide a sample file with this data that would be great.
Br,
Anders
Hi Anders,
The file is private, cannot not send.
Diane