Excel 2016 Freezing when entering data into a spreadsheet with multiple formulas

Question

When working in spreadsheets with multiple formulas, if users enter data into a cell then leave the cell, Excel freezes for a few seconds to a couple minutes. The time varies depending on which spreadsheet they are working in. What I have found using resource monitor is that during these freezes the CPU is hitting a hard usage cap of 25%. On our high powered workbooks it is using 100% of two of the eight cores, but on my laptop which is less robust, it is spreading out over all four cores but still locking up at 25%. According to the MSDN forum on Excel it is normal for some user defined formulas for all versions of Excel to cap out at 25%, however I have tested it in Excel 2010 and the process is instant and there is barely a blip in CPU usage on the same model of laptop with same RAM configuration.

I have checked the CPU allocation for formulas with Excel and it is set to use all available CPU power.

If you turn off automatic calculations the problem persists.

I tried copying a few lines of one worksheet into a new workbook and it only froze for about a tenth of a second, but as soon as I added the rest of one work sheet the time of the freeze increased to about half a second. The more data added the longer the freezes become at a surprisingly rapid rate. The half a second freeze was in a single worksheet that only had 50 lines, each line had formulas in five or six columns.

I have tried disabling links to other workbooks, and isolating this one but that has no effect on the duration of the freezing.

I can understand excel limiting itself to 25% of the CPU but what I can’t understand is why this process is using that 25% of the CPU when in 2010 the exact same spreadsheet caused no discernible CPU usage whatsoever.

What appears to be happening is that every time data is entered in a cell, Excel is recalculating every single formula in the entire workbook. That may be normal behavior or it may not be. According to a few forums I have visited in researching this, that is the way Excel works, and always has. What is clear though is that something has changed. If it recalculated every formula every single time in 2010 it did so with efficiency, whereas in 2016 it is hanging the whole application. I have found other reports of this happening in 2013 as well but in none of those instances has anyone suggested a solution that I have not already tried.

Any advice that any of you might have would be appreciated as the finance users have several spreadsheets of this nature which require hundreds of entries each month. They cannot function efficiently or with any degree of sanity if they have to wait for every single entry they make to process. Users have already stated that if we can’t find a solution they will need to be rolled back to Office 2010 as this makes impossible to do their job in a timely fashion.

Here you can find a sample line showing the formulas in one of the documents I have hidden columns in which there are no formulas or data. You can click on the image to expand it and make it readable.

Answer 1

I've encountered this problem as well. The program hangs and the CPU spikes for a prolonged time. Sometimes the duration can be a few minutes, other times it was more than half an hour!

It has usually happened to me at work when using average-complex formulas in spreadsheets with average long sheets. Some sheets have more than 400 lines and my formulas make use of INDEX, MATCH, EXACT, VLOOKUP, TRANSPOSE, OFFSET, among some other ones that are probably less taxing on the program. Also use a handful of named arrays.

This is a clear bug and should be looked into by the developers.