Excel Error- Cell referencing changing when inserting a row or cut and pasting

Question

I have been dealing with an issue with cell referencing for the past week. When I try to add a new row in excel, excel will not add the row, but instead will shift the referencing of in sheet references of all cells below it by the number of rows added.

For example, if I try to add 3 rows, excel will not add the rows, but say a cell references B20 in the formula, it will now reference B23. BUT since the rows were not added, the formula is completely changed and the right numbers are not pulling through. I have also found that I cannot use ctrl+z to fix the error, and the entire sheet will just be ruined and it is an extremely manual process to shift all the formulas back. I have also found that it is only some cells that have erroneously shifted in sheet referencing, and others are fine. I have never seen this happen before and it is significantly hindering my ability to work on models I am supposed to be building. I have also noticed this happens when I cut and paste cells to different parts of my document.

This is clearly a glitch in excel. I have tried doing an online repair of Microsoft office products on my laptop as well as uninstalling and re-installing all Microsoft products, neither has worked. Has anyone had this issue before and know a fix? Thanks in advance for your help.

Answer 1

Hi

I have just done the update but this issue persits. Will there be another patch released soon to solve it as this is becoming very tricky to use Excel?

Many thanks,

Loic

Answer 2

New version comes.

Release notes for Current Channel releases - Office release notes | Microsoft Learn

Excel

• This fix rectifies a problem in Excel where inserting or deleting cells might occasionally fail, leading to formulas recalculating inaccurately.

Answer 3

You are welcome:)

Answer 4

That worked! Thank you so much!

Answer 5

It seems bug on current version. You may try rolling back office update as workaround.

ODT tool

How to revert to an earlier version of Office - Microsoft Support

Or CMD

1. Start an Office application (such as Excel), and then select File > Account. select Update Options > Disable Updates.
2. Type cmd on the start menu, right click on the Command Prompt, choose Run as Administrator
3. Copy/paste these two commands one at a time and press enter

cd %programfiles%\Common Files\Microsoft Shared\ClickToRun

officec2rclient.exe /update user updatetoversion=16.0.17531.20152

Here is version history. Change the 16.0.17531.20152 to the version number based on your channel.

Update history for Microsoft 365 Apps (listed by date) - Office release notes | Microsoft Learn

I'd suggest you create a support ticket to Microsoft. Hope Microsoft will fix it soon.

Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. Support team there will have the correct channel and resources to help you investigate more and find what exactly the reason has caused this situation.

Get support - Microsoft 365 admin | Microsoft Learn

About how to find the admin in your organization, you can refer to How do I find my Office 365 admin