Nothing can be absolutely perfect, as imperfection is the only truth! Although it sounds somewhat philosophical, it is true even in the tech field! Even the mighty Google can’t make their tools free from limitations. There are so many limitations in Google Sheets that you may not even believe in the first place.
The most prominent limitation in Google Sheets is the 10 million cell limitation. Besides, you cannot insert more than 50,000 characters in a single cell, nor can you assign a value above 1.79769E+308. And if you talk about the IMPORT functions, it is impossible to use them more than 50 times in a single spreadsheet.
But these are only a handful we have talked about, as there are many such limitations in Google Sheets. When you compare it with MS Excel, the limitations become even more prominent. So without any further ado, let’s know about the current limitations of Google Sheets.
- Exploring Google Sheets Limitations
- 1. General Limitations
- What’s The Cell Limit In Google Sheets?
- What’s The Row Limit In Google Sheets?
- What’s The Column Limit In Google Sheets?
- How Many Tabs Can You Have In Google Sheets?
- What Is The Import Limit In Google Sheets?
- What Is The Google Sheets API Limit?
- What’s The Comments Limit In Google Sheets?
- Google Sheets Formulas Limitations
- 2. Collaboration Limitations In Google Sheets
- Offline Access Limitations
- 1. General Limitations
- How To Work Around Google Sheets Limitations?
- Final Note
Exploring Google Sheets Limitations
Google Sheets is a powerful spreadsheet program, no doubt about that! It enables us to work and collaborate with teammates on the go, even while working on a complex spreadsheet project. But, it is still not free from limitations. Let’s check them out!
1. General Limitations
You may not know it, but there are certain general limitations, such as how many rows Google Sheets can handle or how many columns you can incorporate. So, we will first look into these baseline limitations.
What’s The Cell Limit In Google Sheets?
Google Sheets, when launched, has started with a cell limitation of 2 million (2,000,000). They then increased the cell limit to 5 million (5,000,000) in 2019. And recently, that limitation has increased to 10 million.
So, you can now have up to 10,000,000 cells in a single spreadsheet in Google Sheets. These cell limitations not just apply to the files that you have created in GS but also apply to the imported files.
In that 10 million cell limit, Google Sheets offers a maximum of 18,278 columns (up to ZZZ column) in a spreadsheet. However, if the 10 million cell is already vested (due to a higher row number), you will not be able to insert up to 18,278 columns.
You can check out more about this limitation on the official Google blog.
What’s A Single-Cell Character Limit?
Google Sheets also has certain limitations when it comes to the maximum character value of a single spreadsheet. As of mid-2023, a single cell in Google Sheets can hold a maximum of 50,000 characters.
This 50,000-character limit implies all the new, imported, or existing spreadsheets in GS. If you are importing a file (from MS Excel or any other program), all the cells with more than 50,000 characters will be automatically removed in Google Sheets. You can find a detailed answer in Google Support.
Bonus: If you are importing a large and complex file, it is better to arrange the rows and columns first before you start working. Check out my latest guide on how to group rows and columns in Google Sheets to know more!
What’s A Single-Cell Value Limit?
Not just character limit, Google Sheets also has a limit of the maximum value allowed in a single cell of a spreadsheet. You can insert up to 1.79769E+308 value in a single cell this year.
It is a huge number, equivalent to 1.79769 x 10308, that you can now insert in a single cell. So, unless you are a scientist or a statistician, this single-cell value limit is more than enough to work with!
What’s The Row Limit In Google Sheets?
Do you know how many rows Google Sheets can handle? You are maybe in shock to know that there are limitations for maximum rows in Google Sheets!
You can only add up to 40,000 new rows at a time in GS. But that doesn’t mean your spreadsheet will have equal to or lesser than 40k rows. During your work, you can keep on adding new rows unless it reaches the 10 million cell limit overall.
Fun Fact: Adjusting the row height is necessary in most cases but somewhat difficult to execute. So, to do that, follow my comprehensive guide on changing row height in Google Sheets.
What’s The Column Limit In Google Sheets?
Yes, like row limit, there is also a column limit in Google Sheets that defines how many columns you can add in a single spreadsheet. As of this year, the column limit is 18,278 in Google Sheets.
However, remember that if you have crossed the 10 million cell limit before reaching 18,278 columns, you’ll not be able to add any further. This rule also implies to the files you import.
Bonus: Applying a formula to every cell is a hectic job, no doubt about that! But you can now apply a formula to an entire column in Google Sheets.
How Many Tabs Can You Have In Google Sheets?
You can have up to 200 tabs in Google Sheets at a single time. If you work with the default cell settings of a new spreadsheet, this will remain the limitation. But it can increase or decrease depending on the size of each tab.
It is because the cell limit again comes on top. You cannot add more tabs if the overall cell limit reaches 10 million.
Note: If you are working with too many tabs, it may create an issue to properly organize the data. In such cases, you must know how to hide and unhide tabs in Google Sheets.
How Many Sheets Can You Have In Google Sheets?
There are limitations in Google Sheets in terms of how many spreadsheets you can add, although it is not properly defined by Google.
Here, the cell limit again comes into play! By default, a single spreadsheet has 26 columns and 100 rows, which translates to 26,000 total cells in a single sheet. So, for the 10-million cell limitation, you can have up to 384 (10,000,000 / 26,000) spreadsheets.
Working Tips: If you are importing multiple spreadsheets at once, you may have hundreds and thousands of duplicate values and cells. You can simply remove duplicates in Google Sheets to consolidate the data.
What Is The Import Limit In Google Sheets?
It’s time to talk about the import limit imposed on Google Sheets. There are four functions in GS that can help you to import directly to the spreadsheet. Those are IMPORTDATA, IMPORTFEED, IMPORTHTML, and IMPORTXML.
While using any of these functions, you can import up to 50 times in a single spreadsheet. The maximum allowed string length for any import is limited to 50,000 characters.
But for the IMPORTRANGE function (by which you can import data from another spreadsheet in Google Sheets), there is no limit! However, if you try to incorporate this function too many times in a single spreadsheet, it may return an “internal error” message.
So, it is better to just filter data in Google Sheets beforehand to minimize the usage of too many IMPORT functions.
What Is The Google Sheets API Limit?
It is true that Google Sheets limitations are also implied in its API (Application programming interface). Although there is no particular limitation on the file size, Google recommends a maximum payload of up to 2 MB in a single API request.
There are also limits on the requests (both read and write) you can process through Google Sheets API. Exceeding that limit will return the “429: Too many requests” status code. Refer to the table below for the current read and write request.
|Request Type||Per Minute – Per Project||Per Minute – Per User – Per Project|
Please note that if you send more than 300 read or write requests through Google Sheets API, it will return the “429” status code for all the additional requests.
What’s The Comments Limit In Google Sheets?
Again, there is no specific limit on how many comments you can add to Google Sheets. But according to this thread on Stack Exchange, most apps in Google Workspace have a maximum allowed comment length of 2,048 characters.
However, you may add comments with more than that limit if the overall file size is low. Uploaded spreadsheets cannot be larger than 20 MB in Google Sheets. So, you can only add comments until the file reaches this maximum size limit.
Google Sheets Formulas Limitations
Using formulas in Google Sheets is super easy, sometimes even easier than traditional MS Excel. But there are certain limitations on the formulas as well, which are as follows.
- Google Finance Formulas: Up to 1,000 formulas in a single spreadsheet
- CONCAT Function: Up to 50,000 characters for concatenate
- IMPORTRANGE Function: Up to 50 cross-spreadsheet references
- Other IMPORT Functions: Up to 50 formulas each per spreadsheet
Do remember that the more formulas you incorporate into your Google Sheets, the more unresponsive and laggy it becomes. So, try to use limited formulas.
You can also lock cell ranges in Google Sheets to consolidate your data and also make the processing faster.
2. Collaboration Limitations In Google Sheets
You can now easily collaborate with your teammates and colleagues in Google Sheets for any project. And this makes the team game easier than ever. But there are certain limitations to the collaboration aspect of Google Sheets. Let’s check them out!
Maximum Number Of Concurrent Users
Google Sheets limitations also imply the number of people you can share and collaborate with in your project. In the older version, Google Docs and spreadsheets can be viewed and edited by up to 10 people at a time.
But Google has increased the limit since then. Now, you can share a doc or spreadsheet with up to 200 concurrent users (editors and viewers combined) in a single time. Up to 100 people can edit the spreadsheet and comment on it simultaneously. You can find more details on this Google support page.
There is a workaround to bypass these collaboration limitations. You can simply “publish” your spreadsheet through Google Sheets on the internet to create a permalink. And every person that you have shared the link with will have access to view that file. There are no limitations on published files!
Bonus: Sharing and collaborating can be a bit difficult if you are a newbie or just learning the real tricks of GS. Follow my latest guide for sharing and collaborating in Google Sheets to know more.
Real-Time Editing Limitations
Although you can share any spreadsheet with up to 200 people in Google Sheets, not everyone can edit or comment simultaneously, as it is the viewing limit. So technically, up to 200 people can “view” that spreadsheet without any editing rights.
Out of those, only 100 people or teammates can edit or comment at the same time. It is because Google Sheets supports up to 100 open tabs at a time.
If you have more than 100 people present in a spreadsheet, only the owner and the people having editing access will have permission to edit or comment. You can let the file be viewed (not edit) by as many people as you want by publishing it to the web.
Permission And Access Control Limitations
There are no specific limitations in Google Sheets regarding permission and access control. However, you can grant access to edit, download, or both in Google Sheets. And the steps are as follows!
- Step 1: Launch the spreadsheet in Google Sheets.
- Step 2: Click on the “Share” button in the top-right corner.
- Step 3: Click on the drop-down menu under the “General Access” header.
- Step 4: Select “Restricted” to block all access.
- Step 5: Click “Done” to save the settings.
There is another type of access you can grant where people can “view” your spreadsheet but cannot download or edit it. The steps are mentioned below!
- Step 1: Open your spreadsheet in Google Sheets.
- Step 2: Click on the “Share” button located at the top-right corner.
- Step 3: Click on the “Settings” button (Gear icon).
- Step 4: Uncheck the “Viewers and commenters can see the option to download, print, and copy” option.
- Step 5: Press the “Back” (←) button and then click “Done” to save it.
Note: Assigning new limited permissions will reflect only the way anyone can view, edit, or download within Google Drive. It is difficult to restrict that if the person is using other means to download.
Revision History Limitations
Google Sheets gives you the scope to revise and restore any version of your spreadsheet. However, there is no specific limit to the number of revisions permitted in GS.
If you talk about the Google Sheets API, you can set any revision to “Keep Forever” in your account. And you can assign that status to up to 200 revisions for a single spreadsheet.
Google Sheets will also keep up to 100 revisions if you haven’t been assigned the “Keep Forever” tag for up to 30 days. After that period, those 100 revisions will be replaced by the latest 100 revisions. You can find more details in this developer’s guide.
However, if you want to name any revision, Google Sheets allow up to 40 named revised versions as of 2023. So, you can assign individual names to up to 40 revised versions.
Bonus: You can now easily check and restore your edits through the history option in GS. To know more details, check out my latest guide on edit history in Google Sheets.
Offline Access Limitations
You must have already known that you can now access Google Sheets in offline mode, like any other Google Workspace app. But there are some limitations as well.
Limited Offline Functionality
First and foremost, you cannot use all the available tools while in offline mode. Besides, you cannot collaborate in real time. If the file you are trying to open is larger in size, it may even cause an issue.
One support thread of Google already highlighted this problem with larger files in Google Sheets.
But to have offline access, you need to enable the option from your Google Drive settings. The steps are easy, though!
- Step 1: Go to drive.google.com from your browser.
- Step 2: Log in to your associated Google account.
- Step 3: Once signed in, click on the gear icon at the top-right corner.
- Step 4: Click on the “Settings” option from the menu.
- Step 5: Once you are redirected to a new page, tick the box in front of the “Create, open, and edit your recent Google Docs, Sheets, and Slides files on this device while offline.”
- Step 6: Exit from the settings page.
Please note that this offline functionality is device-specific. So, you cannot access all the files from other devices in offline mode until you pre-authorize it.
Syncing Issues And Data Loss Risks
Data loss is a serious threat! According to IT Compliance Group, close to 22% of corporates experience sensitive data loss each year. And things are no different in Google as well, especially if you work in offline mode.
All the work you have done in offline mode will not be saved in real-time until you have an active internet connection. In offline mode, it will only keep a copy in your browser.
So, if more people are working on the same spreadsheet where you’ve done something in offline mode, there is always a chance of data loss. Syncing issues will also be a big headache if you are working on a large file.
Bonus: Data analysis can be easier and more streamlined if you rely on named ranges. To know more in detail, check out my newest guide on named ranges in Google Sheets.
How To Work Around Google Sheets Limitations?
Although Google Sheets comes with so many limitations, it is still the most popular online spreadsheet program in 2023. But if you are annoyed with these limitations, or if you need more control and more versatility, there are some easy workarounds!
Use Multiple Spreadsheets
The core limitation that Google Sheets has is the 10 million cell limit. If you incorporate more cells in a single spreadsheet, it will reach closer to the limit, thus making the sheet slower and less responsive.
You also can’t insert more than 50 IMPORT functions or finance formulas in a single spreadsheet. But in real life, you’ll feel that your spreadsheet is becoming less responsive after inserting the formula more than 20-25 times.
So, it is better to use multiple spreadsheets by dividing your data. In that way, you will make sure that you are not reaching the 10 million cell limitation. Besides, you can also ensure that each spreadsheet has lesser formulas and functions to increase the responsiveness and processing speed.
Bonus: If you have large or mixed values in a cell, it is better to split it into different columns to make it more concise. To do that easily, follow my latest guide on splitting text and data into columns in Google Sheets.
Use Google Data Studio
Google Data Studio (now Looker Studio), the latest data visualization online tool from Google, is extremely reliable in terms of generating easy-to-understand summaries and reports. You can also build your customized dashboard for a more streamlined data visualization.
You can also compare performances over time with different data sets on Data Studio. Once you launch this online tool, you can simply click on the “Create | Report” option to select a specific data source.
You can import data from not just Google Sheets but also from Google Analytics, YouTube Analytics, MySQL, BigQuery, and many more. Besides, you can even directly import CSV files into Data Studio.
So, if you are working with an extensive data set or if you need streamlined reporting and workflows, it is better to use Looker Studio than Google Sheets, as it has very few limitations than GS.
Use A Different Spreadsheet Program
Although Google Sheets is the most popular online spreadsheet application, it is not the only one! There are hundreds of both paid and free spreadsheet applications available that you can also use.
Gigasheet is our top recommendation as it can handle more rows and columns than Google Sheets. Besides, it also doesn’t have any such 10 million cell limit. You can even process more than 50 formulas and incorporate as many tabs as you can with this tool. Both the free and premium subscription models are available.
Proofhub is our next recommendation if you are into project management. This Kanban-styled tool will streamline your workflow and tasks in no time.
Smartsheet is our last recommendation, as it is also an excellent online tool for project management and streamlined workflow. You can project your data visually with this tool. And besides the online web version, you can even access it through the smartphone app.
There is no denying that Google Sheets come with so many limitations that often feel annoying, especially while working with a large dataset. You may need to delete rows in Google Sheets manually to get rid of the cell limitations.
Other than the limitations we have listed above, there are many other issues with GS, too, such as the extremely low number of available shortcuts or lesser customization options.
The option for different formulas are also very limited in Google Sheets, and there is no way to incorporate custom formulas as well. Sometimes, Google Sheets feel relatively underpowered than the mighty MS Excel.
But on the upside, Google Sheets offer better reliability and data security. And over time, this unique spreadsheet program is becoming more optimized and less restrictive.