If you are encountering performance issues in Google sheets, there’s plenty you can do to speed up a slow Google Sheets.
A slow Google Sheets causes simple calculations that normally take a few seconds to take dozens of seconds to minutes. In a worst-case scenario, a slow sheet will freeze or crash.
There could be many reasons that lead to slow Google Sheets workbooks, and in this tutorial, we will cover 10 easy ways to speed up slow-performing Google Sheets.
So let’s get started!
Table of Contents
Restart Everything
When you’re experiencing slow performance in Google Sheets, the first thing you should do is make sure Google Sheets is responsible for the problem.
On the lowest-level, completely close out of your web browser (or the Google Sheets app) and restart the program.
If you’re still experiencing the issue, the next thing to try is rebooting or restarting the computer or other device you’re using.
The simple act of restarting clears countless potential slow-performance causes without needing to do any investigation into the issue.
If restarting doesn’t work, you can try clearing the browser cache. In Google Chrome, you can do this by:
- Opening the menu
- Selecting “More Tools”
- Choosing “Clear Browsing Data”
- Selecting a “Time range”
- Clicking/tapping “Clear data”
I have used Chrome in the screenshots. Other popular browsers also use similar steps to clear the cache.
You can also try disabling browser add-ons/extensions.
You can do this in most browsers simply by opening the Sheet in an “incognito window.” If the Sheet runs correctly in “incognito” mode, you can disable add-ons until you find the guilty party.
Disable add-ons in Chrome by:
- Opening the menu
- Selecting “More Tools”
- Choosing “Extensions.”
- You can turn off individual add-ons in the “Extensions” menu by tapping or clicking the slider.
If you are still experiencing performance issues in Google Sheets, it is now safe to presume the problem is within Sheets itself.
Delete Unused Cells
If you’ve moved a lot of data around in your spreadsheet, you’ve likely accumulated a lot of blank cells.
While these cells don’t add any information for a person viewing the sheet, they take up space in the program’s memory.
You can clear cells through a variety of means, but one of the easiest and fastest is:
- Select all the unused columns (or rows) at the edge of the Sheet
- Opening the right-click menu
- Choosing “Delete columns” (yellow arrow)
The following example demonstrates selecting columns C-Z and choosing the “delete” option from the right-click menu.
This method can also delete rows.
Google Sheets supports five million cells per workbook, 18,278 columns per sheet, and 50 thousand characters per cell. However, performance issues pop-up long before reaching these capacities.
Remove Unnecessary Rows/Columns
Just because a cell has information in it, that doesn’t mean the cell is useful.
The slow Sheet may have more data than you need, especially if you copied it over from another sheet. You can speed up a slow sheet by removing unnecessary rows and columns.
However, you should always keep a copy of the original before removing unnecessary content.
For example, if you’re trying to build a histogram showing how many times people submitted unique email addresses on a form, you only need the email addresses. Any other information will just slow down Sheets.
In our example question form, let’s say we have received thousands of emails from customers and want to find out how many customers asked us questions multiple times.
However, Google Sheets keeps crashing when we try to build a histogram.
The Sheet is also holding information about the Name, Phone Number, and Inquiry, which are irrelevant to our question. So we can speed up Sheets by removing any data that’s not needed.
In the following example, I’ve selected the columns for “Name,” “Phone Number,” and “Inquiry” (yellow arrows). Next, I opened the right-click menu and selected “Clear columns” (red arrow). This will clear the unnecessary information from the sheet.
Avoid Calculation Chaining by Converting Formula Cells to Static Values
This isn’t as easy as figuring out compound interest in Google Sheets. The cumulative workload can add up quickly it needs to perform calculations on top of calculations.
One easy way to eliminate work overhead is to convert the formulas into static values.
This prevents Sheets from needing to run the calculation to show the values in the cells and makes it less work for Sheets to pull information from those cells into other formulas.
To replace formula cells with static values:
- Select and copy all the cells in question
- Open the right-click menu
- Select “Paste special”
- Choose “Paste values only”
This will replace the selected cells with the answer to the formula so Sheets won’t need to calculate the answer when assigning a value to each cell. When doing this on a large scale it can significantly reduce work overhead.
Be Specific with Ranges and Formulas
Specifically defining ranges in cells can dramatically speed up Google Sheets.
While it’s convenient to select an entire column range when running a formula, this causes Sheets to run calculations across all cells in the column, even if they’re blank.
Being specific creates a little more work for the person, but keeps Sheets running smoothly.
For example, if you have data in one thousand rows under the A column, it’s more efficient to set your range to “A1:A1000” in the formula than it is to use the entire column with “A:A”.
Being specific with ranges can make huge differences in calculation time with reference queries to other sheets and VLOOKUP commands.
Additionally, you can check your formulas to see if there’s a mathematically more efficient way to make the same calculation.
If there’s a built-in formula, always use that. For example, =AVERAGE(A1:A10) is faster than =SUM((A1:A10)/10).
Only Use Conditional Formatting when Necessary
Conditional formatting is a great tool for human readability in Google Sheets; however, it’s a major contributor to processing overhead.
Color-coding things like if a product’s revenue was a profit or a loss are great for human-readability. However, it’s not so great concerning performance when you’re working with massive amounts of data.
If you are removing conditional formatting, you should make a copy of the original Sheet and work in the new version. Restoring conditional formatting can be tedious.
You can remove conditional formatting by:
- Selecting the row or column with the formatting rule (blue arrow)
- Opening the “Format” menu (red arrow)
- Choosing “Conditional formatting” (yellow arrow)
- From here you can remove rules by clicking or tapping the garbage can icon next to the rule in the “Conditional Formatting” sub-window.
Avoid Functions that Require Recalculating the Entire Sheet on any Change
A handful of useful functions in Google Sheets force recalculation across the entire sheet every time there’s a change to the sheet (these are called volatile functions).
These powerful functions can quickly add massive amounts of work overhead to a Google Sheets file. Avoid them when working with large amounts of data.
The main four functions in Google Sheets that can cause recalculation problems are:
- NOW
- TODAY
- RAND
- RANDBETWEEN
If you’re experiencing a slow workbook and you’re using one or more of these functions, they’re most likely responsible for the slowdown.
Removing these functions will speed up Google Sheets.
Additionally, you should avoid using conditional formatting in conjunction with any of these four functions. Using the two together can quickly overload the program.
The Closer the Reference the Better
Sometimes it’s inevitable that you’ll need to reference information on another sheet within a workbook or a completely different workbook. These references, especially ones in other workbooks, can cause Google Sheets to run very slow.
In the case of referencing different workbooks, the performance issue may not have anything to do with Google Sheets’s capabilities. The slow performance can stem from the time it takes to exchange information between sheets.
Generally speaking, the closer the reference, the faster the performance. You can alleviate problems by eliminating calls to other workbooks.
If possible, copy/paste the information you’re referencing in the second workbook into a new sheet/page in the first workbook.
Additionally, you can go through your workbook and replace cell information with static values as described in section 4.
Import should be a last resort. However, there are many cases where you’re working with changing/updating data that require importing.
Use “=IF()” to avoid unnecessary formula calculations
You can dramatically speed up a slow Google Sheets workbook by using conditional statements to avoid running formulas that won’t bring back useful data.
This can be very useful for VLOOKUP and queries from other workbooks.
To do this, we need to wrap the “=if()” function around the formula in the cell.
The format for “if” looks like
“=if(*conditional statement*, *what to do if the condition is true*, *what to do if the condition is false*)”.
While you would use “=if()” to avoid complex formulas, we’ll use a simple situation as an example.
In this example, we want to determine the average score of the people who passed the test, or scored above “70.”
So we apply the formula “=if(A2>70,A2,””)” to cell A2, and drag it down to A15.
This returns the scores of “75,” “89,” “89,” and “95” in column B. For purposes of the example, we calculate =AVERAGE for column B’s contents to get our answer.
In the case of the example, we’re not saving much time at all since we’re returning a value from the same file.
However, this substantially cuts down on overhead if we were to pull the passing students’ semester grades from a different workbook.
Using this method can significantly reduce how many calculations Google Sheets needs to run to populate data.
Break up Massive Sheets
When all else fails, it might be time to split-up a workbook. As powerful as Google Sheets is, the application has its limits.
To get Google Sheets to run at an acceptable speed, you may be looking at breaking up your data across two or more workbooks.
Unfortunately, this fix means you’ll have to repeat the same work across each workbook and combine the results.
Additionally, this means some analysis will be much harder. When breaking up workbooks, it’s a best practice to leave the original version intact and work with a copy.
Following these tips can turn many slow Google Sheets projects back into smooth-running spreadsheets. The more efficiently you implement information and calculations in Google Sheets, the faster it performs.
Being aware of hang-ups that can cause Google Sheets to run slow can help you better curate your workbooks to avoid performance problems in the first place.
So these are 10 quick and easy methods that you can use to speed up slow Google Sheets. In most cases, you should see a considerable difference in speed when using one or more of these methods.
Hope you found this article useful!
Other Google Sheets tutorials you may like:
