How to solve the 10 million cell limit in Google Sheets

Written by
Holly Meikle
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 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:

Google Sheets cell limit 0
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.

Google Sheets cell limit 1

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)
Google Sheets cell limit 2

Step 3: Get your blank cell count

Once your formula is ready, click enter.

The number returned is the number of non-blank cells.

Google Sheets cell limit 3
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.

google sheets cell limit 5
In a column, click on the filter icon. Under Filter by values, click Clear, then select (Blanks). Press OK.
google sheets cell limit 6
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.

google sheets cell limit 7
Click on the filter icon again. Click Select all to bring back the cells with your data.

Once finished, click OK.

google sheets cell limit 8
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 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 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.

Split feature Sheetgo
Sheetgo add-on

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.

Merge feature Sheetgo
Sheetgo add-on

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.

Filter feature Sheetgo
Sheetgo add-on

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 circumstances 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.

You may also like…