Look, I get how well intentioned this one was. It was intended to save you from errors. But since it weaves its way unannounced in the background, it can have a toxic effect and actually introduce errors.
Check out this table of values from our sister website Scuderia Quantistica:
Columns B to F contain the pit crew members’ skill stats in each field. Column G is their stamina level, Column H is their expected mistake rate, Column I is their cost.
Column J needs to add together all of their skill stats to come to a total. Just their skill stats. Not their mistake rate and cost per race, which would be kinda nuts since it would be adding apples to oranges and throwing in a banana for good measure. Thus we have the formula as the sum of columns B to F.
Just F.
No further than F.
This is consistent with all of the formulas above in that column.
Why is it arranged that way? B to I contain the raw data. The remaining columns are for analysis. It’s easier to add all of the raw data values in one block and do the analysis off to the side.
Watch what happens when I enter values on row 17:
“Thinking” that I must have “forgotten” to include columns G to I, Excel auto-adjusts the formula to include those columns. It matters not that the formatting in H and J mark them out as “something different to B to F”. It matters not that it makes the formula in J17 inconsistent with the formulas above. Oh no. It auto-adjusts the formula anyway which does kinda stand out because you get a nonsense value in it. But if the values in the added columns weren’t so wildly different to the ones that the formula was supposed to add, you may miss it.
There is of course a way of avoiding it by adding an extra blank column at column J to provide a firebreak between the data and the formulas. However that screws up your filtering ability since for that the data needs to be in a contiguous block.
The other way is to turn the feature off, which can be done by going to File -> Options -> Advanced and turn off Extend Data Range Formats And Formulas.
The only issue with that is that this feature can actually be useful. In my view it just needs a little tweaking to prevent it from changing a formula if doing so introduces an inconsistency with the formulas above.
Until or unless it’s fixed I’d just keep this one in mind, and add the formulas afterwards if they fall into this category.