How to solve the 5 million cell limit in Google Sheets

Working on Google Sheets, have you ever received the warning that your work cannot go on, because you have reached the limit of 5 million cells ?

I often work with the Google Analytics Spreadsheet Adds-on. It’s a truly fantastic tool because it allows you to download the data processed by Google Analytcs directly on Spreadsheet.

In this way I can work this data to create dashboards and get an overview of the main metrics and dimensions based on my KPIs.

Unfortunately, however, it may happen that you encounter the warning message that further action (adding a new sheet or other raws) does not make it possible to continue the work because the limits specified by Google are exceeded.

How can I do? The solution is called =IMPORTRANGE.

Using this formula, I can get around the obstacle and import, from another Google Spreadsheet, all the data that I need and that I cannot downloaded via the first Spreadsheet.

Let’s see how.

How To – How The Formula =IMPORTRANGE Works

The formula =IMPORTRANGE is composed of two elements:

spreadsheet_url: The URL of the spreadsheet from which data will be imported
range_string: A string of the format “[sheet_name!]range” (e.g.: “Sheet1!A12:C45)

The formula allows you to import the range of values from a specific Sheet into another Google Spreadsheet.

How To – Practical Example

Let’s take a concrete case: I’m working on a Google Sheet, in which I created a Report to extract data from Google Analytics

The goal of my work: to see the % Exit of a page of the Checkout Funnel based on the value of the cart for the whole of 2019, divided by weeks. The data will be entered in a sheet called ‘Dashboard’ as seen below:

I created a 52 column report (the week of the year) with metrics and dimensions that interest me, as seen in the image below:

Since the website I am working on has a very high user flow, each sheet that is created has from 4,900 to 5,500 raws (in the image below there are 5,272):

The problem: given the amount of data, the report loads the data up to week 45. If I try to load other data, the message appears:

The solution: I create another Gogle sheet, in which I continue in the creation of my report from week 46 to week 52, as you can see below:

Finally….tadaaaaaaaa: I can use the formula =IMPORTRANGE and import the data into the “Dashboard” sheet.

Following the example, here’s what the data will look like in my Google Sheet:

In the case in question, I used the =QUERY formula to retrieve only some of the values of the almost 5 thousand raws that forms each individual Sheet. Since I can no longer insert Sheets, I created a new Spreadsheet and then I used the formula =IMPORTRANGE inside the formula =QUERY.

However, it is possible to use the formula alone (without =QUERY function as showed above). In fact in some cells I used =IMPORTRANGE to retrieve only the percentage value of sampling data from the other Sheet:

You can also use the formula =IMPORTRANGE with mathematical operators.

Here is another quick example: in this case I want to calculate the average number of items per order. Having exceeded the limit of 5 milion cells, I created a new Google Spreadsheet.

When I launched the Google Analytics API, I got results on the new Spreadsheet. With one operation, I get the result I want:

I showed you a way to “evade” the 5 milion cells limit. The limit is always active, but by working on multiple Google Sheets and using the formula =IMPORTRANGE you can complete your work without having to delete metrics or dimensions.

To sum up, this is the logic behind: