Loading
Loading
  • Home

  • Productivity

  • App tips

App tips

4 min read

Google Sheets now has a limit of 10 million cells. Here's what that means for you.

They've added 5 million more ways to ruin your spreadsheet.

By Tyler Robertson · March 9, 2022
A hero image for Google Sheets app tips with the Google Sheets logo on a green background

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.

An error popping up when adding rows to Google Sheets

Even though the technical limit 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.

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:

  1. "Wow."

  2. "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.

Aw snap browser message

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:

Saving your changes is taking longer than usual. Editing is temporarily disabled.

"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.

Jurassic Park GIF: Your scientists were so preoccupied with whether or not they could, they never stopped to think if they should.

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 QUERY() or 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.

Related reading:

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.

tags
mentioned apps

Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'