I'm just playing a game but this has highlighted a potential problem in Excel I wasn't aware of. I thought absolute cell references ($C$4) meant the reference would NEVER change but I've found it changing when I don't want it to.
I have a few cells of data with an auto sum at the bottom of each column with absolute cell references in. I want to make changes to the data above and have the totals automatically update. I've moved the data to the right with insert (Ctrl+) and the cell refs have changed. Why? I haven't changed or moved the totals just the data. Via a few tests this only happens when I move all the cells referenced in one go. Is that the issue? If I move one or two rows only it doesn't happen and if I move two rows then the other two rows (i.e. all rows in two steps) it also doesn't happen. Also if I extend the sum to include a blank row above/below and just move the data in between it doesn't happen.
I've found a couple of workarounds but it would still be good to understand why an absolute cell reference isn't an absolute cell reference all the time.
In the picture the cells with the yellow highlight are the autosum that should update, the extra data either side of these columns is a copy so that I can just move the data left or right to line up the options without retyping every number.
31 Answer
Absolute referencing is not to protect references against the source or result cells being moved. In fact, it won't, unless only a portion of whichever range is moved rather than the whole range.
What absolute referencing is for is to protect formulas that are placed elsewhere. (Placed into cells that are not where they were originally written, for instance, writing one in cell A1, then copying it down into cells A2 through A40000, or things that effectively do that like using a non-absolute reference in a Named Range that is then referenced in other cells than the one (often carelessly) selected when the Named Range was created.)
To protect your references in the kind of case you are referencing (sorry, I like puns and anything close so...), you take a REALLY absolute referencing approach:
=SUM(INDIRECT("A1:A4"))Since the range reference ("A1:A4") is a trext string, Excel will not attempt to alter it for any column or row insertions, nor for copying the cell to other cells, nor drag-and-drop movements of either the cell or the range referenced. It is truly absolute for you purposes.
I might also mention that writing "INDIRECT("A1:A4") is not materially harder than writing "A1:A4", stopping, highlighting enough of it, pressing F4, then moving on, especially with the changes Excel has made to editing formulas that mean you usually have to reach over to the mouse to do that highlighting or risking the arrow keey movement will finish your edit, usually at an awkward moment that generates the "there is a problem..." path of frustration, so complaints I hear about it being so beastly to have to use INDIRECT() are pretty baseless!
Do note though, that doing it while writing the formula masks the ranges from Excel's usual (and worthwhile) error checking when pressing Enter so I suggest writing the bare ranges while writing the formula, pressing Enter to see if Excel accepts it, then editing the references you need to be really absolute. That actually makes it pretty easy too since you can write the first one's edit, then copy the INDIRECT(" start and for any others just paste that before the ranges and only type ") after each. So
=SUM(A1:A4) + SUM(B1:B4)goes like so:
=SUM(INDIRECT("A1:A4")) + SUM(B1:B4) (first one typed fully)
=SUM(INDIRECT("A1:A4")) + SUM(INDIRECT("B1:B4) {pasting first half of each edit ( INDIRECT(" }
=SUM(INDIRECT("A1:A4") + SUM(INDIRECT("B1:B4)") {typing the last half of each edit ( ") }
