How to Fix Slow Google Sheets

Post date: Nov 05, 2018 5:4:8 PM

If your Google sheets are  taking forever to load or process data, then these 20 Optimisation strategies will be able to help you.

• 1. How to know if Sheets are Slow

1. Google Sheets Limitations. 

2. Check size of Google Sheets

3. Deleting un-used row\columns

4. Converting formulas to static numbers to improve speed

5. Using closed ranges

6. Use volatile functions with care or eliminate them 

7. VLOOKUP Optimization

8. Import formula optimisation

9. Use IF to optimise formula calls

10. Using a Control Switch to manage extensive formulas

11. Use Filter, Unique and Array_Constrain to create helper tables

12. Avoid Calculation Chains

13. Use Conditional Formatting sparingly

14. Split & Combine your slow Google Sheets to speed them up

15. Archive Old Data

16. Use the power of Google Apps Scripts

17. Other Troubleshooting Tips

18. Know when it’s time to move to a database

19. CONCLUSION

1. How to know if Sheets are Slow: Check for these behaviours:

• Calculations become very slow and loading bar appears frequently every time you make a change in the sheet.

• • Sheets are slow to respond to mouse or keyboard clicks.

  • Data does not show even after you have entered the same into a cell. You need to reload the sheet sometimes to have it show.

  • Sheet becomes un-responsive. You may get an error message like the one below or you may need to re-load the page.

  • Computer heating up, using a lot of RAM & battery. 

2. Google Sheet Limitations: Know these limitations inherent in Google Sheets -

• • Google Sheets has a limit of 2 million cells per workbook (see Details Here).

  • Google Sheets has a maximum number of columns of 18,278 columns.

  • Within a single cell, there’s a maximum string length of 50,000 characters.

  • You can add a maximum of 40,000 new rows at a time.

3. Check size of Google Sheets

• • Using a special  audit tool you can see the total number of cells in the Sheet, how many have data in them and then statistics for each individual sheet too, including how many volatile and array functions you have.

4. Delete Unused Rows\Columns

• • Google Sheets get slower as they get larger. So one of the first things to try, is to reduce the size of your Google Sheet.

  • Blank rows\columns slow down performance, dramatically so in fact. So it’s a good idea to delete them whenever you can, so you reduce the number of cells Google Sheets is holding in memory.

  • Ideally you should remove any large numbers of blank rows under your data, or columns to the right of your data.

  • Especially for pages getting input from Forms you can delete all the rows under the data as every Form Input creates a new row for itself.

5. Convert formulas to static values wherever possible

• After you have used a set of formulas in your data, you don't need them again, you should convert them to values. This is also true for columns like S.no etc.

• This will reduce the file size, which will help performance overall, and it’s also best practice (to reduce errors occurring if someone or something happens to break the formulas).

• You change formulas to values by copying them and then:  Edit > Paste special > Paste values

• Do keep a copy of a “live” formula at the top of your columns of data in your tables, so you have a record of how the calculation was performed and can also very quickly re-use it if needed:

6. Use closed range references

• It's better to use Closed range references  like A1:B1000 instead of open references like A:B in your formulas wherever possible. Open references add  extra calculation overhead to your Sheet.

• It might not look like there’s much data in column A, but Sheets will check every cell, including all the blank ones, if you use open ranges like A:B.

7. Remove volatile functions or use with caution

• Remove volatile functions or use with caution - NOW(), TODAY(), RAND() and RANDBETWEEN() as they recalculate every time there’s a change to the Sheet. All dependent formulas must also be recalculated too (i.e. formulas that reference or include a nested volatile function).

• This uses up processing power and so can negatively impact your Sheet’s performance. Best idea is to put these functions in a cell like A1 & then you can refer to the cell in any formula using $A$1 (absolute reference) where required. This will significantly increase the speed.

8. Vlookup Optimization

• Use closed, rather than open, range references for your search tables where possible.

• Reduce the size of your lookup table, if possible. For example, rather than search across everything you might use the Filter function to create a smaller, helper table for the lookup first, and then use that in your VLOOKUP.

• Don’t nest the FILTER (or other) function inside your VLOOKUP and create the table on the fly each. It’s much, much slower.

