google sheets
I have several pivot tables that present 5-7 columns of data extracted from a sheet that has about 30 column and 5000 rows.
That sheet in turn is updated everytime a line is entered in the orders sheet.
I'm finding unacceptable response time from my spread sheet, I think due to the cascade of calculations involved.
There is no way to effectively control the frequency of update in google sheets.
How do you create a formula that can be part of a Google Sheets pivot table?
Here is an example:
It is very simple not only because just for illustration but also because Calculated Fields are not the place for complex formulae.
When we are looking to reduce the recalculation time of a spreadsheet we should try the following actions among others:
While googling for advice on spreadsheet optimization and measuring recalulation time I found that in software engineering there is the concept of "code smell" which in simple words means bad code resulting of bad programming practices. Check out Detecting Code Smells in Spreadsheet Formulas
A couple of my posts related to this topic
Note: One of them is in Spanish, try Google Translate or something similar).
Best Answer
One trick that helps a lot is to shuffle the pivot analysis off into another file
File 1 does your data entry, and contains your static files for lookups.
File 2 uses one or more IMPORTRANGE, one for each sheet it needs from file 1.
Instead of updating every minute or on every change, IMPORTRANGE runs every 30 minutes. Since I didn't need Real Time pivot tables this was an acceptable answer.
If you do need results quickly after making changes in file 1, simply reload the browser tab that has file 2.