Excel 2016 Slow Calculation - Delay After Every Click

Question

Hi,

Every time I press enter on a cell there is a 3-5 second delay before it loads.

I am using iterative calculations as my spreadsheet needs circular references and I also have calculation options set to Automatic.

Is there a way to speed this up? I have a MacBook Air from 2014

Thanks

Answer 1

I have yet to see a spreadsheet that NEEDS circular references. I'm sure there's a better alternative. That being said, how many iterations do you have it set to execute?

Answer 2

I have a similar problem but it's even more obvious.  Even when I open a blank worksheet, I am having similar "slow" experience. I did a cell selection from one cell to another with my mouse, jumping from left to right and the cell selected just did not follow. It froze a bit and then unfroze, then it repeats itself.. this is frustrating..

Answer 3

Hi,

To better assist you, we suggest that you post your query on this forum. The support team from Microsoft TechNet will be the best resort regarding your concern. Rest assured that their support team will be glad to assist you.

Let us know if you have any other concerns.

Answer 4

In general what you are talking about is 'optimizing' your spreadsheet. You need to find "bottlenecks" causing the slow performance in your sheet.

Although large volumes of data are a very common cause of slow performance, poorly designed formulas and functions can slow down even relatively "small" worksheets.

Here are some articles that highlight some common "mistakes" that you may be able to correct to speed up your sheet (I have more links if you want them):

@Speed up Excel in 15 Easy Stepshttp://professor-excel.com/15-ways-to-speed-up-excel/

Excel is a great tool for performing complex calculations. Unfortunately, the larger an Excel spreadsheet gets, the slower the calculations will be. Depending on the formulas, size of the workbook and the computer, the calculations may take up to 30 minutes. In this article, we take a look at 15 methods to save time and speed up Excel. ET MR Macros.docx

Calculation Options in Excel: Decide When and What to Calculate http://professor-excel.com/calculation-options-in-excel-decide-when-and-what-to-calculate/

Does this sound familiar to you: Excel takes too much time calculating. Instead of instantly showing the results, you have to wait for several seconds or even minutes for Excel to finish up the calculation. The problem: The larger your Excel model gets, the more you get frustrated by the lack of performance. ET MR Macros.docx

@util- RefTreeAnalyser Utility http://www.jkp-ads.com/RefTreeAnalyser01.asp

allows for easy Auditing of formula dependents and precedents, helps you trace errors, and will let you time your workbook calculation for each worksheet to find bottlenecks as well as check columns for formula inconsistencies. Jan Karel has a free demo version with limited functionality, if you’d like to take it for a spin.

@util- What is FastExcel Version 3 and why do you need it?http://www.decisionmodels.com/fastexcel.htm

There are 3 major products in the FastExcel V3 family which are targeted at different types of useage, so that you can buy only the tools that you need.  The Profiler gives you a comprehensive set of tools focussed on finding and prioritising calculation bottlenecks. If your spreadsheet takes more than a few seconds to calculate you need FastExcel profiler to find out and prioritize the reasons for the slow calculation. FastExcel Manager contains tools to help you build, debug and maintain Excel workbooks. SpeedTools provides you with a state-of-the-art tool-kit to help you speed up your Excel Calculations

see https://www.youtube.com/watch?v=EWUeZt02pho for an overview of FastExcel by Bill Jelen (Mr Excel). ET MR Macros.docx

10 Tips to Optimize & Speed up Excel Formulashttp://chandoo.org/wp/2012/03/20/optimize-speedup-excel-formulas/

Excel formulas acting slow? As part of our Speedy Spreadsheet Week, today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster. ET MR Macros.docx

10 ways to improve Excel performancehttp://www.techrepublic.com/blog/10things/10-ways-to-improve-excel-performance/2842?tag=nl.e072

Takeaway: You don’t have to live with sluggish workbooks. Try these tricks for a noticeable boost in performance. Most Excel files are small enough not to affect performance, but size isn’t the only thing that can slow things down. Fortunately, you don’t have to know all about multithreads and dual processors to eliminate bad performance. The following tips are easy to implement, so even the most casual users can improve performance when a workbook slows down. Better yet, apply this advice when designing sheets to help avoid sluggish performance altogether. ET MR Macros.docx

7 Reasons why you should get cozy with Index()**http://chandoo.org/wp/2013/09/18/index-formula-usage-and-tips/******Of all the hundreds of formulas & thousands of features in Excel, INDEX() would rank somewhere in the top 5 for me. It is a versatile, powerful, simple & smart formula. Although it looks plain, it can make huge changes to the way you analyze data, calculate numbers and present them. It is so important that, whenever I teach (live or online), I usually dedicate 25% of teaching time to INDEX().ET MR LOOKUPs.docx

75 Excel Speeding up Tips Shared by YOU! [Speedy Spreadsheet Week]http://chandoo.org/wp/2012/03/27/75-excel-speeding-up-tips/

As part of our Speedy Spreadsheet Week, I have asked you to share your favorite tips & techniques for speeding up Excel. And what-a-mind-blowing response you gave. 75 of you responded with lots of valuable tips & ideas to speed-up Excel formulas, VBA & Everything else. ET MR Macros.docx

Big trouble in little spreadsheethttp://chandoo.org/wp/2014/01/17/big-trouble-in-little-spreadsheet/

I pointed out that if you ever find yourself having to switch calculation to Manual, there’s probably something wrong with your spreadsheet.

This prompted one of the participants to come to me for advise regarding restructuring a spreadsheet with that very problem. This analyst had a file with only 6000 rows of data in it, but the file size was something like 35MB, and after each and every change she had to wait at least a minute for the file to recalculate before she could do something else.

It turns out there were two problems with her files that were easy to resolve. ET MR Macros.docx

Circular References: Calculation, Detection, Control and Removal**** https://fastexcel.wordpress.com/2015/09/14/excel-circular-references-calculation-detection-control-and-removal/

Excel circular references occur either when a formula refers to itself or when a chain of formulas links back to it’s starting point.

Most of the time circular references are created by mistake (unintended circular references) and should be corrected. They can also be used to solve iterative or goal-seeking problems in Finance and Engineering.

Handle Volatile Functions like they are dynamitehttp://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/****If you’re building large models, then you may want to use volatile functions – including OFFSET(), INDIRECT(), and TODAY() – with caution, because unless you know what you are doing, they *might* slow Excel down to the point that data entry is sluggish, if not downright tedious.

In fact, you *might* want to consider getting out of the habit of using these functions at all if there are  alternatives, and you might want to replace volatile functions in your existing models with non-volatile alternatives…I have reduced recalculation time in large models from minutes to milliseconds by doing just that! ET MR Macros.docx