I'm looking for insights on how to make Google Sheets update more quickly. At present my spreadsheet has 22 sheets with 15 to 100,000 cells each. Mind you many cells are blank.
Cust_Orders
- has a lookup on
product code
(Sheet Trees) to fetch product descriptions. - has a lookup on
product code
andsize
to get prices (SheetTrees
, and SheetStandard Prices
).
Inventory does a query on Cust_Orders
to fill in what people have ordered. It also does lookups using Trees
and Standard Prices
.
Inv_Grouped
does a pivot table on Raw Inventory
to show what remains.
Anyway, at times when I update a number it takes well over a minute for the sheet to update.
I'm looking for a general set of guidelines to speeding up updates. A quick search for Google Spreadsheet optimization has found nothing of note.
Some of the sheets are reference only. They tend to be small, and mostly just keep similar stuff together.
After one good, but very general answer below, I'm editing this to try define what I'm looking for:
Here's a sheet that I use for trouble shooting to find all orders for a particular item:
=filter(Cust_Orders!A6:K3690,Upper(Cust_Orders!I6:I3690)=I2,Upper(Cust_Orders!G6:G3690)=G2,RegexMatch(upper(Cust_Orders!C6:C3690),C6))
Would a QUERY be better?
e.g. this is a query used to fill in the plant description:
=query(Tree,"select H where E = '"&UPPER($G3266)&"'",0)&" -- "&query(PriceBase,"select D where B = '"&I3266&"'",0)
Should I rearrange my data so that I can use VLOOKUPs instead?
Are conditional formats expensive?
Does it cost much to use named ranges instead of sheet!RC references?
Is it better to put intermediate results on a different sheet, or to put them in hidden rows or columns?
Is it worth while to try to arrange your sheet linearly so that dependencies flow in a particular direction?
There are likely a bunch of things I've not thought of.