The Google Sheets size limit is 10,000,000 cells. The default Google Sheet opens with 26 columns labled A through Z and a 384,615 row limit, which reaching the Google Sheets cell limit of 10,000,000 cells. Create any combination of rows and columns that remains under the 10 millions cell limit. Theoretically you could create a sheet with 1 column and 10,000,000 rows, which we explore below. (Note: Google Sheets also has a 100MB file size import limit).
The following post goes into detail on the Google Sheets row limit and the 5 best solutions for anyone with a slow Google Sheet. If you are looking for an immediate solution, try Row Zero, a blazingly fast spreadsheet that works just like Google Sheets but can handle big data.
Skip to the specific sections using the links below or continue reading for the full overview.
- What is the Google Sheets row limit (max row count)?
- Why Does Google Sheets Have a Row Limit?
- What are the effects of reaching the Google Sheets cell limit?
- The 5 best solutions for the Google Sheets row limit
What is the Google Sheets Row Limit (max row count)?
The fundamental Google Sheets size limitation is 10,000,000 rows (Note: Google Sheets also has a 100MB file size import limit). By default, when a new Google Sheet is opened, it has 26 columns labeled A through Z. Scrolling to the bottom of a new Google Sheet will take the user to row 1000. At the bottom of the spreadsheet, users see an option to add more rows by entering the number they would like to add and clicking the 'add' button.
To test the Google Sheets Limitation of 10,000,000 cells, a sheet with 26 columns should support 384,615 rows (10,000,000 cells / 26 columns = 384,615.4 rows). As expected, adding the necessary number of rows to reach 384,615 rows in a sheet with 26 columns is successful.
If a user attempts to add more rows, Google Sheets produces an error alerting the user to the Google Sheets cell limit of 10,000,000 cells.
Theoretically, if a user deletes columns, Google Sheets should increase the row limit to reach the 10,000,000 row limit. To begin the test, all but two columns were deleted by right clicking the column heading and selecting 'Delete column' from the context menu. This can be done in bulk by selecting multiple columns at once. Then using the 'add columns' menu in the bottom left-hand corner, add columns to get to 5,000,000 rows (10,000,000 cells / 2 colulmns = 5,000,000 rows). Google Sheets really starts to slow down in executing this command and it often shows the 'working' message.
Eventually, Google Sheets displays a sheet with 2 column and 5,000,000 rows.
Now for the final step to test the Google Sheets cell limit a user should be able to reduce the sheet to 1 column and expand the row count to 10,000,000 rows. Like before, column B is deleted and 5,000,000 more rows are added using the 'add rows' feature in the lower left-hand corner. Unfortunately this attempt proves unsuccessful and Google Sheets start encountering performance issues. First the 'Page unresponsive' error is thrown then the 'Aw Snap' chrome error screen is shown.
Reloading the page takes a long time and reverts to the previous saved state of a Google Sheet with 2 columns and 5,000,000 rows. The attempt to create a Google Sheet with 10,000,000 rows and 1 column was unsuccessful but theoretically it is possible.
The next section dives into the reason why Google Sheets has a row limit but if you are looking for a spreadsheet that can support millions of rows and columns, Row Zero is a blazingly fast spreadsheet designed for big data.
Why does Google Sheets have a row limit?
This architecture makes it possible to support historical versioning, multi-user collaboration, and off-line mode, but also means Google Sheets is limited to the compute power of a user's desktop and the constraints of the underlying code.
The guidance when it comes to dataset sizes in Google Sheets is performance is generally great below 10,000 rows. As we experienced earlier, Google Sheets starts to drammatically slow down as the number of rows, columns, and cells grows. It becomes fairly unusable over 100,000 rows of data. The Google Sheets 10,000,000 cell limit is likely in place based on performance testing of the application and where it fundamentally breaks down.
Microsoft launched Excel in 1985 and Google launched Sheets in 2006. Since its launch, Google Sheets has become a widely used spreadsheet. When Google Sheets launched in 2006, it had a 2 million cell limit, which was increased to 5 million cells in 2019, and increased again to 10 million in 2022. In its current form, Google Sheets is great for small personal spreadsheet projects and works well for small companies. As dataset sizes grow, Google Sheets users inevitably hit performance issues and need a more powerful solution. If you are looking for free, more powerful alternative, check out Row Zero, a blazingly fast spreadsheet that works just like Google Sheets but for big data sets.
What are the effects of reaching the Google Sheets row limit?
- Fail to load file - If a file is bigger than the Google Sheets file size limit of 100MB, an error message like the one below will display and the file will not load.
- Slow load times - Uploading a file under the 100MB file size limit and 10,000,000 cell limit will succeed but the closer files get to the size and cell limit, the longer they will take to import.
- Inoperability - Google sheets performance limitations may manifest in features ceasing to work. For example, saving may fail or formatting may stop working.
- Crashing - With bigger dataset sizes, row and column quantities, Google Sheets may crash or become unresponsive after certain formulas or transformations are executed on the dataset.
The 5 best solutions for the Google Sheets row limit
The following are options for working around the Google sheets row limit.
1. Use a more powerful spreadsheet
Row Zero - Row Zero is a blazingly fast spreadsheet that is designed to have the same functionality as Google Sheets but can handle multi-million row datasets. Row Zero runs in the cloud, supports multi-user collaboration, and can ingest data from a csv upload, S3 import, database query, API connection, or a connection to any other cloud data source.
2. Delete blank cells in Google Sheets
To reduce the number of cells, deleting blank cells can be a fruitful exercise. Using sort and filter features can help identify which columns or rows contain large numbers of blank cells, which can then be deleted. Deleting blank cells frees up memory, which helps preserve Google Sheets from slowing down.
3. Use Google Big Query
Google BigQuery is a fully managed, serverless data warehouse and analytics platform that enables users to analyze massive datasets using SQL queries in real time. Google recently added a connection from Big Query to Google Sheets, allowing users to pull data from the Big Query database into their google sheets using SQL commands. Instructions can be found here. Keep in mind that even with the big query connection, data sets that are bigger than the Google Sheets max row count or cell limit, will still cause Google sheets to slow down or crash.
4. Use a different application that is better at handling large amounts of data.
- Jupyter Notebook - Jupyter is a tool best suited for software engineers, data scientists, and analysts that know how to code. The application enables manipulation of large data sets with commands written in code cells and can handle millions of rows, though it runs locally and is dependent on the hardware in a user’s computer.
- SQLite - SQLite is an easy to install lightweight database that can be installed and run on a user’s computer. The database can store millions of rows of data, which can be accessed by writing SQL queries to transform and analyze data. Once transformed, the data can be opened in a spreadsheet.
5. Open in a notepad file and truncate
Open the large data set in a notepad file, like notepad++ and delete rows until it can easily be opened in Google Sheets.
As you are now well aware, there are a number of different options for working around the Google Sheets cell limit of 10,000,000 cells. The solutions generally require using nother applications to store the dataset or manipulate it before transferring it back into Google Sheets. For folks wanting to do their work in a spreadsheet, the easiest option is to simply use Row Zero, a blazingly fast spreadsheet designed to work with large data sets, that runs in the cloud, and doesn’t require a download.