DONT'T CHANGE MY FORMULA!!!!!!!
How about keeping the formula intact, returning a #REF result and NOT editing the formula with a #REF comment. This stupid idea prevents one from following the formula back and evaluating what the error is, or what cell it's referencing. Who ever thought of doing this should have some training! If one receives a spreadsheet developed by someone else, they may not know how the formulas work/reference, but leaving the formula intact gives the opportunity to understand what the result should be. CHANGING the formula should NEVER be an automatic option by the software!!!!!
I think it is ridiculous that these vague errors (such as "#REF) are given in the first place!!!! Tell me exactly what is wrong and why after I make a change. Also, many of these errors are because Excel formulas continue to get f***** up when there is blank or 0 values. Like seriously. Why the **** does MS make it so ridiculously difficult to know what is not working and/or make it so difficult to work with your existing data without having to creat multiple additional data columns to placate what the Excel formulas don't like (e.g. zero value cells)??????
Sorry for my rant. I'm aware that all of it's not exactly applicable to this feedback suggestion, but I can't help but feel them as intrinsic.
Many, many, many sighs.
Scott McGee commented
I came here to suggest something related to #REF! errors. I changed a name or two in PowerPivot and updated some SQL calls in the PowerPivot sources and updated, which is what I think caused the #REF! errors in my XLOOKUPs to appear. But I really don't know exactly what caused the errors, because I've done several hours of work on my data model and it could be lots of different things.
I would prefer to be notified about the error differently and agree - replacing the formula with "#REF!" is horrific! My thought was to just put a red squiggly though the invalid reference. I also liked someone's idea to give warning about creating #REF! errors...
Regardless - you can't replace the invalid reference! Thank goodness I had the "always create backup" option turned on, so I can figure out how these multiple heavily nested XLOOKUPs with the #REF! errors worked. Because looking at the formulas with the #REF! errors, I probably would have just started over and taken the several hours it took to create the formulas, rather than try to guess what broke...
Kevin Osborn commented
@Harlan Grove ... you have a good point with respect to your example where one formula refers to 2 cells and you cut one cell and paste into the other.
I run into this problem all the time as it relates to tables. It's not related to cut/paste but frequently a column or whole table is deleted. Frequently it's related to Power Query where the PQ changes and thus the output table changes. Here is what happens and why this is so frustrating.
A1 = XLOOKUP("ABC",Table1[Column1],Table1[Column2]). Column 2 disappears for some reason (see above) and Excel now shows A1 = XLOOKUP("ABC",Table1[Column1],#REF!). So it is impossible to look at A1 and know what was intended in the formula. If the formula was left as it was originally and the result was #REF then it would be easy to find the problem and fix it.
I would be ok if Excel provided a warning IFF (if and ONLY if) the warning was not generic and actually told the user what cells and formulas were going to be affected and the message could be copied to the clipboard or provided in a new sheet (e.g. Check Compatibility > Copy to New Sheet). There are too many cases (e.g. Links, Pivot Tables) where Excel provides a message (e.g. broken links) but does not provide a way to find what was broken.
Harlan Grove commented
If your formulas in cells X99 and Y100 were =A5 and =B6, respectively, and you CUT cell B6 and pasted it into cell A5, you'd want both the X99 and Y100 formulas to be =A5? If Y100 were returning the correct value of the former B6 but now A5 but cell X99 returns #REF!. How would you be able to diagnose the problem in cell X99? If you made X99 the active cell, pressed [F2] to enter Edit mode, then pressed [Enter] to reenter it without changing anything, would you expect it still to return #REF! or the new value in A5?
Likewise for deleting row 5 or column A.
In any of these actions, you're DESTROYING a range to which your formulas refer. I'd argue it's better to have Excel replace the A5 reference with #REF!. It'd be better still if Excel displayed a confirmation dialog stating that you're about to destroy a range to which other cells refer and prompting you whether you really want to do that. However, if you did perform the action, Excel should alter the formulas. Maybe another approach would be Excel adding the original formula as a cell comment. However, the formula itself should be changed to reflect the fact that you've destroyed a formerly valid reference.