• For example, this formula is bad practice and will really slow down your Sheet if you have a lot of them:

• =VLOOKUP( $A1 , UNIQUE( FILTER( $K$1:$N$10000 , $K$1:$K$10000 < 100 )) , 2 , FALSE )

• whereas creating that nested Filter table as a helper table and referencing it is the preferred method:

• =VLOOKUP( $A1 , $F$1:$I$10 , 2 , FALSE)

9. IMPORTARANGE Optimization

• The ImportHtml, ImportFeed, ImportData, ImportXml and ImportRange formulas pull data from sources external to your Google Sheet, so they require an Internet connection to function and are slower than other functions that operate entirely within your single Sheet, so they will potentially affect the performance of your Sheet.

• For example, using ImportRange to import large amounts of data from another Sheet will take time and you may see the Loading… error message for a while:

• The best idea is to minimize the number of times importrange formula is used. It may be best to get the entire data in one go in a hidden sheet and then use the data for reference in other sheets instead of having multiple use of import range.

•  

• Note, you’re limited to a maximum of 50 of ImportData functions in a single spreadsheet. Also note, that these functions recalculate as follows :

• ImportRange: Every 30 minutes

• ImportHtml, ImportFeed, ImportData, ImportXml: Every hour

10. Use IF statements to optimise formula calls

