After some 15 years using Excel, I’m not sure how I’ve never seen this issue until now (or did I just not notice 😱 ?). In this post, I’ll quickly cover what it is and how to fix it.
Firstly, it’s not a bug. It’s a feature built into Excel from the beginning, or near beginning. It’s present on both Windows and Mac.
When entering data into one range of cells, Excel will OVERWRIGHT A FORMULA YOU’VE ENTERED in another cell.
That’s correct- there are situations where, if you delete data from a range of cells that are used as input to a formula elsewhere (e.g. you’re summing over a range), and then re-input them, then that SUM function might get overwritten by Excel.
Here’s a video demonstrating the issue.
Thankfully, you can stop it happening by unchecking “extend data range formats and formulas”. Thanks to John MacKintosh and Deborah Dalgleish.
Go to Open Excel > File > Options > Advanced > Uncheck Extend data range formats and formulas.
Here’s a very old post (dated 2009!) that explains the whole thing a bit more.
I’m still to see what turning this setting off will mean for my other workflows but in any case, I wonder how many errors have accrued and will accrue worldwide with this feature. I’m not exaggerating when I say Excel broke my heart with this one :(. I’ll need to go and edit my previous post Why I Love Excel, just to add a little caveat.
Keep up to date with new data posts and Big Book of R updates by signing up to my newsletter. Subscribers get a free copy of Project Management Fundamentals for Data Analysts worth $12.
Once you’ve subscribed, you’ll get a follow up email with a link to your free copy.