How do you fix Google Sheets when it is running slow?
The five ways to speed up working in Google Sheets when it is running slow are: filter your data when working on specific rows, convert formulas to values where possible, sort your data if you are working with functions like
VLOOKUP
, create a unique column ID to help find data quicker, and look at alternative functions instead of
VLOOKUP
.
Recently I was working with a Google Sheet which had over 60,000 rows. I needed to use the
VLOOKUP
function on
each row
as well as perform other calculations on selected columns and rows.
Throughout the modifications and calculations, Google Sheets worked very slowly. Whenever I deleted or changed a cell that referenced a formula, it would process the change and update the spreadsheet.
Due to the slow and painstaking process, I found a few ways of making the calculation time faster.
1. Filter Your Data
Where possible only work on the rows you need to work on. If you can filter your data do so and do it in steps or stages rather than one big batch.
I found when I filtered my rows I wasn’t waiting anywhere near as long for my formulas to process and calculate.
This solution works well when you need to retain the formulas in your cells. However, if you don’t look at using the next solution.
2. Convert Formulas To Values
If the use of a formula is to provide a specific result needed for further analysis and the inputs needed in the formula will not be changing, then copy the column’s contents and paste them as values.
This process, once you’ve created your formula and copied it through to the appropriate rows, is a simple matter of selecting the range containing the formula, hitting
Ctrl/Cmd + C
and then pressing
Ctrl/Cmd + Shift + V
on the keyboard.
Working with static values rather than calculated values can help speed up processing time on your spreadsheet.
3. Sort Your Data
If you are using the
VLOOKUP
function to obtain values from other sheets, then try to sort the data that
VLOOKUP
is searching through to find the match. Change the
VLOOKUP
from
VLOOKUP(search_item, search_range, return_column, FALSE)
to
VLOOKUP(search_item, search_range, return_column, TRUE)
.
Sorting your data will help to speed up the time it will take for the spreadsheet to hunt for your search_item in the formula.
4. Create Unique ID Column
See if you can create unique id’s of your data if, even after sorting, there are still lots of rows containing the same common sorted value. For example, the data I recently played with contained basic transactional information such as date, detail, account, and amount.
To improve the ability of Google Sheets to search specifically for a value, I created a column that contained a string representation of the date followed by a string representation of the amount. I then created a new ID column and sorted it by this column.
If the current order of your data is important, create an iterated column that has the correct order of your data first , before then creating your unique ID column. Once you’ve finished processing using the unique ID column, you can remove it and then sort your data by the iterated column to return the original data set back to its proper sorted rows.
Check out how to create a unique ID with dates in Google Sheets here .
5. Use Other Formulas
Instead of using the
VLOOKUP
function, see if other alternatives are faster such as
INDEX
and
MATCH
or
SUMIFS
, work faster.
You can read more about the
alternatives to
VLOOKUP
here
.
Summary
There are several ways to speed up the processing time of Google Sheets, especially when working with large data sets. Solutions you should try include filtering data so that the number of rows you are working on is smaller, converting formulas to static values so that the processing time spent recalculating cells is less, sorting data to help function such as
VLOOKUP
find data quicker, creating unique ID’s for each row to make the lookup process even faster, and using other alternative formulas besides
VLOOKUP
. Any of these solutions can be helpful in speeding up the calculation process.
