How Many Cells Are In My Google Sheet?
What should you do when you get that Google error, “There was a problem. This action would increase the number of cells in the workbook above the limit of 2000000 cells.” We’ve got a handy new add-on that will help.
We’re big spreadsheet fans, as we’re sure you know. Tiller makes magic in Google Sheets with a daily automated bank feed. Connect your bank, credit card, loan and investment accounts, and then build out a super-powered finance dashboard to gather insights into your spending and financial world.
But how much financial data can one sheet hold?
Google Sheets has a 2 million cell limitation. All cells, empty or full, are calculated into the size of your sheet. Our power users sometimes bump into this wall after a few years of data. To help them keep tabs on their spreadsheet size we’ve released our second Google Sheets Add-on. (Our first one makes it easy to split transactions.)
Size My Sheet, which you can install here, counts the cells in your Google Sheet and provides a nice graphical representation of your spreadsheet’s remaining capacity. It’s a handy sidebar add-on to help you know your limits so you don’t hit that wall.
Giving the add-on permission
The Size My Sheet add-on requires two permissions to run. Google Sheets add-ons don’t provide a lot of detail about these permission requests, but the access is very straightforward. The add-on itself is asking permission to view your spreadsheets (so it can size them) and to run on its own (so it’s available in your menu bar when you need it).
The add-on does not store any data about the content of your spreadsheet, nor does it share any of your spreadsheet data with Tiller. Google tells us how many people install this add-on and the number of weekly users, and that’s the limit of the information we see about how or where it’s used. We’ve also made the source code available on GitHub.
What to do when you’ve reached the limit
When you reach the 2m cell limit Google will alert you when you try to add more rows or columns: “There was a problem. This action would increase the number of cells in the workbook above the limit of 2000000 cells.”
Now what? Well, you have a number of options to trim your spreadsheet.
- Delete rows or columns. If you delete 100 empty columns in a 1,000 row spreadsheet, that’s 100,000 cells you’ll be freeing up.
- Remove sheets. Check in to see if you’ve added any additional sheets (tabs) that you’re not using and delete them to save some space.
- Move some sheets into another spreadsheet. You can right click the tab at the bottom for any sheet, and then choose “Copy to” to make a copy into another sheet. Once that’s complete, you can delete the original sheet from your maxed out spreadsheet.
- Archive previous years’ data in a copy of your Google Sheet. If you’re using Tiller and your Transactions and Balance History sheets have many years of bank data, you could create individual sheets for each year. Open the File menu then make a Copy of your spreadsheet. (The copy will be saved in your Google Drive.) In the copy, which is no longer linked to Tiller, you can delete this year’s data from either or both of your Transactions sheet and Balance History sheet. In your original spreadsheet, which will continue to be updated by Tiller, you can delete all prior year data.
- Separate your data from your reports. If you want to use cell-intensive formulas like ARRAYFORMULA or QUERY, set these up in a separate Google Sheet and use IMPORTRANGE to share specific columns of bank data into these new Google Sheets.
- Create multiple spreadsheets instead of one. You can create multiple Tiller finance spreadsheets, so a great way to keep your sheets in check is to spread your data out among several sheets. Perhaps you have one spreadsheet for your investment data, another for your family finances, and a third for your business spending.
Let us know what you think! Reach out to email@example.com or message us via the chat window on our website if you have feedback.