How to Speed Up Google Sheets Lag

The frustration is real. We’ve all watched the loading bar inch towards its inevitable conclusion at a snail’s pace. If you’ve ever stressed about whether you’ll miss a deadline because your .xlsx spreadsheet file is going haywire, you’re not the only one.

Small businesses and digital marketers alike often use spreadsheets for a simple way to track content (text or numbers) that might be calculated, sorted, or listed. This includes tracking project management, budgeting, cash flow, invoicing, scheduling content calendars, and uploading contact data to your CASL compliant email lists. For many solopreneurs and freelancers, using a spreadsheet is a free way to keep information all in one place without a complex system, and it’s often their only lifeline to that critical data.

Sheets, Google’s spreadsheet tool in GSuite, is a cloud service that can be accessed from anywhere, from any kind of computer. It can save to .pdf, .xlsx, .csv and even .html, making it a handy tool to work with for a savvy digital professional on the go, but there’s one drawback for those big files on Google Sheets: lag. Is the tool working for you, or are you working for it?

Google Sheet Issues That Slow You Down

Your deadline is approaching, but your Google Sheet is protesting in one of the following ways:

1. That dreaded loading bar appears as Google Sheets does calculations in its own, sweet time.
2. Mouse clicks and keystrokes have no effect, or at least a lagging one, as your Google Sheets slow right down.
3. You’ve entered data into the cell, but where is it?
4. Your Google Sheet is simply unresponsive. At this point, recovery is slim-to-none. And if that error message pops up and stays put, you might be looking at starting from scratch.

So why the lag?

Google Sheet Lag Factors

There are several reasons why Google Sheets might be giving you grief, and they all come down to have you reached (or even exceeded) the limits? Individual cell-size is limited to 50,000 characters  (roughly 500 average-length sentences). Per workbook, you are allowed:

1. 5 million cells
2. 18,278 columns
3. 40,000 rows

How To Beat Google Sheets Lag

There are several steps you can take to speed up Google Sheets.

1. Delete all unused cells, blank rows or columns from in and around your datasets.
2. Reduce the overall file size by recording the static values and deleting the formulas used to achieve them. Two ways to do this are:
   1. Edit > Paste special > Paste values
   2. 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

It’s advantageous to keep one copy of the formula at the top of your data columns for record-keeping purposes and future use.

1. Using Closed Range References, where you specify the range boundaries (ex., A1:B1000) will stop Google Sheets from checking all the blanks cells as it would when they’re open (ex., A:B). If new data is anticipated, an open range allows for those inclusions, so set accordingly.
2. Using volatile functions—NOW(), TODAY(), RAND() and RANDBETWEEN()—uses up precious processing power, so proceed with caution or remove them altogether.
3. Create smaller “helper tables” through the Filter, Unique and Array_Constrain functions to calculate data subsets and reduce the overall table’s size.
4. Avoid long calculation chains
5. Formulas can access data within a sheet faster than across multiple sheets, so try not to do the latter!
6. Split large datasets into separate Sheets; an especially useful tactic if you have data from previous years that you need to keep, but not necessarily do anything with—put them in a new Sheet!
7. Keep Conditional Formatting for small data tables, presentation tables, and dashboard reports. This cell-by-cell process can cause Sheets to slow down if used on large datasets!

….still lagging?

Try These Google Sheets Strategies To Improve Speed

1. Close down Google Sheets. Reopen Google Sheets. No, really, sometimes it works.
2. Disable and then re-enable Google Sheets offline access, too.
3. Refresh your browser and clear your cache and cookies while you’re at it.
4. Open Google Sheets in another browser or operating system.
5. Ensure the browser and operating system you’re using are running their latest version.
6. Disable unused or out-of-date browser plugins or extensions.
7. Try replicating the issue on another computer and/or wifi network to trouble-shoot whether it’s a hardware or connectivity issue.
8. Give it a minute. Sometimes, changes take time to show up in the Cloud.

Still not working? Perhaps it’s time to move your data to a database or CRM. In the meantime, we hope you’ve found this information valuable, and good luck!

If you would like to set up GSuite for your business, come talk to us and we’ll make sure it’s connected correctly. Read more about our GSuite services here and contact us for a custom quote. Simple account setups start at only $100!