Excel does a terrible, horrible, no-good thing

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.

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.

Back to Top