Google Sheets is one of the most popular spreadsheet tools thanks to its intelligent features and collaborative abilities. It also provides almost 5 times the number of cells in a single spreadsheet than Excel. Google Sheets gives users a 5 million cell limit, which at first glance, may seem like a limit you may never reach. However, the Google Sheets cell limit applies to the combined number of cells in a single file. This includes all of the tabs and every row or column that may or may not contain data. Once you know that, it makes sense why the 5 million cell limit may not seem so large.
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 exactly how reaching the Google Sheets cell limit can affect you and how to solve this issue.
What happens when you are close to the Google Sheets cell limit?
Usually, when you are near the 5 million 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 properly 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?
As mentioned previously, 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 your 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 the Google Sheets file from which you want to count its number of cells.
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:
In this example, I will be selecting a range from A1 to G637.
So my formula will look like this:
Step 3: You have your 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?Now that you know how to count the number of cells in your spreadsheet effectively, it’s time to explore the ways in which you can reduce the number of cells inside your Google Sheets. With the Sheetgo add-on, 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 make an edit or addition in the original file, the change will automatically be applied in the new spreadsheets too.
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 Sheets 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 effectively 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! Although Google Sheets may seem to give your plenty of cells at first glance, even 5,000,000 is not enough for some. However, the Sheetgo add-on offers several solutions to combat this dilemma, so 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.
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.