Forum Discussion
Excel does not allow me to insert rows (and when it does, its armageddon)
Hi everyone,
Im new here, I've been solving excel issues for quite some time thanks to this place, but never needed to post before (you know how it is, somebody always faced the same problems before), so thank you already in advance.
Let me try to explain what is happening. I've been working on a pretty heavy financial model (revenue forecast and such) and the model worked just fine, but suddenly, in the past days, excel stopped allowing me to insert additional rows or columns. It was a problem, but I manage to go around it, doing new calculations at the bottom of every page. The weird thing, is that I could insert rows from time to time. When excel feels about doing it, allows me to insert blank rows, or to copy and paste another row. When I just open the file, I may be able to insert 2-3 rows, but then just stops working again, so every change in the model, takes forever.
But the tricky part, started happening two days ago. One of these times I am able to insert a row, every single formula, from the row I just inserted, to the bottom of the page, changes. Let me give you an example:
In the row nº10 I have a formula, begining in the column AA that says: "=AA110+AA111" and this formula is replicated all along the row up to the column AZ for example. So, if I insert a new row, before the row nº10. For an inexplicable reason, the formula in the column AA does not change, but the formula in the next rows (AB onward) immediately change to "=AB111+AB112", which obviously breaks my model into pieces.
This is not a problem of the file being corrupted, because I checked out older versions of the file, and I have the same problem, and at the same time, every single colleage of mine is able to work in the file without a single problem (we work online on a OneDrive). I have tried to reinstall the whole office package, but I have the same problem.
Also, I forgot to mention, but this is now happening in quite a number of different files...
Have anyone faced something similar? Any ideas?
Thanks again!
(PD: sorry for the long post)
Hi all. I may have found out a way around this problem. If you change the workbook calculations to manual, it allows you to insert cells and the file does not mess with the formulas. Not sure for how long it will work, but at least you are allow to edit the models while somebody (hopefully) fix the issue
14 Replies
Did anyone ever respond with a real fix for this? This same problem started for me yesterday on a file that is rather complex, but had been working fine up until then. And the "Manual Calculation" workaround did not help. The workbook pulls real-time Stock Market data and makes calculations based on these.
I went back to previous versions autosaved in OneDrive, ones which were definitely working before today, and these exhibit the very same problem. A very nice MS Customer Support guy, who directed me to this post, spent over an hour looking at my issue, in my actual file, to no avail. However, he did say that he discovered that a file from 5 weeks ago seemed to work. But that would mean trying to recreate over a month of data entries and calculations. Not even sure I could do that... but I know for sure I don't want to.
Everything else seems to work OK. I can copy/paste rows and columns at the end of current data, but cannot insert and/or move rows columns to within the existing data. It seem so to have affected all sheets in a given workbook, even though I have not used them in months. And, if I create an new sheet to test things, inserting or moving does not work there either.
Here's another tidbit of information that just noticed. Even with "Workbook Calculation" set to automatic, not all of the cell formulas are being updated, especially those that point to cells on other sheets. Even forcing a "Calculate Now" doesn't update it. I have to go into a cell as if I am going to Edit it, and when I come out of edit mode, the formula updates.
Because this is affecting previously functioning workbooks, I feel that there MUST be some sort of bug in Excel that needs to be fixed. Would greatly appreciate it if someone did get a response from Microsoft. This is a real problem for me.
Having the same issue with my company files too. Just started randomly in the past week. We have a lot of ODBC files pulling data automatically into open excel files, with this issue the updated data does not update the table causing confusion with our users.
A fix we found was changing the external data properties from "Insert cells for new data, delete unused cells" to "insert entire rows for new data, clear unused cells". again, some of these files have been fine for years so was a bit of a guess and not sure if it will affect the files going forward.
Did you ever find a real permanent fix to your issue? I just posted a reply to the original post as I am experiencing the same problem all of a sudden.
- I'm having the same issue at my company since that update that was pushed this week
This on top of some files taking forever to update. Microsoft needs to fix this, just created a bunch of problems for a new ui look. Not worth it Hi all. I may have found out a way around this problem. If you change the workbook calculations to manual, it allows you to insert cells and the file does not mess with the formulas. Not sure for how long it will work, but at least you are allow to edit the models while somebody (hopefully) fix the issue
I have exactly the same issue on one complex XLS. Been happening on and off for a couple of months, and none of the fixes work. Even if I create a new blank tab it still doesn't let me insert rows or columns.
Just installed the latest office updates, but no joy.
Be interested to hear if anyone ever finds a fix!
Did you ever find a real permanent fix to your issue? I just posted a reply to the original post as I am experiencing the same problem all of a sudden. Please excuse that I'm pinging everyone who posted here to see if anyone came to any sort of permanent fix.
- Myself and others in our organization have the same issue since being forced on new excel 365 version. Working on large financial models and it is a significant problem. This needs immediate fixing.
Did you ever find a real permanent fix to your issue? I just posted a reply to the original post as I am experiencing the same problem all of a sudden. Please excuse that I'm pinging everyone who posted here to see if anyone came to any sort of permanent fix.
Same here, I have a tons of problem. I don't know if it's the new monthly version that are causing them but I am working on a file and it's hellish !
Inserting rows works like once and then I am unable to insert rows (or move cells) in any workbook. Even an empty one. I am forced to close all my files and I have to try my luck when I am opening them back if I will be able to do what I need to do.
It's been some time (like one month or two max) when inserting copied rows was sometimes not working first time and I had to do it again to have Excel actually do it. But since today I can barely work on one file which requires me to insert rows to update some info.
I hope it will be quickly fixed.... 😕
Did you ever find a real permanent fix to your issue? I just posted a reply to the original post as I am experiencing the same problem all of a sudden. Please excuse that I'm pinging everyone who posted here to see if anyone came to any sort of permanent fix.
- I have the same problem. I have been inserting columns in my securities worksheet for years, but now I can't anymore. I can neither insert or delete columns, and when I try copy and insert (which I used to do at the end of each month) Excel crashes. Using Office Pro 2019, Windows 10.
This has only started to occur when working with existing tables. I try to insert a row, but the only choice I am given is "Insert copied cell". Is there any way to insert a new blank row?
- Thanks for your answer. I am using Microsoft 365 MSO on a Windows 10.
