Excel Online won't calculate/ won't open [Best Solutions]
5 min. read
Updated on
Share this article
Improve this guide
Read our disclosure page to find out how can you help Windows Report sustain the editorial team Read more
The huge grid of rows and columns that make up the Excel program, plus other elements increases the size of the worksheets to work with compared to earlier versions that had slow-calculating worksheets.
Large worksheets in the program also calculate slower than smaller ones, but the big grid introduced with Excel 2007 puts performance as a priority, because slow calculation and data analysis tasks make it hard for the user to focus on what they’re doing and this can lead to more errors.
When you find Excel Online not calculating or not opening, some of the causes include these tasks and the size of the files being used. Multithreaded calculation is another cause for the calculation time lag, but the most important factor is how your worksheet is designed and built.
Once you identify and measure then improve the calculation issues in the worksheets, you can fix the calculation problems you’re experiencing with Excel Online.
Below are a few calculation considerations for Excel Online that can help resolve the calculation and file opening problem.
FIX: Excel Online not calculating/not opening files
- Open the workbook in the latest version of Excel
- Examine your formulas
- Calculate across workbooks
- Limit usage of volatile functions
- Check usage of array formulas
- Check for Defined names in your workbooks
- Check links and hyperlinks
1. Open the workbook in the latest version of Excel
When you open a workbook the first time, in a new version, it may take long to open if it has many calculations because the program recalculates it and verifies all its values. If you find the file opening slowly, or not opening at all, try the next solution.
2. Examine your formulas
Check your workbook and examine the formulas used, because some take up a lot of memory like the array formulas including Lookup, Offsets, Index, Match, and Indirect, which are okay to use, but the referenced ranges can cause Excel not to perform well, thus not open files or not calculate.
With the new grid, referencing a whole column means referencing more than a million columns, which will cause Excel Online performance issues, unlike the old version or grid where you could reference just one column.
For earlier versions, reconstructing your formulas to reference only the cells you need for your formulas will work. Check also the defined names so that there are no other formulas referencing whole rows and columns.
Some functions too have been deprecated from Excel Online and Excel desktop application so check if you’re using such functions because they affect calculation or file opening.
3. Calculate across workbooks
It is recommended that you limit the formulas doing calculations across all your workbooks if you’re trying to open the file over the network and/or Excel Online is trying to calculate huge data.
In this case, have the formula in one workbook, then create a link from one workbook to the next instead of calculating across networks.
4. Limit usage of volatile functions
There’s no need to have many cells with the TODAY and NOW function, as long as your date and time settings in your spreadsheet are current, you can use one of these functions at a time, then reference it via a defined link name.
5. Check usage of array formulas
These must be used in the correct way but you need not add more cells than you must have to your array. When a cell has a formula that needs calculation, then all cells in that formula will be calculated.
6. Check for Defined names in your workbooks
Defined names reference cells and formulas all over your workbook so as to add a name to your formulas, one that is friendly for use. Check for defined names linking to other workbooks or temporary internet files, as these links slow down or make Excel Online not calculate and/or not open your files.
The Name Manager tool comes in handy to help you find or view defined names that are invisible in the Excel Online interface, and those you don’t really need.
7. Check links and hyperlinks
Excel Online can bring live data from other spreadsheets, so you need to know the file and any external files it links to. There’s no limit on how many workbooks can be linked, but you can encounter issues around this, so test your file without the links to see if the Excel Online not calculating or not opening files issue is in this specific file, or in one of the files that are linked.
Did any of these solutions help? Share your experience in the comments section below.
RELATED STORIES TO CHECK OUT:
