How to handle dynamically sized pivot tables?

[MagnusNovak]

Hi everyone,

I'm struggling to find a better alternative to how I currently "use" pivot tables. Currently, I use them to list / organize data by categories / subcategories dynamically. I wish I could do all my calculations via the pivot table directly but I can't find a way to do so, so I end up writing my formulas "outside" of the table (but still making reference to it as appropriate). Attached is an example of what I'm talking about: Better Pivot.xlsx

Here, the categories / subcategories are the pivot table, but all other calculated columns are outside of the table. The reason being I couldn't find a way (even with Pivot Table's custom fields) to make these specific calculations inside the pivot.

My issue is: because the table's size is dynamic and can potentially expend further, I need to have the formulas of the other columns to continue up to the potential max size of the pivot which leads to a lot of formulas being unnecessary. Because my file has a lot of data in it (lots of transactions, lots of categories, etc), having unnecessary formulas makes the spreadsheet very slow.

Is there a better approach to what I'm doing? "A cleaner way"?

Thanks!

[avk]

Here i have prepare pivot based on your data table.