• • You can reduce the number of lengthy calculations being done in Sheets by using the IF Statement to check for certain preconditions. For example - only perform a Vlookup if the cell is not empty:

  • = IF( A2="","", Vlookup (A2.....))

  • The same can be done for any formula. Other examples could be to check if a certain Value is larger or smaller or check for a certain text to do length calculation only if required. 

        = IF( A2>=1000,Filter("Sheet1!....),"NA")

11. Using a Control Switch to manage extensive formulas

• You can actually turn Calculation on & off using a control switch cell with the IF statement. This way the sheets will not slow down as they will only start calculating the desired formula when you actually need them to. 

• For example - Use data validation to create a drop-down menu in a single cell, the control cell, with values “Process” and “On Hold”.

• Then use an IF formula like this to only process the performance-hungry formulas in your slow Google Sheets when you want to:

• =IF( $A$2 = "On Hold" , "On hold" , your_formula_here )

• Here’s an example of this control switch technique applied to some performance-hungry Instagram IMPORTXML formulas (for the top 25 accounts):

12. Use Filter, Unique and Array_Constrain to create helper tables

• • The Filter, Unique and Array_Constrain functions can be used to create smaller helper tables for further data analysis work.

• If you had a big table with 150,000 rows of data in, but were only interested in performing calculations on a smaller subset meeting certain parameters like "New/Old", "Country/City etc." , then you could use these functions to reduce the size of your table and create a new helper table for your calculations.

  • For example, you might use the Filter and Unique functions to create a helper table from a dynamic subset of your larger table:

=UNIQUE( FILTER( A1:E100000 , B1:B100000 = "India"))

        or using Array_Constrain to create a helper table from a static subset of your larger table:

=ARRAY_CONSTRAIN( A1:E100000 , 10 , 4 )

13. Avoid Calculation Chains

• Avoid calculation chains of cells that reference each other in a long chain, for example a long column of cells equal to the cell above, like so:

• • It's better to reference a single cell with absolute references and then copy that down your column, e.g.

= $A$2

14. Use Conditional Formatting sparingly

• Conditional formatting can be very slow on large datasets because it’s implemented on a cell-by-cell basis. So it’s best to use it for small data tables and in your dashboard reports. You will see immediate improvement by removing the same from large data sets.

15. Split & Combine your slow Google Sheets to speed them up

• Assuming we’re sticking with Google Sheets though, it might be time to split up your huge dataset into separate Sheets if your Sheet is so slow that it’s frustrating to use.

• This is especially true if you are importing large amounts of data from other sheets. If you are importing data from say 16 separate files then instead to importing directly into one single file it may be an excellent idea to make 3 or 4 separate sheets and each one imports data from 3-4 sources. You can them bring the data from just 3-4 sources to your main sheet. This will reduce the calculation load on the main sheet and make things much faster.

16. Archive Old Data

• Once your sheet starts getting too much data and is becoming sluggish it may be a good idea to Archive the old data into a separate sheet for future reference. Maintain the past Months, Quarter or Year's data as your need may be. 

• The best approach to split & archive your really big Google Sheets is to make new copies, label each Sheet clearly and then delete all the data apart from the data relevant to that Sheet. For example, if you’re offloading data from 2017 into it’s own Sheet, then here are the steps:

  • • Make a copy of the master Sheet, with all your data in

    • Name this new Sheet and include “2017” somewhere in the title

    • Sort the data by year in this new Sheet

    • Add a filter and show everything except 2017 data (i.e. hide the 2017 data)

    • Delete all these rows

    • Remove the filter to leave only the 2017 data

• • Once you’ve completed all the steps for all your archived data, you’ll want to remove it from your master Sheet. I’d advocate keeping a full copy of your master Sheet before you start deleting any rows of data though.

  • You’ll end up with a master Sheet that just has your current, or live, data in it, and it’ll hopefully be much faster.

  • You can also do any calculations or data analysis locally and then bring the data back together in your master Sheet using the IMPORTRANGE formula.

17. Use the power of Google Apps Scripts

•  Google Apps Script can be used to extend the functionality of Sheets.

• Apps Script is extremely effective at doing big batches of calculations, especially repetitive ones, often more quickly than thousands of regular formulas.

• Detailed instructions on the same are out of scope of this article, however we do conduct in-dept tutorials for the same. You can send you inquiry using this LINK to us for training on Google Apps Scripts. 

18. Other Troubleshooting Tips 

Sometimes it might not be an issue with Google Sheets themselves, try these strategies to see if any resolve your issues:

• Close and re-open the Google Sheet (sometimes it’s the simplest fixes that work).

• Refresh your browser. Close other tabs, opening too many tabs will slow down your computer as each Tab uses a large amount of RAM.

• Clear your cache and cookies. 

• Try another browser or operating system. Make sure both browser and operating system are currently running on their latest version.

• Disable browser plugins or extensions or try accessing the sheet in Incognito\Private mode.

• Try replicating the issue on another computer to see if it’s the computer hardware having issues. A Google Sheet’s calculation speed depends mostly on the local resources.

• Try replicating your issue on another network to see if it is a network/internet issue. Most functions are run locally in the browser but some, like the IMPORT formulas, require access to the Internet (as well as any Apps Script which is run on Google servers).

• Try disabling and re-enabling offline access.

• Try on a different or a faster computer.

19. Know when it’s time to move to a database

There will come a time, when Google Sheets won’t fulfill all of your data needs. There is the 2 million cell limit that may fill up pretty quickly with real-world data. Before you hit the  2 million limit you should think through the next steps beyond slow Google Sheets. You’re going to want to move your data into a database archive. You can still work in a Sheets environment but offload the data storage to a dedicated database.

Google Database Options

Within the Google ecosystem, we’re talking about BigQuery and Cloud SQL, both cloud databases. However, the integration isn’t simply drag-and-drop anymore, so you’ll need development skills to connect these services.

Google BigQuery is integrated with Google Drive, so you can use your Google Sheets as tables in BigQuery. You can also use the BigQuery Apps Script service to manage your BigQuery projects.

Connecting a Google Sheet to Cloud SQL directly requires Apps Script, although you could export data from Google Sheets (e.g. as CSV) and import that into Google Cloud SQL.

Detailed instructions on the same are out of scope of this article, however you can send your inquiry using this LINK to us for more details & support on this Topic. 

20. CONCLUSION

I hope that you will find a lot of value in the above article. Google Sheets are only a small part of the Tech Ecosystem  that can help you to convert technological threats into an opportunity, re-invent themselves & transform their industry.

If still find yourself struggling even after doing lengthy & expensive trainings then probably you lack the Thought Process & Tools for Exponential Growth. 

STOP trying to Re-Invent the Wheel ! 

Check out our  100% Practical & Transformational Coaching that have turned around hundreds of businesses.

CLICK HERE

Remember: You need to work Hard in Business but Struggle is optional !