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. These suggestions can also help improve the performance of your sheet if you find it is getting slow.
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.
How do I know if I'm close to the limit?
Google Sheets has a maximum of 5 million cells. However, most users don’t hit this size limit. Excel workbooks do not have a data limit.
If you do reach the Google Sheets limit, you can create a new sheet and cut/paste some of your older Transactions or Balance History rows into this new sheet to shrink the size of your active Tiller Sheet.
Using the Size My Sheet Google Sheets Add-on to check sheet size
If you're concerned that your Google Sheet might fill up because you've been using Tiller for a while, or you're using advanced queries, reporting, and functions you can install our Size My Sheet Google Sheets Add-on to monitor your sheet's size.
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.
