Google-sheets – How to speed up Google Sheets

google sheetsgoogle-sheets-performance

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 and size to get prices (Sheet Trees, and Sheet Standard 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.

Best Answer

When we are looking to reduce the recalculation time of a spreadsheet we should try the following actions among others:

  1. Reduce the number of formulas
    1. Avoid to do fill down/right across hundred or larger number of cells.
    2. When this it's possible, use array formulas instead of filling down/right your formulas
  2. Reduce the number of cells on the spreadsheet
    1. Remove unnecessary rows and columns.
    2. Split independent sheets into several spreadsheet files.
    3. Use FILTER(), ARRAY_CONSTRAIN() functions to get only the cells that are required for calculations
  3. Reduce the number of cells that use custom functions.

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).

Related Topic