Did you know that Google Sheets has now increased its 5 million cell limit to 10 million cells?
Google Sheets is one of the most popular spreadsheet tools, thanks to its intelligent features and collaborative abilities. It also provides almost five times the number of cells in a single spreadsheet than Excel. Google Sheets gives users a 10 million cell limit, which is likely to be a limit you may never reach. However, if you end up reaching it, you’ll need to know how exactly to solve this problem.
When you come close to the cell limit, you often experience errors, which can end up affecting the productivity and efficiency of your work. Luckily, there are a few solutions to help us avoid reaching the cell limit and encountering these difficulties. Let’s explore precisely how reaching the Google Sheets cell limit can affect you and how to solve this issue.
What are the limitations of Google Sheets?
Before going into more detail on cell limitations, below is a list of 8 key limitations to consider when working with Google Sheets:
- Cell limit: 10 million cells or 18,278 columns (column ZZZ) for spreadsheets that are created in or converted to Google Sheets. This same limit applies for spreadsheets imported from Microsoft Excel. The limits are the same for Excel and CSV imports.
- Row limit: 40,000 new rows at a time.
- Tab limit: 200 sheets per workbook.
- Finance formulas: 1,000 GoogleFinance formulas.
- ImportRange formulas: 50 cross-workbook reference formulas.
- ImportData, ImportHtml, ImportFeed, or ImportXml formulas: 50 functions for external data.
- String limit: 50,000 characters. When you convert a document from Excel to Google Sheets, any cell with more than 50,000 characters will be removed.
What happens when you are close to the Google Sheets cell limit?
Usually, when you are near the 10million cell limit in Google Sheets, you will receive an error message like this:
However, there are other consequences of getting close to the cell limit, which can end up affecting the way you work.
Here are just a few of the things you may encounter:
- Long load time: With so much data, your Google Sheet files can become quite heavy. This means that your computer needs to work harder in order to open everything, leading to a longer load time.
- Failing add-ons: Some add-ons may not be able to open as your computer will be prioritizing opening and updating your actual datasets.
- Crashing: In some cases, the processing power required to keep your files open and running can be too much. As a result, Google Sheets may get too overwhelmed and crash unexpectedly.
Rather than letting ourselves get to this stage, there are a few ways that you can effectively count the number of cells in your spreadsheet so you know well in advance when you will reach the cell limit.
How do I count the number of cells?
The cell limit in Google Sheets includes both blank cells and cells including data. Unfortunately, there is currently no app or add-on available that can automatically count the number of cells in your Google Sheets. There is, however, a function you can use to get the total number of cells containing data in your spreadsheet file.
The COUNTA function allows you to count the number of cells in your file that contain data. By applying this formula to your spreadsheet, you can keep track of how many cells you’re using in your file.
Let’s take a look at how to use the COUNTA formula in your own Google Sheets file.
Step 1: Open your Google Sheets file
Open your Google Sheets file.
Here, I have an extensive database full of all the employees within my company.
Step 2: Insert the COUNTA formula
In your spreadsheet, click on a blank cell and add the COUNTA formula using the following format:
=COUNTA(first_cell:last_cell)
In this example, I will be selecting a range from A1 to G637. So my formula will look like this:
=COUNTA(A1:G637)
Step 3: Get your blank cell count
Once your formula is ready, click enter.
The number returned is the number of non-blank cells.
Limitations of the COUNTA function
Although the COUNTA formula is easy to use and quick to apply, it does have some limitations. Let’s take a look at them:
- Doesn’t include blank cells: The COUNTA will only count the cells that contain data within them. As mentioned before, the cell limit in Google Sheets also includes blank cells, so the formula isn’t as effective as we need it to be.
- Manual range input: The COUNTA formula cannot automatically be applied to your entire spreadsheet. Instead, you have to manually input the cell range, which may become tedious and complex if you’re dealing with thousands of rows or columns of data.
- Tab limit: The COUNTA formula can only be applied to cells within the tab you’re in. You cannot determine the number of cells in your entire spreadsheet with one formula. You must apply the formula to each separate tab within your Google Sheet.
Although there are some limitations with the COUNTA formula, it does give you a good idea of whether you may be close to the cell limit.
With this accurate insight, you can then carry out various solutions to reduce your heavy spreadsheet and avoid crashes, data loss, and other errors.
How do I reduce the number of cells inside a Google Sheet?
As Google Sheets does count blank cells as part of the cell limit, the most simple way to avoid the limit is to delete these empty cells. You can do this by using the Filter feature in Google Sheets.
Highlight the entire dataset in your spreadsheet and head to Data > Create a filter.
In a column, click on the filter icon. Under Filter by values, click Clear, then select (Blanks). Press OK.
Google Sheets should now only show your empty cells. Highlight the empty cells all at once by holding down the shift key and clicking on the last row in your spreadsheet.
Right-click and select Delete selected rows.
Click on the filter icon again. Click Select all to bring back the cells with your data.
Once finished, click OK.
Although this method is effective at removing empty cells, it can be pretty time-consuming. What’s more, this method only works when you have entire rows that are empty. If you use this on empty cells in the same rows as your used cells, the entire structure and formatting of your data will be affected. As a result, you can mix up your original data, which can have serious repercussions on the rest of your data management process.
There is a much easier way to avoid the cell limit altogether without risking the loss or unwanted change of data. Let’s see how you can use Sheetgo to avoid the 10 million cell limit.
Avoid the Google Sheets’ 10 million cell limit with Sheetgo
With Sheetgo, you can reduce how heavy your spreadsheets are without sacrificing any of your data.
Here are three ways that you can use the Sheetgo add-on to reduce the number of cells in your files.
1. Split your data
Sheetgo’s Split feature allows you to dramatically reduce the weight of your spreadsheet by dividing your one large spreadsheet into multiple smaller spreadsheets.
Not only do you save lots of time and effort with Sheetgo’s automated transfer, but you also have complete control of which data is transferred to a separate spreadsheet. What’s more, these spreadsheets will constantly be updated with the latest data, so if you edit or add to the original file, the change will automatically be applied to the new spreadsheet.
To find in-depth instructions on how to use the Split feature in the Sheetgo add-on, take a look at our post on How to split Google Sheet into multiple sheets.
2. Merge your data
If you have successfully split your large sheet into multiple sheets but you’re looking for a way to further reduce your cells, then merging your data might be the perfect solution for you.
Let’s say you have multiple files containing separate datasets. In each spreadsheet file, you can create a “summary sheet” that analyses the entire file’s data. You can then use the merge feature in the Sheetgo add-on to connect all of these summary sheets back into one main file. This allows you to receive all insights from each of your files in one place, without having to deal with too much data.
To discover how to merge specific tabs within multiple sheets into one main spreadsheet, take a look at this post on How to merge multiple files into one.
3. Filter your data
Perhaps you know the exact data you need to transfer within your large file. You can use the Sheetgo filters to extract the exact cells you need. Sheetgo offers three filters; Filter by condition, Filter by query, and Filter by cell color. These cover every single way you can select and transfer specific data within your spreadsheet.
Once you have successfully filtered the data to the cells you require, you can then automatically transfer them to a new spreadsheet file using Sheetgo. To learn more on how to use the Sheetgo filters, check out this post on how to
Filter Google Sheets from one sheet to the other.
How to increase the cell limit in Google Sheets
There you have it! Even though Google Sheets has a 10 million cell limit, there may be some circ*mstances where you reach it. The Sheetgo add-on offers multiple solutions to combat this dilemma so that you can avoid the risk of errors or computer crashes altogether. With Sheetgo, you can split, merge or filter your data as a way to increase the number of available cells in your Google Sheets – all without sacrificing any of your data.
Are you interested in learning more about how to make the most of your data within Google Sheets? Why not explore our blog post on Sorting and filtering data in Google Sheets?
Alternatively, check out our related blog posts below!
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.
