Google Sheets is a great tool for working with data. It’s free and easy to use, which makes it very popular. However, there are some disadvantages to working with this tool, as it has limitations that condition how much data you can work with, as well as what you can do with it.
In other words, whether or not this is the right tool for you will depend on a few things. Do you need to handle a large volume of data? Do your spreadsheets use a lot of formulas? Do they contain multiple references to external data or to other formulas? If you answered yes to any of these questions, then Layer will be a great addition. That way, you can continue using Sheets, without having to compromise on performance.
In this article, you’ll learn about Google Sheets’ main limitations and disadvantages. As an alternative option, you can use integrated automation tools, such as Layer, that work on top of your spreadsheets and provide you with powerful workarounds.
Google Sheets limitations
What exactly are the limitations of Google Sheets? Given the popularity of the tool, there should be more available answers. However, there’s no official list of limitations, so there’s a lot of disagreement regarding its disadvantages and limitations.
Additionally, there have been some changes to Google Sheets recently, and some of the old limitations have actually changed. Below, you can read about the known limitations of Google Sheets and how they might affect you and your projects.
1. Cell number limitation
The first limitation is an important one, as it also limits other features. Google Sheets limits you to 10,000,000 cells, for both new and converted documents. This means that if each of your tabs contains 1,000,000 cells, you are limited to 10 tabs. In other words, it wouldn’t matter what the tab limitation actually was. If you’re likely to have close to 10,000,000 cells, keep in mind that the sheet will become extremely slow and difficult to work with.
How to Import CSV to Google Sheets Automatically?
If you need to open CSV files in Google Sheets, you can either do it manually or automatically. Here's how to import CSV files into Google Sheets.READ MORE
2. Cell size limitation
In addition to the 10,000,000 cell limit, you can have a maximum of 50,000 characters in one cell. If you try to paste more than 50,000 characters into a cell, you’ll get a warning message. However, be careful if you’re importing data, as it will simply skip the cells that exceed the 50,000 character limit; in other words, you won’t get a warning about data loss. If you work with large numbers or strings, you’ll need to check that none of the cells contain more than 50,000 characters.
3. Column limitation
Google Sheets limit the maximum number of columns to 18,278, but due to the overall cell limitation, you can only have this number in a Google Sheet with 1 tab and 547 rows. If you work with high-volume data that spans numerous columns and tabs, this could seriously limit the number of rows you can use.
4. Row limitation
There is no set limit to the number of rows allowed in Google Sheets, but knowing the cell limitations, you can find ways to work around this. If you only have 1 tab containing 1 column, you could have 10,000,000 rows with one cell each. In theory.
In reality, not only is it difficult to see what you could use that for - other than a very long list - but it would also be practically impossible to scroll through this manually. As you approach any of the limits - cell number, column number, etc. - Google Sheets becomes slow and difficult to work with.
5. Tab limitation
Like with rows, there is no set limit for the number of tabs, but the cell limitations also apply. By default, each new Sheet has 1 tab containing 26 columns and 1,000 rows (26,000 cells). If you keep this size, you can have 384 tabs. However, this is the maximum limit, which you can’t get too close to without compromising performance. In practice, even with 100 default tabs - all blank cells - the workbook becomes very slow and is likely to crash.
6. IMPORTRANGE formula limitation
Google Sheets removed the limitation of only 50 IMPORTRANGE formulas per workbook. However, users have still reported some issues with its use. The first is that the formula sometimes fails to retrieve the data due to an “internal error”, but there is no indication of the error source.
While Google is working on this issue, there doesn’t seem to be a single cause, so it could take longer to fix. The second problem is related to the way in which this “internal error” is handled. The contents of the cell in question are replaced by “IMPORTRANGE internal error”, but there is no visible warning. This can lead to more errors or inaccurate data in related formulas.
Google Sheets allows you to import and link a specific range of cells from another spreadsheet. Here's how to use the IMPORTRANGE function in Google SheetsREAD MORE
7. External data limitation
Previously, Google Sheets allowed a maximum of 50 formulae to get external data: IMPORTDATA, IMPORTHTML, IIMPORTFEED, IMPORTXML. This limitation no longer seems to apply, but this doesn’t actually mean it’s a good idea to add more. Just like with the new cell number limit, you might not want to push it. Even with 50 of these formulas, the Sheet can become slow and uncooperative. Additionally, the cell size limitation also applies to external data, so you will lose anything that exceeds the 50,000 characters.
Although Google Sheets has tried fixing some of its limitations, such as the 10,000,000 cell limitation, they still fail to meet realistic standards In fact, if you have that many cells in your spreadsheet, you should probably consider a database instead. However, even with 1,000,000 cells, your spreadsheet will perform slower and become more prone to crashes. If you have complex formulas and references to external data, it could become completely unworkable. As an alternative solution, Layer works on top of your spreadsheet tool and can help overcome most of these limitations.
How to overcome Google Sheets collaboration limitations with Layer?
Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you can:
- Manage Access: Give spreadsheet access on a tab or cell level to relevant stakeholders.
- Collaborate: Automate tasks, set deadlines, and communicate on top of your files.
- Review & Track: Consolidate input, track changes, and restore previous versions.
There are also some disadvantages to Google Sheets that are not as quantifiable as the limitations above. For example, shortcuts are very limited, and customization doesn’t work very well. While they occasionally add more built-in formulas, it doesn’t offer many possibilities for custom functions. Overall, the options for data transformation and analysis are quite limited, especially when compared to Excel.
If any of the disadvantages or limitations above seem like they might apply to you, don’t despair. By using the Layer web app, you can continue working with your Sheets and Excel spreadsheets, but with an extra layer of functionality, connectivity, and security. You can easily share specific parts of your spreadsheet with collaborators, collect and consolidate data, and review changes, for an effortless and seamless collaborative experience.
If you’d like to learn more about the main limitations you can encounter when using Excel or Google Sheets, these articles will be helpful.