Slow Google Sheets?

We’ve all been there, stuck watching the little loading bar creep slowly, frustratingly to it’s conclusion:

How can you speed up a slow Google Sheet?

First off, this is a *difficult* question to answer because there are so many factors that may or may not be causing you to have a slow Google Sheet.

What follows in this article is some suggested optimization strategies and some research into what causes slow Google Sheets.

## Strategies to speed up Google Sheets

- How to recognize slow Google Sheets
- Know the size limits of Google Sheets
- Measure a Google Sheet’s size
- Measure a Google Sheet’s calculation speed
- Delete un-used cells
- Convert formulas to static values wherever possible
- Use closed range references
- Remove volatile functions or use with caution
- Vlookup strategies
- Index-Match strategies
- Query function strategies
- Array Formula strategies
- Import Formula strategies
- Google Finance function strategies
- Use IF statements to manage formula calls
- Manage expensive formulas with a control switch
- Use Filter, Unique and Array_Constrain functions to create smaller helper tables
- Avoid long calculation chains
- Reference data on the same Sheet
- Use helper columns
- Split your slow Google Sheet into separate Sheets
- Use Conditional Formatting sparingly
- Leverage the power of Apps Script
- Use custom formulas sparingly
- Other troubleshooting tips for slow Google Sheets
- Understand changes in the cloud can take time to propagate
- Know when it’s time to move to a database

I remember the old days when you would set hundreds of thousands of VLOOKUP formulas loose on your dataset and then go out for lunch. If you were lucky they’d be finished when you returned. (And woe betide anyone who made a mistake with a formula!)

Although we’ve come a long way from those days, if you work with data a lot, chances are you’ll find yourself coming up against slow spreadsheets at some stage.

A lot of the tips that follow are generally good spreadsheet practices anyway, so even if you only work with small Sheets at the moment, they’re worth implementing now.

## 1. How to recognize slow Google Sheets

If you’re reading this, chances are you’ll recognize some or all of the following issues.

**Calculations in your Google Sheets are super slow** and the dreaded loading bar makes an appearance every time you make a change to your Sheet.

Your **Google Sheet becomes sluggish** to respond to mouse clicks or keystrokes.

**Data does not show in cell**, even though you know you’ve entered data into the cell. For example, have a look at this formula that simply didn’t show a value after being entered:

**Your Google Sheet becomes unresponsive.** This is the worse case scenario, and in my experience, if this doesn’t resolve fairly quickly, you don’t often recover from here.

So if you see this error message, and you’ve waited a few minutes but nothing’s happening, then you might want to just bite the bullet, exit the page, and pickup again but trying a different approach this time.

This is usually accompanied by your computer going into overdrive with the fan whirring loudly!

## 2. Know the size limits of Google Sheets

Obviously if you’re dealing with large Google Sheets — Google Sheets with large amounts of data and/or formulas — then you’re more likely to see your performance suffer.

No discussion of slow Google Sheets therefore, would be complete without first discussing the size limits of Google Sheets.

#### Overall limit:

Google Sheets has a limit of 5 million cells per workbook (see Google file sizes).

If you do something that will take you past this limit (e.g. adding new rows or a new Sheet), you’ll see this error message:

#### Column limit:

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

If you do something that would take you past this limit, you’ll see the following error message:

#### Individual cell-size limit

Within a single cell, there’s a maximum string length of 50,000 characters (enough for approximately 500 average sentences, or about 162 Tolstoy sentences).

If you do something that would take you past this limit, you’ll see the following error message:

#### Max number of rows you can add in one go

You can add a maximum of 40,000 new rows at a time (you can do this as many times as you want though, up to the 5 million cell limit).

If you try to do more than that, you’ll see the following error message:

## 3. Measure a Google Sheet’s size

Using Apps Script, you can quickly build a simple tool to calculate the size of your Sheet.

This Sheets audit tool shows 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 *(CLICK TO ENLARGE)*:

**Bonus:**Get your own copy of this Google Sheet Audit Tool to measure the size of your Google Sheets.

The code for this tool can also be found here on GitHub if you prefer.

I’ll do a post in the future looking specifically at how I built this tool with Apps Script.

New to Apps Script? Check out my beginner guide to Apps Script.

So that’s measuring the size of Google Sheets, what about measuring their speed?

## 4. Measure a Google Sheet’s calculation speed

To measure the “speed” of various calculations you can make use of Chrome’s Developer Tools.

