Excel Formula References Changing When I Insert a Row

Copper Contributor

Hello,

 

I have multiple excel workbooks, where if I insert a row, it stuffs up my formula references (i.e. formulas start referencing the wrong cells) even though I haven't changed the formula. The rows are not even referenced by the formula indirectly (i.e. it is not part of an array in a SUMPRODUCT).

 

This never used to happen, and has only started occurring in the past week or so. 

 

Any help would be much appreciated.

9 Replies
You may try to use absolute reference of ranges for that formula like =SUMPRODUCT($A$1:$A$10,$B$1:$B$10)

I am not using a =SUMPRODUCT formula, even simple formulas like =A28 are stuffing up. It is as though Excel has corrupted because it is happening to a colleague too. @Harun24HR 

kinda have the same problem my formula is
=SI(C29="";"";RECHERCHEV(C29;Produit;2;FAUX))
and if i insert a row or move the data on c29 the formula change itself to
=SI(#REF!="";"";RECHERCHEV(#REF!;Produit;2;FAUX))
could it be the same as you ??? no clue what to do
I am also having this issue. I am noticing that I only have the issue when I rapidly insert rows (ctrl+ repeatedly) it happens

@mjwilly88 

 

This is exactly the issue I am having. And sometimes if I try to insert rows, excel simply won't insert them.

 

I have been using excel prolifically for the past 10 years and this is issue has only just started occurring in the past two weeks.

I'm also having the exact same problem after getting a new computer and installing office. I've used excel for decades and have never had this issue. Extremely frustrating. Everything I can find online just says to use $ to lock cell references but you shouldn't have to do this to keep formula references relative when inserting rows into a worksheet. Right now inserting a row messes up every formula above it in all of my worksheets. Did anyone figure out how to fix this issue? 

@Brendon_Kelly 

My co-workers and I are dealing with this exact issue. It's extremely frustrating and severely impacting workflow. @Brendon_Kelly 

Issue has been patched. For some reason I had to manually force the update. You can do this in excel by going to File -> Account (at the bottom) -> Update options -> Update Now.

 

https://learn.microsoft.com/en-us/officeupdates/current-channel#version-2405-june-19

 

@Brendon_Kelly 

@RPresearch 

Could you please give bit more details with which formulae you use. If not sample file when screenshot. Also - moving from one computer to another did you change Office or not, e.g. it was 2019 and now 365 or 2021; or that's exactly the same Office platform and version?