In this article, we would look at why you are facing issues with slow google sheets. We would also look at some tips you can use to make your google sheets run faster.
Google Sheets is a server-side rendered service. There can be many reasons which can cause your Google Sheets to slow down. We would look at some of these. But before that, we should know how to identify if you are facing slow Google Sheets.
Am I facing a slow Google Sheets?
These are a few points which will help you identify if you are facing slow Google Sheets.
- Your Google Sheets becomes unresponsive frequently: If your Google Sheets crashes a lot of times, or if it becomes unresponsive frequently then it is a sign of it being slow
- Functions take a lot of time to load: When you enter a function, Google Sheets usually take some time to load. It is very less usually, but if it is taking a lot of time to calculate, then you might be dealing with slow Google Sheets.
- Data does not show in the cell for some time: Suppose you enter a value in the cell, and it still shows empty for some time. This means
- your inputs are taking a lot of time to register, hence it is a sign of slow google sheets.
- There is a lag while working: Lag means that there is a delay in response compared to your inputs. Lag is a bad sign as it can be very frustrating to work with.
Reasons and how to make google sheets run faster
In this section, we would have a look at a few reasons which might be the cause behind slow Google Sheets and we would go through some tips that you can follow to rectify it.
Make sure that Google Sheets is behind the problem:
The first step should be to make sure that Google Sheets is behind the problem. First of all, check your Internet Speed. If it’s too low, then it might be the reason. If it doesn’t work then, you should try restarting your browser or even your PC.
If restarting didn’t work you can try removing the cache for the website. In Google Sheets, you can do this by doing the following steps:
- Click on three dots>More Tools> Clear Browsing data
- Select the Time Range you want to delete the cookies and cache from
- Click “Clear Data”.
If this does not work, it means you are facing slow Google Sheets. You can then try other steps listed in this article.
Delete Unused Cells
When you are using the same sheet for a long time, there is a high possibility that you might have accumulated a lot of unused blank cells. While these cells don’t add information but they take up a lot of the program’s memory. So it’s a good practice to delete these cells.
Suppose your Table takes space till Column E, so you can select all the columns to the right of E and right-click to select the “Delete Columns” Option.
You can do the same with the rows as well. Removing the unused columns decreases a lot of load from the program.
Convert formulas to Static Values
We use a lot of formulas while working with Google Sheets. These put a lot of calculative load on the program. Each formula is calculated when you boot up or when you change some data on which the formula was dependent. Hence, it is a good idea to minimize the use of formulas whenever possible.
You can remove the formulas and just use the values when you are sure that the value wouldn’t change again.
- Click on the formula you think won’t need calculation again.
- Cut the Cell Data (Ctrl + X )
- Right-click on the cell
- Click Paste Special>Values only
This would paste the value of the cell and would remove a lot of the burden of the program. Do this for all the formulas which don’t need recalculations or updating.
Avoid long-chain operations
Long-chain operations mean a set of operations that are dependent linearly on each other.
For example, in this figure, we have printed a Fibonacci Series. It is a series which contains the sum of the previous two numbers. This is a long chain operation since every entry is dependent on another value which in turn is dependent on another value.
Try to reference cells from the same sheet compared to other sheets
While IMPORTRANGE is a very useful function that lets you import data from other sheets, you should try to avoid it. IMPORTRANGE takes a lot of processing time since you have to import data from another location. Try to structure your file such that most of the references are on the same sheet. You might need a bit of restructuring but it would make sure that you don’t face slow Google Sheets.
Try to avoid using conditional formatting
Conditional formatting is a very frequently used feature in Google sheets. It, however, takes a lot of processing since it updates in real-time, and for huge data, it can lead to slow Google Sheets.
Try to use conditional formatting wisely. If possible, use it only on smaller tables and don’t color the entire table.
Use Filter to create smaller helper Tables
When you have a lot of data, let’s say 1000000 rows in your table and you only want to perform calculations on a subset of data. For example, you have a list of orders and you want to perform calculations on an order with a price above some specific amount. It is a good idea to use a Filter in these cases. Not only does it become easier to work with, but it also reduces the load on the server, hence eliminating the possibility of slow Google Sheets. You can check out an amazing article on using Filter in Google Sheets here.
Try to use the Google Apps script efficiently wherever possible
Let’s say you have a table like this.
You have to calculate the tax for each entry. Now, we can write a formula which basically calculates tax for a single cell and print it. We can then apply this formula to each and every cell and it would solve our problem.
But a more efficient way would be to calculate the tax for all the cells at once. The first formula would run a lot of times repeatedly, whereas the second one would run only once. Even if the number of calculated values is the same, calculating all the values at once would be faster.
Final things you can try
If nothing works you might need to change the structure for your Google Sheets file keeping everything in this article in mind. Try to break your sheet into smaller sheets and try to reference data within the same sheets.
If this doesn’t work as well, then you might be dealing with a huge amount of data. You might need to switch to some other database which fits your needs. You can try BigQuery or SQL but there don’t have the same interactive features as Google Sheets and you might need to connect it to some frontend framework. This would also need development skills.
In this article, we saw how to speed up google sheets. We tried a lot of tips and tricks you can incorporate into your Google Sheets file to make it run faster.
Loved this article on Slow Google Sheets? Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
How to create a Dynamic Named Range In Google Sheets: In this article, we would have a look at a Dynamic Named Range in Google Sheets. We will implement it in a step-by-step fashion using a simple example.
How To Calculate Standard Deviation In Google Sheets: We will learn how to calculate standard deviation in Google Sheets using the STDEV function. The STDEV formula is used to find the standard deviation of numbers or a range of values.
How to Calculate Running Balance in Google Sheets: Running balance is a total of transactions already made, or in other words, a cumulative total. In this article, we would have a look at how to implement the same.