In your browser window, you can access the Developer Toolkit by pressing ** Cmd + Option + I** on a Mac or

**(on a PC). It can also be accessed in the View > Developer menu.**

`Ctrl + Shift + I`

Under the **Performance** tab of the Developer Tools you can record the webpage (or Google Sheet in this case) as it loads, and check the screenshots box to show you what is happening and when.

For formula calculations, you can identify when the calculations start, e.g. marked by the 1 in this image, at around the 3.6 seconds mark:

The number 2 shows a screenshot at this point in time, so you can see that your formulas are not showing yet.

You can identify when the formulas have finished calculating (the red box, around the 8.6 seconds mark):

Check the screenshot to see that the formula results are showing in the cells now.

And so you an see how long this batch of formulas took to calculate:

8.6s – 3.6s = 5s

There’s a whole lot more to the Developer Tools, but that’ll do us for today.

This is the methodology I’ve used to calculate formula speeds for comparison tests. I ran all of the tests in the same place at the same time of day (so wifi speed discrepancies were negligible) and also ran them each multiple times and took averages.

## 5. Delete un-used cells

Your Google Sheets will get slower as they get larger.

So one of the first things to try, before getting into the marginal gains of formula optimization, is reduce the size of your Google Sheet.

Blank cells 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 datasets, or columns to the right of your datasets.

**What about new rows being added automatically?**

Don’t worry if your data is being imported automatically by Apps Script or a third party tool like Supermetrics or Tiller, as the new rows are automatically added to your Sheet as required.

*(However, you should always test this theory with your specific setup or app, to confirm this is the case.)*

## 6. Convert formulas to static values wherever possible

Once you’ve used a set of formulas in your data, and you’re sure you won’t need them again, you should convert them to values.

This should 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**

Better yet, learn the keyboard shortcut to save yourself time. Highlight the formulas to change, **Ctrl + C** on a PC or **Cmd + C** on a Mac to copy and then **Ctrl + Shift + V** on a PC or **Cmd + Shift + V** on a Mac to paste as values.

Note, I advocate keeping 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:

## 7. Use closed range references

Closed range references means using something like `A1:B1000`

instead of `A:B`

in your formulas (i.e. you explicitly specify the boundaries of your range).

If you’re working with large Sheets and are using open references where you don’t need to, this will be adding 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.

**Note: **Whilst this is generally a good idea for speeding up your formulas, there are obviously situations when an open reference is desirable, for example when new data is anticipated. Leaving an open range will allow any new data to be included in calculations.

## 8. Remove volatile functions or use with caution

In Google Sheets there are four functions, `NOW()`

, `TODAY()`

, `RAND()`

and `RANDBETWEEN()`

, that are known as *volatile* functions, which means 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, although this is only going to be noticeable if you have large numbers of them.

To illustrate, I copied 100,000 `TODAY()`

functions down column A, which took my machine about 5.5 seconds.

I then deleted 99,999 of these formulas and just left `TODAY()`

in cell A1 and then put the following formula in cell A2:

`= $A$1`

and then copied this one down. The dollar signs represent absolute references and keep all my formulas locked onto cell A1.

Running this formula down 100,000 cells in column A took around 4.1 seconds, so almost 1.5 seconds quicker.

It’s also best practice to do this, because if I ever need to make a change to the date, then I can just change it in this one place, A1, and not have to redo all of my formulas.

## 9. Vlookup strategies

Use closed, rather than open, range references for your search tables where possible (see no. 7 above).

Reduce the size of your lookup table, if possible, although this only has a marginal positive affect in my experience. For example, rather than search across everything you might use the Filter function (see no. 17 below) to create a smaller, helper table for the lookup first, and then use that in your VLOOKUP.

However, don’t be tempted to 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)`

Combining VLOOKUPs with Array Formulas to return multiple column values with a single lookup is significantly slower than simply using multiple individual VLOOKUPs.

In Excel, sorting your data and using approximate matching (TRUE as your final argument in the VLOOKUP) is quite a bit faster than the exact matching algorithm (FALSE as the final argument). It’s slightly quicker in Google Sheets too, but only slightly it seems, so it’s not worth the extra hassle.

Another Excel VLOOKUP trick is to use two approximate matching VLOOKUPs instead of a single exact match. It’s significantly faster because the underlying search algorithm is much, much faster. However, I found this technique was actually slightly *slower* on Google Sheets so I wouldn’t recommend it.

## 10. Index-Match strategies

The Index-Match is a powerful lookup combination and well worth learning (learn it in day 10 of my free Advanced Formulas 30 Day Challenge course).

In it’s classic construction, you might see formulas like this:

`=INDEX( Sheet2!$A$2:$P$51 , MATCH($A3,Sheet2!$G$2:$G$51,0) , MATCH(C$1,Sheet2!$A$1:$P$1,0) )`

where you have two nested MATCH functions to lookup the row and column numbers (which is why index-match-match is so flexible).

This is fine for small numbers of index-match-match formulas, but inefficient at scale.

Say you’re looking up 10,000 rows and 10 columns. For every single cell, you’re using two matches to lookup the row number and the column number, so your total number of calculations would be:

`No. of Match functions for row +`

No. of Match functions for column +

Index Match function =

```
```(10,000 rows * 10 matches) +

(10,000 rows * 10 matches) +

(10,000 rows * 10 indexes) =

`300,000 calculations`

Much better to **split the match lookups out** into their own helper row and column rows, as shown in this example:

By splitting both the match lookups into their own row and column, you can compute them all once first, and then use those numbers in your index function.

Now you’ll only have to calculate the index function when you do your calculation, so your total calculations now would be:

`(10,000 matches for the rows) +`

(10 matches for the columns) +

(10,000 * 10 indexes) =

```
```

`110,010 calculations`

Significantly less than before, only about 1/3 as many calculations to do!

## 11. Query function strategies

As alluded to above , using open range references can hurt performance because you may inadvertently include thousands of extra blank rows in your calculations (see no. 7 above).

Instead, use closed range references or create smaller helper tables as inputs to your Query functions to speed up performance.

For example, running tests with a QUERY function, I found that each additional 20,000 empty rows I was including with open ranges would add 1 second to the calculations.

So rather than a formula like this for example:

`=QUERY( Sheet2!A:E , "select *" , 1 )`

you’d be better served by specifying the range explicitly:

`=QUERY( Sheet2!A1:E100000 , "select *" , 1 )`

This improves the Query function speed because the calculation is no longer including all of the blank rows in `Sheet 2`

under the dataset.

## 12. Array Formula strategies

If you spend long enough reading help forums you’ll find people saying that using a single array formula to replace hundreds of individual formulas will be quicker, and others staunchly saying the opposite. So which is it?

In my experience, array formulas working on big datasets (those with lots of rows) are slow and will make your Sheet sluggish. I’ve found they tend to be slower than the individual formulas.

However, they’re so useful in specific situations, like gathering Google Form data, that I still recommend learning about them and using them, with caution.

I use them heavily for performing running calculations on datasets that grow over time (new rows being added). That way I know the calculation happens without me needing to update anything.

For example, this formula:

`=ArrayFormula( IF( A2:A <> "" , A2:A * 100 , "" ))`

which is the array version of this individual formula:

`= IF( A2 <> "" , A2 * 100 , "" )`

will automatically compute a value for every new row of data added.

I would test both versions on your own sheets, and choose whichever is most appropriate (weigh up speed versus array benefits of automatic calculation). The individual formula approach has the added benefit of being easier to debug and setup.

If you want to keep array formulas (and I hear ya!) consider archiving historic data periodically (see no. 21 below), to keep the live dataset manageable.

## 13. Import Formula strategies

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.

They’re 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 IMPORTRANGE is a slow formula because it’s connecting to another Sheet to retrieve data. In general, it’s best to minimize the number of these external calls required.

However, when your data starts to get really big (around 20,000 rows), the IMPORTRANGE formula will just get stuck at the `Error Loading data...`

stage.

In this case, use multiple ImportRange calls to retrieve your data.

Combine them into a single formula using the `{ ... ; ... }`

array notation, for example in this formula which gets the first 15,000 rows and then the next 15,000 (up to 30,000) from Sheet 1 of our source Sheet:

`={ IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UrtOONA8B2pNYCI1byDA__R9_Bbz0GWrfxP_MDotaTY/edit#gid=1789207765","Sheet1!A1:E15000") ;`

IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UrtOONA8B2pNYCI1byDA__R9_Bbz0GWrfxP_MDotaTY/edit#gid=1789207765","Sheet1!A15001:E30000") }

**Other Notes**

Note, you’re limited to a maximum of 50 of ImportData functions in a single spreadsheet (link).

None of the other Import formulas have an explicit limit like this on their help pages and some limited testing suggests that if there are any limits, they’re certainly greater than 50.

By default, these functions recalculate as follows (see documentation):

**ImportRange**: Every 30 minutes**ImportHtml, ImportFeed, ImportData, ImportXml**: Every hour

## 14. Google Finance Function strategies

The `GoogleFinance`

function is another function that retrieves data from an external source, so requires an internet connection. Like the Import formulas above, it will be slower than regular functions operating inside your Sheet only.

Again, the general strategy here is to reduce the number of external calls you make with GoogleFinance function, i.e reduce the number of GoogleFinance functions you use.

So think about retrieving a whole year of stock prices with a single formula, rather than hundreds of daily functions for example.

Per the documentation, GoogleFinance data may be delayed up to 20 minutes.

## 15. Use IF statements to manage formula calls

You can use an IF wrapper to check whether a calculation needs to be performed before doing it.

#### Example

Say for example you have a table of product data that includes books, and you want to use a vlookup to bring in book sales data alongside each book.

So you set your VLOOKUP up to search for the ISBN number (the book’s serial number) in the sales data table and return the sales value. The inefficiency comes because you’re searching for all your products, not just books, so all those products that have N/a in the ISBN column are still be searched for.

Instead, check first for the ISBN number and then only perform the lookup if you have a valid ISBN.

The formula could look something like this, where you first check that the ISBN lookup value is not equal to **N/a**:

`= IF( A2 <> "N/a", your_vlookup , "Not a book")`

or more generally:

`= IF( test , calculation_if_test_true , output_message_no_calc )`

## 16. Manage expensive formulas with a control switch

Use a control switch cell with the IF statement method from above, to keep things manageable.

Use data validation to create a drop-down menu in a single cell, the control cell, with values “Process” and “On Hold” (or whatever makes sense for you).

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

## 17. Use Filter, Unique and Array_Constrain functions to create smaller helper tables

The `Filter`

, `Unique`

and `Array_Constrain`

functions all accept ranges (tables) as inputs and return ranges (tables) as outputs, that are smaller than the input tables.

Thus they are useful to create helper tables for further data analysis work.

Say you had a table with 100,000 rows of data in, but were only interested in performing calculations on a subset of that data, then you could use one or more of 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 < 100 ))`

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

`=ARRAY_CONSTRAIN( A1:E100000 , 10 , 4 )`

## 18. Avoid Long Calculation Chains

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

I created a column with 100,000 copies of this formula, referencing the cell above, and it was NEVER able to show all of the formula answers, even though the underlying formulas were showing in the cells.

I had to close the file, wait a moment and re-open to see the completed results.

Interestingly, this blank cell issue always occurred at the 99,100 occurrence of the formula. Here you can see the formula referencing the cell above (A99102) but not showing the value 1 like the cells above are showing:

(I hit this 99,100 limit in all the various chaining tests I did, which makes me think it’s some kind of limit to what Sheets can handle for linked cell calculations.)

It’s slow and may not even complete the calculation under certain conditions.

Aside from that, it’s also very bad practice to chain calculations like this. One error will cascade through the entire chain and it’s also difficult to debug.

A better practice would be to reference a single cell with absolute references and then copy that down your column, e.g.

`= $A$2`

## 19. Reference data on the same Sheet

Referencing data in the same sheet keeps things simple — always a good practice — and saves time because your formulas can access the data more quickly.

Obviously it’s not always practical or convenient to reference data in the same tab of your Google Sheet, but keeping references within your one file will be quicker than using IMPORTRANGE to bring in data form other Google Sheet files.

That said, below I discuss the pros of splitting up large, slow Google Sheets (see no. 21 below) and using IMPORTRANGE to bring them back together as necessary.

## 20. Use helper columns

Tempting as it is to create clever formulas that do everything in one go, there are many advantages to creating helper columns. Not only are they easier to create, they’re easier to follow (for you and colleagues), easier to debug, and they can even be faster sometimes too.

Suppose you have a list of a website’s URLs in column A and you want to quickly extract the path, the part of the URL after the www.website.com address.

You might use this formula in column B:

`=RIGHT( A1 , LEN(A1) - SEARCH( ".com" , A1 ) - 4 )`

Now, since the hostname is the same in each case in this example, we’re needlessly calculating the nested SEARCH function every time.

We could run this once, figure out that our hostname was 24 characters long (and store this in a helper column to ensure best practices), and then replace the SEARCH function with this constant ( 24 digits + 4 digits for the “.com”):

`=RIGHT( A1 , LEN(A1) - 28 )`

## 21. Split your slow Google Sheet into separate Sheets

First of all, it may be time to consider warehousing your data in a proper database and not Google Sheets if you are getting close to that 5 million limit (see no. 27 below).

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 technique should be approached with **CAUTION** though, to avoid data loss. Only do this if you’re confident you know what you’re doing and you’re sure you really need to.

Be aware that this approach comes with a cost also. Before you had one single table to keep track of, to modify, update and analyze, whereas now, if you split it up, you’ll need to keep track of multiple Sheets and ensure that changes and analysis are consistent.

A safe bet for this approach might be to offload prior years of data say, or old products that are no longer in your inventory. In other words, it’s data we don’t envisage using again, but that we want to keep a copy of.

#### How to safely split up slow Google Sheets

The best approach to split up 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 2015 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 “2015” somewhere in the title
- Sort the data by year in this new Sheet
- Add a filter and show everything except 2015 data (i.e. hide the 2015 data)
- Delete all these rows
- Remove the filter to leave only the 2015 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.

## 22. Use Conditional Formatting sparingly

Conditional formatting is a super nice feature for adding extra context to your Google Sheets, for example highlighting outliers or values above thresholds. I use a lot in my scheduling sheets to highlight the row for the current day for example.

However, it can be very slow on large datasets because it’s implemented on a cell-by-cell basis.

So it’s best used for small data tables and in your presentation tables and dashboard reports.

## 23. Leverage the power of Apps Script

Apps Script is a Javascript based scripting language that can be used to extend the functionality of Sheets (and interact with other Google services). Check out this Beginner Guide to Apps Script if you’re new to it.

The possibilities here are extremely broad, encompassing everything from automating repetitive processes to speed up your workflow to creating custom functions to save you typing out complex formulas over and over (see next section).

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

#### Example

Imagine I have a column of numbers that I want to classify into Small, Medium and Large using a nested IF statement like this:

`=IF( A1 > 500 , "Large" , IF( A1 > 250 , "Medium" , "Small" ))`

This took about 6.5s to compute for 100,000 rows, and about another 30s to copy and paste as values (another best practice for optimizing your slow Google Sheets – see no. 6 above).

Replacing this with Apps Script and running for the same data range took around 16s to paste in all 100,000 values, so less than half the time.

Here’s the code for this function:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
function columnClassifier() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var input = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues(); var output = []; for (var i = 0; i < input.length; i++) { if (input[i][0] > 500) { output.push(["Large"]) } else if (input[i][0] > 250) { output.push(["Medium"]) } else { output.push(["Small"]) }; } sheet.getRange(1,2,output.length,1).setValues(output); } |

Optimizing your Apps Script code is an entirely different and gigantic topic in its own right, so I won’t make any comments in this article. Note however, the current quotas and limitations applied to Apps Script code.

## 24. Use custom formulas sparingly

Custom formulas, also known as user defined functions, or UDFs, are custom functions written in the Apps Script script editor window.

Complex custom formulas will be very slow if you approach them in the traditional function way, and read/write single cells at a time.

For example, consider this custom formula to mark up a product price by 15%:

1 2 3 4 5 6 7 8 9 10 |
/** * Markup a price by 15% * * @param {number} input The value to markup * @return The input multiplied by 1.15. * @customfunction */ function MARKUP(input) { return input * 1.15; } |

which looks like this in use:

Copying and pasting this down 1,000 rows is SUPER slow. It took me about 10 seconds, but it’s also dependent on your wifi connection.

What you need to do with custom formulas, to improve their performance, is think more like array formulas. The slow part of these formulas is the reading from, and writing to, the Sheet, as this happens via the spreadsheet API, so it’s dependent on your wifi connection.

It’s best to minimize the number of read/write calls to your Sheet if you can.

Say you want to perform a calculation on 10,000 cells, then you’ll be better off reading all 10k cells into your function once, performing the calculation and then outputting back to your Sheet in one go, as a range.

For example, change the above formula to this more general one, which accepts a range input and outputs a similar sized range:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
/** * Markup a price by 15% * * @param {number} input The value or range to markup * @return The input multiplied by 1.15. * @customfunction */ function MARKUP_RANGE(input) { if (input.map) { return input.map(MARKUP_RANGE); } else { return input * 1.15; } } |

This will perform the same calculation on 1,000 cells in under a second, which is significantly faster. This is how it works in practice, accepting the whole range as the input now:

**Note:** Custom apps script formulas are recalculated only when their arguments change. Also, these arguments must be deterministic, i.e. not one of the volatile functions like NOW() or RAND(). If your custom function tries to return a value based on one of these, it will display `Loading...`

indefinitely.

Read more about custom function optimization on the Google documentation.

## 25. Other troubleshooting tips for slow Google Sheets

Sometimes it might not be an issue with Google Sheets that’s causing the slow response time. It’s worth trying 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.

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

> If possible, 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 wifi network to see if it is a network 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 a faster computer perhaps ¯\_(ツ)_/¯

## 26. Understand changes in the cloud take time to propagate

Remember that if you’re working in real-time with other collaborators, changes can take a few moments to synchronize.

Other users and any apps script files will be seeing a cached version of the Sheet until all the synchronization operations are completed, and this may affect how certain cells appear. For example, they may well have finished calculating in your Sheet but still be showing blank or #N/A in the view a collaborator sees.

Just something to keep in mind, and honestly, it’s a very minor point. In my experience it all happens so quickly that it does feel almost instantaneous, although again really large, slow Google Sheets will suffer from this more.

## 27. Know when it’s time to move to a database

There will come a time, and perhaps you’re already there, when you have to admit defeat and accept that Google Sheets won’t fulfill all of your data needs. For all of it’s strengths, of which there are many, that 5 million cell limit fills up pretty quickly with real-world data.

Well before any of your production Sheets approach the 5 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.

Look out for more resources on these topics later this year.

## Conclusion

That’s all folks!

I’ve tried to make this post as exhaustive and as useful as possible, but I’m absolutely sure there are more ideas out there.

I welcome any comments and thoughts on this topic. Think of this post as a living, breathing article to which more will hopefully be added in the future.

Thanks for reading and I hope the insights contained herein have been valuable!

*(This post contains some affiliate links, which means I earn a small commission on any signups for the products mentioned in this post. I only do this with products I personally use and believe in.)*

Great stuff Ben, thank you! Will refer to this article often, both for my own sheets, and in support of users on the Sheets Help Forum.

That “Toggle” idea btw is great. I’ve got a sheet on which, in addition to a the primary duties the sheet performs, there are many tabs that show reports that are only occasionally needed. Since these reports Query or Filter the entire database, I don’t want them running in the background when the user is not even looking. I put a giant ON (in green) / OFF (in red) switch cell at the top of the report, keeping them deactivated when not in use.

Lots to think about here, and I’m going to flip through the structure of my sheets looking for opportunities to apply these ideas. Great stuff Ben!

Best,

Lance

Thanks Lance! Great to hear of your use case for a toggle switch too. Let me know if you have anything to add 😉

Ben,

Brilliant! A very timely article as I attempt to speed up my unwieldy sales tracking spreadsheet. Lots of great ideas to implement!

Thank You!

Bernie

Thanks Bernie!

Thank you Ben, super useful and very helpful information. This post will certainly be part of my “G Sheets how-to reference manual”.

Keep up the great work , looking forward to seeing more advanced Google Script how-to´s and courses!

Cheers,

Thanks Frank!

Great Article Ben.

Thanx for such an exhaustive article. I was indeed looking for such article for some time.

Just a question: Somewhere I read that there is a limitation of number of formula cells to 40,000. I am already have a sheet having more than this so this limit is definitely not correct. But is there actally any limit on the number of formula cells or the only limit is overall number of cells limited to 2 million?

Further is there any script that removes unwanted rows and columns from the sheet?

Yes, that must be an old limit, but it doesn’t exist now. I’ve not heard of a formula cell limit anymore, but I’ll add it in here if I do. For deleting rows you could try this add-on: https://chrome.google.com/webstore/detail/remove-blank-rows-and-mor/gmidogdphchbiehdmbigdbcopkpjpnio?hl=en-US

You could quite easily write a small script to do it yourself too. You’d need to calculate the max rows and the last row with content and then delete those rows.

Hope that helps!

Ben

Dear Ben,

Thanx for the reply.

As you said in the article, I have also observed that normal formulas are speedier than array formulas but then formulas need to be copied every time new rows are added.

To reduce the work I have developed a small script which adds row and then copies the formulas formulas in the new rows. But for each sheet I have to make changes in the script to remove the values from non-formula cells depending on the requirement.

I have not been not able to develop any generic formula that can be used in different sheets.

Can you suggest some way to copy only from formula cells or copy all but then remove values from all non-formula cells.

The script used in one of the sheet is given below:

function AddRows_FOR_sheet() {

var sh = SpreadsheetApp.getActiveSheet();

var s = sh.getActiveRange();

var row_position = s.getRowIndex();

var row_num = Browser.inputBox(‘Adding row below current position’, ‘Enter row numbers to be added’, Browser.Buttons.OK_CANCEL);

var lCol = sh.getLastColumn();

sh.insertRowsAfter(row_position, row_num);

var range = sh.getRange(row_position, 1, 1, lCol);

range.copyTo(sh.getRange(row_position + 1, 1, row_num, lCol), {contentsOnly : false});

}

Perfect! Very relevant! Impressive!

Thanks !

Thanks Sergei

Thanks Ben!!

seriously awesome tips.

Now, i can easily speed up my google sheet and my productivity also.

thanks for posting for us

and save our valuable time in future

Thanks Nitish

Thank you Ben, that’s an awesome post, coming right on time, and tips which are not in many other places.

Thanks Cédric!

Ben,

What would you recommend for hardware to make the sheets calculate as fast as possible. An i7 K series processor? or xeon? have you tried different hardware?

Jason

Once again, trying to get a copy of Google Sheet Audit Tool. But the link download never sent to my email. Also for another similar link in this website since along time ago.

I always end up for giving up when face situation like this in this website.

If you really want to share, why you make it more difficult?

or do you actually just want to grab other people email only or do you just want to attract people to visit your site?

otherwise, you better not share it buddy rather than giving a fake link to readers

Hi Dave,

Sorry you never received the template email (maybe in your Spam folder?). I just tested the link and download email and it’s appears to be working fine. Have sent you the template email directly in any case!

Cheers,

Ben

Hi Ben

Thank you for your sheet link.

Sure, i’ve checked before in my mail folder including spam folder and there is no mail response from your submitted link in your website

Now i receive it after you sent it manually.

Hi Ben.

Thanks for this great article. It was very helpful!

I’m having an issue with one of the spreadsheets I use. It has a large amount of data, some scripts and IMPORTRANGES, but it’s still usable.

What’s the problem: when I edit (add or delete data) from one specific column in one specific sheet, the formula calculation bar starts to load and keep loading for a long time. It also “breaks” the IMPORTRANGE formulas from other spreadsheets that link to this “problematic” spreadsheet.

The input data from this column triggers only an VLOOKUP formula in the cell adjacent to it. The are other features in the spreadsheet that are way more complex ant this doesn’t occur when editing them.

Have you seen anything like it? Do you have any idea what that might be?

Cheers,

Italo.

Thanks! This is awesome! Is Google Sheets faster with a business G Suite account versus a free personal Google account?

I’ve followed the advice in several of the items here, and they did help considerably, but it can still take 10 seconds to recalculate when a user makes a change to a single value on one of the tabs in their spreadsheet. The formulas are very simple. There are no vlookups and no importranges or anything like that. But there are 32 tabs with anywhere from 50 to 300 similar rows. It’s almost as though Google Sheets is recalculating all 32 tabs when it isn’t necessary.

Is there a way of toggling autocalculate on and off? Perhaps there’s an add-in that will turn off autocalculate and let users recalculate on demand. I don’t know what to do, but waiting 10 seconds for each simple change is getting to be too much for the people using this sheet.

Any further suggestions at this point?

Hi Ben,

great article, however i don’t see much info about how to improve the importxml function.

Does-it exist a function which allow us to store the data and not check again and again, maybe juste once a week ?

Because i try to check regulary (one a week/month) if links are still on webpage. But it’s so slow on Spreadsheet and sometimes it just fail.

Excellent

Can you backup your claim that array formulas are slower than individual formulas? The strategy used to time the sheet and the actual sheet used, the actual formula used, the number of times, You’ve repeated these tests?

Thanks for your tips. But In Google Sheet Audit Toll after aproximately 5 minutes running script in my google sheets, show this message: Exceeded maximum execution time

My spreadsheet is extremely large, I believe there is not enough time for the script to run. Here are some suggestions to work around this kind of problem: https://stackoverflow.com/questions/7854573/exceeded-maximum-execution-time-in-google-apps-script

I posted this issue in github: https://github.com/benlcollins/apps_script/issues/1

Unfortunately I am not aware with codes to try to fix this problem 🙁

Thanks

I discovered that what was making my worksheet slow was a PivotTable where I processed my entire data table. I removed the PivotTable (which I use exporadically – about once every 2 months), when I need to use it I create a new one at the time of need as it was just to sort the data.

Good evening,

I’ve added your script to my Google docs library, given all the auth but it keeps saying “Please enter a valid Google Sheets URL”

I’ve just done a copy/paste on your cell B6 of the whole URL of my google sheet doc… Where I’m failing? 🙁

Hi Marco,

This sounds like a permissions issue. You’ll need to ensure the Audit Sheet account has read access to the file you want to check, otherwise you’ll get that error. You can go into your google doc sheet and share it with the same Google account where the audit file is.

Hope that helps!

Ben

Hello Ben,

I’ve multiple spreadsheet but it’s the only one giving me this error…

Is it possible to share that one with you so you can double check?

Sure, you can share with me at ben [at] benlcollins.com and I’ll take a look if I can find the time.

Done, than you so much. The file name is Financial Resume 6.1. I’ll write inside my email so we can have a direct contact.

Thank you indeed 🙂

Great article! Now I understand better a lot of points of the performance of Google sheets.

Thanks!!

How I can use the chart in various way in google sheets as i want to see a month selling of my products

Hi Ben,

thank your for the great article. Very informative!

I have a question. On the office we work a lot with a Google Sheet that has about 5 tabs. Usually 3 of us work on it simultaniously. And others, also on other locoations, have the page open.

Sometimes the page gets pretty slow.

Does working like that affects the speed of Google Sheets?

Would a reload help?

Regards, Felix

Excellent article. My problem is strange though. When I type a new formula and hit “Enter” the cell does not show the result until I perform a change in another cell (even though I can see the formula if I leave and come back to the cell). Once I have made a change in another cell….. entering a single character then hitting “Enter” for example, the formula’s result appears in the original cell. Happens on a variety of devices (All Win 10 running Internet Explorer OR Chrome and on any Ethernet/WiFi network). Any thoughts?

Thanks for the work on this article. Ben do you know how upgrading your computer will affect Google Sheets, meaning which has a better effect more memory or a faster CPU. Of course help but could you shed any light on the subject.

Thanks

In the case of a large index+match table, do you think it would also be faster to use a double filter? You could do:

=filter(filter(table,column=value),row=value)

Or is it possible to use getpivotdata on a table that isn’t actually a pivot table?

Hi Ben, awesome article!

I have some questions related to the loading-times workings of IMPORTRANGE.

A.

Lets say I have one “source” file with a large database.

If I call on that database in another file but filter the importrange:

filter(importrange( [url], “A:E”), A=”[value]”)

does it import everything first and then apply the filter locally, or does it apply the filter and then only import the filtered data? ie, is the importing process faster if it’s filtered?

B.

If I have 3 different sheets and import their data into one “source” sheet with from which my employees’ individual sheets import their filtered data.

When the individual employee sheets call on the one “combied” source sheet, does it take longer because in the backend it needs to gather from the other 3 source sheets? Or is that all instant in Google’s cloud backend?

Great article with a lot of helpful hints! Didn’t know about the ArrayFormula wrapped in the VLookup to return values in multiple cells instead of using multiple VLookup formulas. That is a great discovery! Thank you so much! I know you caution against using ArrayFormulas, but according to the worksheet audit page provided, I am using less than 5% of my available allotment. So any opportunity to program fewer formulas I will jump at!

Hi Ben, is the Google Sheet Size Audit Tool working? Because I made a copy and entered the URl of the spreadsheet I want to audit but nothing seems to happen…

Thanks

Hi Sebastian,

The tool is working fine. It took me some time to understand why it is not running from my end too. You need to open the Script Editor form Tool and then try to run the macro called “sheetAuditor” from menu “Run”. You will be prompted for authorization and once you allow the access to your files the data should load properly.

Delay from form submission to spreadsheet view of submission

Hello, great article, very helpful.

I am now experiencing a delay of 5-10 minutes from the point of form submission (the user hitting SUBMIT) to being viewable in the spreadsheet view. This happens sporadically, but when it happens it can takes 4 or 5 hours. I can not find the cause. I recently “cleaned” the form and the sheet, now are only 350 used rows with 17 columns.

Any suggestion please. Thank you very much