Strange Excel Bug I haven't seen before - inserting new columns prevents calculation

Question

I have a excel file I'm working on, it uses efficient formulas and only takes 3 seconds to calculate... until

If I try inserting a new column anywhere in the sheet, something strange happens. it takes ages to create to the column (longer than usual) then after that excel wont calculate, it just gets stuck on 0% as though its a huge excel sheet with loads of volatile functions in, however I haven't changed any of the formulas, just inserted a new column. I have left it for an hour it doesn't change off 1%.

if I delete the newly inserted column it still wont calculate, the only way i can get a functioning file back is to revert to a previous version

I have been working professionally with excel for 5 years and never seen a file behave like this, there also doesn't appear to be any previous forum posts on this topic so thought it was worth asking on here.

Can anyone give me some advice or things to try?

Answer 1

My gut instinct is that Excel is having to rerun your calculations on every row. A single calculation that takes 100ms to run is fast. But if you have to rerun that calculation 1000s of times then it adds up quickly. Excel normally only has to rerun formulas that are impacted by a data change you make (e.g. changing a cell value). But if you do something that causes Excel to have to recalculate everything then it'll cause problems.

You mentioned that the spreadsheet is huge. If you're talking 1000s or 10,000s of rows then that is going to get expensive quick. Adding a column is probably forcing the formulas to recalculate (because the relative offsets have changed, perhaps). I'd be curious to know if adding the column to the end of the rows still slows things down.

Removing a column is no different than adding a column so a refresh is still going to be needed. Perhaps you can test this by removing a column that isn't being used in the formulas. See if it takes forever to refresh.

To test this theory you can temporarily disable the recalculation in Excel. In the ribbon bar go to the Formulas tab. Then on the right side find Calculation Options. It is set to Automatic by default. Change it to Manual and then add the column. Once you've added it then try reenabling the auto calculation. If it grinds to a halt again then this is the problem.

Answer 2

Hi @barnaby king

After you insert a column, tons of re-calculations maybe triggered.

• Which version of Office are you? If conditions permit, please check it on Excel for web.
• Is your file size big? Does this issue occur in other Workbooks?
• Do you use many Names, Conditional Formatting, structured references with Excel tables?
• What formulas were used? Try to simplify the formulas. Some sub-formulas nested multiple times can be stored in separated cells, and then referencing these cells.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---