5

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.

3
  • Have you tried to post different sheets in their own file?
    – marcanuy
    Jun 9, 2017 at 22:16
  • If your questions included on this question are rhetorical, say that as on this site each question should have only one question, otherwise the community will vote to close it as two broad. (By the way I updated my answer to include some links).
    – Rubén
    Jun 11, 2017 at 14:25
  • What I'm looking for is a guide to optimization that is more specific than the answer originally given. Jun 16, 2017 at 13:38

1 Answer 1

3

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

1
  • Sound like good tips (except for reducing formulas and somewhat for reducing cells which kind of defeat the purpose). Yet what about Query VS Filter as was asked in the OP?
    – LWC
    Oct 17 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.