0

I have a large complex spreadsheet with ~200 tabs. It opens/loads very slowly (upto 5 minutes) due to multitudes of formula calculations. I am trying to optimize the formulas so the spreadsheet will open/load faster.

One of the most frequent calculations is to multiply about 60 cells in each tab by a variable in 'Sheet1!B4' (Sheet 1, cell B4). I expect this value to change maybe once in a year or so, which would require updating all 200 tabs in at least 60 cells each.

Will it be better to hard-code the value, and take the hit for updating it once a year in all the affected cells in all the tabs?

Or is it ok to reference it in some way, which does not impact the performance, and preferably makes it faster?

Here are the three options I am considering:

  1. Hardcoded value: =countif(C$10:C$30,$B60) * 10

  2. Reference cell: =countif(C$10:C$30,$B60) * Sheet1!:B4

  3. Use Named Range of a single cell: =countif(C$10:C$30,$B60) * PARAMETER_VAL where PARAMETER_VAL is a named range referencing Sheet1!B4

Which of the above would be the fastest?

Is there any other way to make it faster, that I may be missing?

J. Doe
  • 46
  • 5
  • This might be better suited to webapps.stackexchange.com . Have you tried using array formulas to expand into neighboring cells? The fastest manner will always be to remove the formula entirely, leaving only a static value (i.e. moving the calculation to an apps script function that reads input from various cells on various tabs, computes the result arrays, and then writes the given array on the given tab). – tehhowch Mar 01 '18 at 23:58
  • If you know there are cells that are rarely updated, you may be better off computing those with a custom Apps Script function and execute them only when you need to or on a schedule. – Argyll Mar 05 '18 at 04:43
  • Thanks for your comments. tehhowch and Argyll , these cells are dependent on other cells which are updated 1-2 times a day. People would like to see real-time updates. I could probably use onEdit trigger, but it might be triggering multiple times, and the script might time-out or overlap with someone else's edits... I don't think it is feasible, unless there are finer points I am missing. @pnuts Thanks for catching that, I have updated the post now :-) – J. Doe Mar 08 '18 at 03:38
  • for such big speadsheets I think it's much more suitable to use a proper database – phuclv Mar 08 '18 at 03:58
  • @LưuVĩnhPhúc I agree. We are in the process of moving to a MySQL database, but until then... – J. Doe Mar 11 '18 at 05:22

1 Answers1

1

I don't think that any of the three alternatives will have significative difference on the spreadsheet performance because the reference / named ranges point to a cell with a fixed value.

Related Q&A

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thanks for the link... I followed it and found some interesting threads. However, performance cannot be measured simply by t2-t1 in a production environment, especially with Cloud. It is difficult to predict the load on the servers and the network at any given moment of time, as well as unknown design variables like Google's cache-refresh rates etc. My best option would be to actually understand how the internal caching is implemented for various function calls, or do the testing in an isolated network environment. – J. Doe Mar 08 '18 at 03:51