Slow Google Sheets?
Hello and welcome to another article from TeqTog, where I will talk about how to speed up Google Sheets in this tutorial article.
Google Sheets is an effective program which lets you work with numbers in both your professional and personal lives. Be it having to sort through huge volume of data, keeping track of performances, analyzing trends with variations in numbers, calculations, budgeting, making lists or presenting graphical images of your numerical results; Google Sheets has your back all the time for any of these.
However, on many unfortunate occasions, you will encounter situations where your Google Sheets will just refuse to respond to your commands and will start lagging in its performance.
More often than not, when Google Sheets is slow to respond, it either takes a few minutes to start performing as usual or it can just end up crashing. There may be several reasons for this to happen, but nevertheless, we can all agree that when Google Sheets decides to slow down – life becomes hard.
You may miss out on important deadlines, your work may start piling up and all of this can cause you to become frustrated which can eventually impact your work performance.
So do make sure to go through this article on how you can speed up Google Sheets in 10 simple ways to the end.
Usual Signs that your Google Sheets is Lagging
Most commonly, you will face these annoying things when your Google Sheets has become slow. That is when you know, that you need to do something to bring back the pace in your Google Sheets.
- The usual sign would be when you have typed in data into a cell in Google Sheets; however, what you typed in is not appearing.
- Commands by clicking on your mouse or pressing the keys will have no response. Or, the response time will be delayed.
- When Google Sheets is carrying out calculations, you will see that a loading bar appears.
- All of Google Sheets is totally unresponsive to everything. You should be aware that this is usually when there is a low chance that Google Sheets can recover your work till now. Moreover, an error message can appear and if that is displayed for a long time; it might mean that you have to begin your entire work from square one.
We have all been there. That is why, I will try to give you a few helpful tips on how to speed up Google Sheets in 10 possible ways.
How to Speed up Google Sheets in 10 Ways
All of these tricks might work for you or just a few of them. Either way, it is always helpful to learn about all of them, because you never know, which might save your work next time. So, keep reading!
Remove Unused Cells in Sheets
While you were working on your spread sheets, you may have needed to relocate a lot of your data throughout the spread sheet.
This is may have led to a lot unused cells being stored in our Google Sheets. It is important to know that, these cells (although empty) occupy space in your spread sheet’s memory.
Therefore, it is effective to clear unused cell and you can accomplish this by:
- Select the entire empty column or rows
- Open the menu by right clicking
- You should see and select “Delete Column” from the drop down menu
As you can see, I have first selected a column (B1:B18). Then from the right click menu; I can choose the “Delete Rows 1 – 18”.
Clearing Irrelevant Columns or Rows
Just like how empty cells can cause Google Sheets to lag; so can columns and rows which holds unnecessary information. You need to decide which data is relevant for our work and which data can be removed from your spread sheet.
By doing so, you should get rid of those column or rows which contain such irrelevant data.
For instance, suppose I require a list of my clients and their legal addresses only. But my spreadsheet also has information about their email addresses and date of incorporation as well.
If I consider this data as extra, I can just remove the entire column for “emails”.
- You need to select the column or columns you wish to discards
- Then open the right click menu
- You will find a “Clear Column” option. Select on that.
Do Not Stack Up Formulas
If you need to work on a figure, make sure that it is in a static form. What this means is, suppose you have a figure in a cell. This figure is the result of a calculation.
Now, if you need to work on this figure a second time; you should convert the figure into a fixed value form, instead of keeping the first calculation.
Now if you perform a new calculation on this figure for a second time, Google Sheet will not have to carry out calculations twice.
When new calculations are piled up on top of old calculations, Google Sheets runs all of the calculations given in that cell – this sometimes causes it to slow down.
Therefore, one easy way to avoid this is by converting your calculations in fixed values.
- Select the concerning cell
- Open the right click menu
- Find the fourth option: “Paste Special”
- Select “Values Only” from the sub-choices, to replace your calculation with a value only.
Restart or Reset Everything
It may not come as a surprise, but, you can always restart your program for satisfying results.
When your Google Sheets starts acting up, either just close your spread sheet program or simply give your computer a reboot or restart as well.
You can simply clear your browser history and cashes as well to speed up your slow google sheet.
To clear browsing data you need to go to history page from “Customize and Control” option in google chrome browser by clicking three dot menu option in the right-top corner of the browser, like the image below:
Now, you need to click on “Clear browsing data” option in the (left-bottom option) browsing history page, like picture below:
Now, you have the option to clear the browsing data. Select the time frame you want to clear, like maybe last hour or 24 hours or even all time data.
You can open this “Clear browsing data” popup with shortcut Ctrl+Shift+Delete key from keyword in windrows.
There is different options “Basic” and “Advanced”. You can check or uncheck different options there to clear.
I recommend to check at least these 4 options ( Download history, Browsing history, Cookies and other site data, Cached images and files ) to clear.
Apply Conditional Formatting Specifically
Conditional formatting is an effective way to make your data representation well comprehensible; however, utilization of conditional formatting may contribute to work overload for your program.
Therefore, it is best to apply this tool very specifically and avoid using it unnecessarily on all cells.
- Firstly, select the column that has conditional formatting
- Find the “Format” menu from the top of your spread sheet and click on it
- Click on “Conditional Formatting” from the drop down menu
- You will be displayed a sub-menu and from there, click on the garbage icon to remove rules of conditional formatting.
Bringing References Closer
On common occasions, you might to have references for information in another sheet or another workbook entirely. Connecting your program with other workbooks and sheets by using references may adversely impact the performance of your Google Sheets.
Moreover, this may even not always be the fault of Google Sheets itself; because, it may take some time for information to be transferred from one workbook to another workbook.
You can prevent this problem from occurring by the following ways:
- Trying to avoid using references to other programs where possible
- You can also copy and paste relevant information from the other workbooks in your main workbook
- In addition, you may try bringing all the relevant information from the other workbooks into your main workbook by opening new sheets in your main one.
- Also, replace all the calculations in cells with fixed values in the other workbooks; as advised earlier in this article.
Stop Using Volatile Functions
Google Sheets feature a lot of such functions, which runs recalculations throughout your entire spread sheet program once your apply them. They are very powerful functions and they can force your Google Sheets to suffer from processing overload.
Such functions are known as volatile functions and it is always best to avoid using if possible.
For your better knowledge, these are the basic volatile functions which forces Google Sheets to perform an overall recalculation:
I will tell you to avoid the application of the functions mentioned above for your spread sheet’s smoother performance.
Dividing your Workbook
It may also come to this, when you need to create multiple workbooks to carry out your work instead of just one.
Performing heavy work on just one spread sheet may cause it to have a performance break down sometimes; which is why you need to open two or three more workbooks.
The downside of this solution is that, it may cause all you workbooks to perform faster; however, you will need to repeat some of your work amongst all your workbooks and later compile them.
Application of the “=IF()” Function
One of the most effective ways you can gear up your spread sheet’s performance is by applying the =IF function instead of using formulas which will not source back necessary data.
This method is extremely helpful if you are working with VLOOKUP or information from other workbooks.
The =IF function will have this structure:
“=IF(*conditional statement*,*what if the condition is true*,*what if the condition is false*)”.
- Type in the following formula: “=IF(*cell no.*,*your condition*,*cell no.)”
- Press enter
In my example above, I wanted to identify, which price quotations for a tender bid were above $1,000.
Therefore, as you can view in the formula bar on the top, I typed in =IF, gave the cell reference (which was B19), then gave an interval, then put down my condition (which was what if the value is greater than 1,000) and gave a comma again, gave the cell reference again and, pressed enter. I then dragged down till C26.
Now all of the values which are above $1,000 are marked in the next column (C19:C26)
This saves Google Sheets from a lot of processing and therefore, saves your time as well.
Using Specific Formula and Ranges
Another effective way to speed up Google Sheets is by setting a specific range to your cells when you are running a formula.
For instance, rather than selecting an entire column (column A) for applying your formula, just choose a specific range of cells in that column that actually has data (suppose, A1:A500).
Because a lot of time, Google Sheets has to run calculations across all of the cells included in a column; although some cells may just be empty.
Setting a specific cell range to run formula may be time consuming; however, it is a guaranteed way to keep your Google Sheets running at convenient pace.
There you have it: how to speed up your slow Google Sheets and make it faster in 10 ways. Hope my article will be helpful for you next time your spread sheets refuses to respond.
Best of luck!
Take a look on Teqtog others Google Sheets tips articles on: