Eliminate redundant sheet references in formulae on that sheet
Why does Excel sometimes add a sheet reference prefix to a cell reference in a formula when the host cell containing the formula is on that same sheet? A bizarre and inconsistent practice that is a completely useless waste of formula space.
Ryan MacGregor commented
Completely agree. We actually build a tool that removes the extraneous sheet name: https://macabacus.com/docs/excel/formulas-data#clean-cells.
Additionally, this sheet reference causes problems if sorting a list that includes these references - as if Excel doesn't know how to deal with them and ends up scrambling the formulas.
Brett Ables commented
I've had this happen if I flip to another sheet, then switch back, any clicked cell will then generate a reference that includes the sheet name.