Excel has Power Bi build in behind the scenes (called PowerPivot).
You use “Get data” to import data into the Data model (not a sheet).
There is almost no limit to the number of rows you can import. I have done 102 million. (should not have too many columns).
Once you have tables in the data model, you can write “measures” (calculations) on it using Dax language. Very efficient and fast.
I then create pivot tables using those measures and share the sheet with accounting folks who don’t know EM. They often copy/paste parts of these for various purposes. Often they add formulas to the side of the pivot table for various reasons.
In this case I reconcile data from external parties to our general ledger. We need to keep the Excel recons for audit purposes and they want to see how I got to the recon.
I am trying to figure out how to use EM pivots to get to the same end result.I am sure it could be done, but the learning curve… In Excel I already have the knowledge.