SEO and General Business tutorials and tips.
This post was sourced from by MOZ.
Last week, Google unceremoniously rolled out an update that sang a siren song for folks like me (real sheetheads, if you will): they increased the maximum number of cells in a Google Sheets spreadsheet from five million to 10 million.
Immediately after hearing the news, I leaped into a new sheet to see what having twice the possibilities could get me. Here’s what I learned.
1. You can’t jump straight to 10 million
To test drive the new limit, I removed all but one cell from my spreadsheet and went to use the built-in Add button to create 9,999,999 new rows. No dice.
Even though the technicallimit has been raised, Google still has guardrails in place to keep us from getting too wild too quickly. Because it turns out, 10 million is a lot of cells.
tried to record a video of myself scrolling to the bottom with just a trackpad, but i have things to do this week pic.twitter.com/OAEKyusR07
— Tyler Robertson (@aTylerRobertson) March 7, 2022
You can still add up to five million rows at once, however, and if your sheet has more than two columns, you may need to do a bit of math to see how many rows you can add before reaching the barrier (10,000,000 divided by number of columns, rounded down).
2. A sheet with 10 million cells is really slow
Once you’ve hit 10 million cells, you’ll probably say two things:
“Why isn’t it doing anything?”
Because even if they’re blank, Google Sheets still has to think about all of those cells, and in turn, your browser still has to do work to render them. Ten million of anything is a lot to process, so even things like scrolling will take longer than you might be used to. Just pretend you’re on dial-up.
If you want to actually do anything to those cells, that’s another matter entirely. Adding a single
SEQUENCE() function to count the cells resulted in a long delay, followed by my browser crashing.
It did eventually load, in case you want to check my math.
But then let’s say that you get your 10 million cells set up, filled with your amazing data, and you want to change the formatting? No, you don’t.
When trying to center-align the text in the sheet above, about three minutes passed before Google finally gave me this error:
“That’s fair,” I thought, “I’ll come back to it later.” But 20 minutes later, the error was still there. “Temporarily” stretched longer and longer until I opted to delete the sheet, start over, center the text first, then add my data. The more you can do before maxing out the cells, the better.
3. Your Zaps will not be happy about 10 million cells
Just because Google Sheets can have 10 million cells, does not mean your sheet should have 10 million cells.
When Zapier sends a request to Google Sheets, whether it’s to create a new row, or see if there are any changes that need to be triggered on, it waits around 30 seconds for a response. In order to send that response, Google needs to process all cells in the sheet, determine their output, wrap it up in a nice little package, and send it back. Even though it’s a big Google machine, that can still take some time on large sheets because it processes those cells one at a time, going left-to-right and top-to-bottom, like reading a book (here’s more on how I found that out).
If Google Sheets can’t form a response within that 30 seconds, Zapier receives a “timeout” error, meaning it may not receive new rows or make updates as expected.
With even my relatively simple spreadsheet (which uses one formula in cell A2 to fill in the row numbers), timeouts start to happen at around 1,200,000 cells, almost one-tenth of the new limit. If your sheet has lots of formulas, especially with slower functions like
IMPORTRANGE(), that number is even lower.
So the moral of this story is to resist the temptation of 10 million cells. Your Zaps will thank you for it. (If you really do need that many cells, keep reading for some solutions!)
4. 10 million cells lets you do…a lot
If you can get past the slowness, this new limit does open up some fun and interesting possibilities. In my initial experiments, I was able to come up with a couple:
But you could also:
Keep a record of the first and last name of every person in Cape Town, South Africa
Take a census of all of the swans in England (a real thing) for the next ~75 – 150 years
Making the cells as tall and wide as possible (2000 pixels), have a spreadsheet that could fill around 20 billion 4K TVs
If it’s not obvious yet, the actually practical uses for 10 million cells are few and far between. I’m not sure who Google actually changed the limit for, but whoever they are, they should follow the next step.
What you should actually do if you need 10 million cells
I’m going to say this out of love: if this is you, stop using Google Sheets.
If your spreadsheet has 10 million cells, you no longer need a spreadsheet. You need a database, and those are two different things.
Databases are designed to let you selectively choose which data is processed, letting them break large sets of data into more manageable chunks. Thanks to that, they can automate that data through Zapier without running into the timeout issues that Google Sheets has. And they’re not all scary, either—apps like Airtable are designed to put a friendly face on your data, making it easy to import your data (from Google Sheets, for example) and get started. We even have a guide all about it.
And for the rest of you sheetheads with 10 million cells, who refuse to leave the familiar fields of Google Sheets: godspeed.
Please let me have your feedback below in the comments section.
Let us know what topics we should cover for you in future.