FAQ: Workbook Performance
What's the upper limit on workbook size, and what affects this limit?
The overall limit is 20 million cells per workbook. Keep in mind that performance impacts are much more dependent on workbook formulas and dependencies than on workbook size. For a complete list of workbook limits, see Reference: Workbook Limits.
This table lists workbook characteristics that can have a significant impact on performance, along with some tips for mitigating that effect:
Performance Impacts
| Tips
|
|---|---|
Using many instances of the same formula instead of an array formula. | Instead of using drag-fill to place many occurrences of the same formula in cells, use an unconstrained formula when possible. Remember to submit the formula using the unconstrained formula keyboard shortcut. For details, see Concept: Array Formulas in Workbooks. |
Dependencies within and between workbooks. A change in 1 cell might cause calculations in many other cells. If more than 1,000 cells are directly or indirectly affected, performance might degrade | When using external references, minimize the use of producer workbooks that are referenced by consumer workbooks, particularly if the producer workbooks are large. |
Calculation-intensive functions, such as VLOOKUP, SUMIFS, and so on, when changing a range value. | Worksheets provides indexed functions that are optimized for performance. These WD functions create a b-tree index for the specified range or array and provides better search performance for repeated use on the same data. If you're doing a lot of lookups on the same set of data, the b-tree index is cached and used again and again.We recommend using these functions instead of their non-indexed counterparts whenever possible:
To optimize workbook performance, we recommend when you reference a range in the WD functions that you write your formulas and specify the correct lookup ranges using absolute references. This ensures that less b-tree indexes will need to be created and cached. Absolute references ensure cell references in formulas remain constant regardless of where the formulas are copied or filled. To make a cell reference absolute, add a dollar sign ($) in front of the column letter and row number in the formula. Examples: $Q$45, $T$67, $A$1:$A$10, etc. |
Volatile functions that run any time Worksheets recalculates, such as RAND, NOW, TODAY, OFFSET, INDIRECT, and INFO. Actions that cause the workbook to recalculate include:
| You can wrap a volatile function in the ONCE function to prevent recalculation. Don't create a defined name that uses a volatile function and then use that defined name in many places. Instead, place the volatile function in a cell, and create a defined name as a reference to that cell. Example: To set up and use a defined name for the current date:
|
These workbook characteristics typically have a minimal effect on performance
- Workbook total size
- Number of cells
- Number of sheets (tabs)
- Cross-sheet formulas
- Formula sequence
- Summary tabs
Sometimes I see an alert telling me to come back to my workbook later. Why?
When Worksheets takes more than several seconds to complete an operation, an alert like this displays. For example, it might display while inserting or refreshing live data, or when recalculating a lot of formulas. The alert enables you to dismiss the workbook so you can do other work while the action completes. If you open a workbook that's already running a refresh or a Data Wizard process, you also see the alert.
Sometimes I see a Processing... indicator for a while, in the Drive file list. What determines whether I see the indicator or not?
When you create a workbook from existing data - such as from a report or an integrating application - Worksheets displays a
Processing...
indicator while adding the data into the workbook. The time required can vary from seconds to hours, depending on several factors, including:- The number of calculations and formulas.
- The size of the file or Workday report.
- The complexity of the integrating application generation process.
- Whether the workbook contains live data from any Workday reports, or other resource-intensive processes that the tenant might be running. (Aside from live data, Worksheets processes are independent of any other tenant processes.)
After Worksheets starts generating the workbook, you can't cancel it or start generating a new integrating application workbook. However, if a workbook is in a Processing state for more than 24 hours, Worksheets automatically cancels it, enabling you to move the workbook to the trash or regenerate it.
My workbook calculations are too slow. What improvements can I make?
- If you're making many changes to a workbook with complex formulas, you might want to prevent recalculations until you finish all your changes. You can temporarily change the recalculation mode to Manual from the dialog.
- Remove any data that you don't need for analysis.
- Minimize external references among workbooks.
- Minimize the number of formulas.
- Use whole-column references (such as A:A) sparingly.
- If you're using lookup formulas, try to organize your data so the calculations are on sorted data. Lookup functions are much faster with sorted data, and even faster when you can avoid using the exact match option.
How long should it take to upload an Excel spreadsheet?
Although most uploads finish within a few seconds, extremely large Excel spreadsheets might require several minutes. The progress bar in Drive lets you know that the process is occurring. You can work in other workbooks while the upload continues in the background.
Worksheets seems to run faster or slower depending on which computer I run it on. Why?
Rich web applications such as Worksheets can reduce performance if a computer is running other resource-intensive applications concurrently, or if processing capacity is low for another reason. Although it's not possible to recommend specific configurations because of the number of variables involved, you might find that in your environment you need to establish your own hardware configuration requirements.