If you're close to exceeding Google spreadsheets size limits, you'll see a message at the top of your spreadsheet that indicates what limit you're about to reach.
- Number of cells: 400,000 total cells across all sheets
- Number of columns: 256 columns per sheet
- Number of formulas: 40,000 cells containing formulas across all sheets
- Number of tabs: 200 sheets per workbook
- GoogleFinance formulas: 1,000 GoogleFinance formulas
- ImportRange formulas: 50 cross-workbook reference formulas
- ImportData, ImportHtml, ImportFeed, or ImportXml formulas: 50 functions for external data.
Google forms size limits
These size limits also impact the spreadsheets that collect form responses (for example, if you send a survey). To determine the number of responses that a form can handle, take the number of questions in your form and the number of cells containing other data into account.
Google spreadsheets also have complexity limits. Every time a cell is updated, any cell that references it will also be recalculated. If formulas become too complex or take too long to calculate, the spreadsheet will timeout during calculation.
Formulas that increase the complexity of a spreadsheet include:
- VLOOKUP, QUERY, SUMIF, and many similar formulas that take a large range of cells as input.
- Volatile formulas (e.g., NOW, RAND, OFFSET, INDIRECT) are recalculated every time the spreadsheet is modified. If there are a large number of formulas that depend on cells with volatile formulas, they will be re-calculated on each edit, which may slow down a spreadsheet.
- Import-based formulas (e.g., IMPORTRANGE) are recalculated periodically and magnify complexity.
Approaching complexity thresholds
If a spreadsheet takes several seconds to finish calculating, a warning message will appear at the top of the screen. This warning means that formulas may start to display errors or take a significantly long time to calculate if complexity in the spreadsheet is increased. To remove this warning, reduce complexity in the spreadsheet using the tips listed below.
Exceeding complexity thresholds
If a spreadsheet exceeds complexity thresholds, an error message will be shown at the top of your screen. Cells that haven't finished calculating after several seconds will be annotated with errors; however, these cells will continue to calculate in the background until they update with correct values. These calculations may take up to several minutes, depending on the size of the spreadsheet.
Note that every time a cell is updated, all cells dependent on that cell are recalculated. This means that changes to a single cell could cause the entire spreadsheet to recalculate. In some cases, this could take up to several minutes. To avoid exceeding complexity thresholds, go back and modify recent updates to the spreadsheet, following the tips below to reduce complexity.
Tips & Tricks
- Try breaking up a large spreadsheet into multiple smaller spreadsheets.
- Minimize the number of complex formulas. See the list above for examples of functions that increase complexity.
- After getting data points through any complex formula, try to copy-and-paste them as "values only."
- Select the values and copy them.
- Go to the Edit menu.
- Point your mouse to "Paste special."
- Select "Paste values only."
- Minimize the number of cells that depend on:
- A large number of other cells.
- Cells with complex formulas.
- Cells that are changed frequently.