This error occurs when you are trying to
- Import the data into google sheets (via DigiXport addon) OR
- Increase the no. of cells (by adding additional rows/column) OR
- Import the data from Excel or other data source
This is due to the google sheet has a limitation of certain no. of cells and columns.
As per google docs,
Limit is upto 10 million cells or 18,278 columns (column ZZZ) for spreadsheets that are created in or converted to Google Sheets
NOTE: The Blank cells also count towards the google sheet cell limit.
How to calculate the no. of cells in the Google sheet?
To calculate the no. of cells in the current sheet, you can simply enter the formula below on any blank cell. (It doesn’t matter if you have data in other cells)
=ROWS($A:$A)*COLUMNS($A:$ZZZ)
It will give you the total no. of cells in the current sheet as shown below
You can repeat the process for other sheets. When you add up all the cells you can check if it is near to the google sheet cell limit.
NOTE: The hidden sheet also count towards the cell limit.
How to resolve the Google sheet cell limit error?
There are various ways you can overcome this error. We’ll first discuss the most easiest ways to counter these.
1.) Backup the data & delete the data from the current sheet.
One way is to download the data in the excel format. That way, you have all the data backed up. To do that, You can
- Go to File.
- Go To Download > Microsoft excel.
Once downloaded you can delete the data from the current sheet. Keyboard shortcut: Select any cell > Press Ctrl + A + A and then press delete.
That way the error will not occur soon.
2.) Use a Different Spreadsheet.
If you are using the data in the current sheet for data studio or any other purpose, you can simply use a new spreadsheet.
If you have already created a reporting template in the current sheet you can simply make a copy of the current sheet. To do that you can
- Go to File.
- Click on Make a copy.
Once the sheet is copied, you can delete the old data retaining the template.
If you have queries which output data in the old sheets, you can simply transfer them, by changing the Spreadsheet Id column in the saved_queries sheet to the Id of the new Spreadsheet.
NOTE: The ID of spreadsheet can be found in the URL – > https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit
3.) Delete Blank Cells.
This is the method where you can still use the current sheet and reduce the amount of cells.
As stated before, the blank cells do count for the spreadsheet cell limit. To get the no. of blank cells, you can use the below formula
=COUNTBLANK(range)
where range can be of the format A1:F100
If you have large no. of data rows, you can delete the unused columns, that would free up a lot of unused cells.
For Eg: In the below image, we have 80000 rows and 4 unused columns. Deleting it would free up 80000*4 cells.
Other ways to delete the blank cells are
- You can delete the blank rows, if any, which have 0 data points.
- If data for some dates is 0, you can delete those rows.
- In saved queries sheet, you can even delete unused rows/columns as per the method mentioned above.
Avoid manually deleting blank cells, as it won’t help much. Check if you can bulk delete rows or columns.
3.) Delete Unused Sheet OR Copy the sheet data to a new spreadsheet
If you have any sheets which are used as test sheet or with no data, you can simply delete it.
If you have data which is important, then you can create a new spreadsheet and copy the data to that sheet or simply download it in excel format.
Also, make sure if there are any hidden sheets, that will count for the cell limit.
4.) Use ImportRange of Query to import the data from other sheet.
If you are performing an operation on a bulk data, then
- You can store the data from current sheet in a new spreadsheet.
- Delete the data from the current sheet
- When the new data is added to current sheet, simply use IMPORTRANGE or QUERY to import the old back up data from the other sheet and perform the operation.