How to Find Cells Where the Formula Was Overwritten in Excel #shorts

It happens all the time that someone accidentally has overwritten a formula with a hard-coded value. It can take a long time to spot errors like that. Fortunately, there is a great feature in Microsoft Excel and I'll show you a super easy way to find these cells in Excel.

Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

[wayneedmondson]

Thanks Leila. Another quick way.. highlight column, then SHIFT+CTRL+\ will highlight the manual entries. Thanks for sharing! Thumbs up!!

[PAC]

What’s needed is an add in that sends electric shocks through the keyboard when someone overwrites formulas…

[GeertDelmulle]

Add sheet protection to the cells with formulas.
A couple of months ago I did exactly that (on a much more elaborate, high-profile Excel file), and people may complain that they can’t do what they “need”, but instead they had all to pass through me. The end result: a multi-year, multi MioEUR programme was funded successfully.
And believe you me: we ran tons of financing scenarios (all fully automated, of course :-).
Sometimes, you need a fairly high level of sophistication, and cell/formula-protection is definitely part of that. :-)

[lilianedecoste]

You can also show formulas instead of results. In my French version I press Ctrl+Shift+2 (I think in English version you have to press Ctrl+' (Or go in the Menu Formula tab, Show formulas)!

[jessequillen]

You could also see which cells had formulas vs hard coded with the shortcut CTRL+` . This is faster than the constants method.

[chh]

I've never even heard of 'number constants'. And did not know to check; " ... Ctrl-G, Alt- S - Constants ...".

Once again, thank you Leila ...

[masterchef]

these excels shorts are really helping

[luvlycan]

That's a good trick... I usually press Ctrl + tilt (~) key to check weather it's a formula or manual entry

[baphnie]

Show Formulas is my go to for this sort of thing.

[KhaledsWay]

More please. You're the best in the world. I cannot thank you enough for all that you've done to help us.

[JustaReadingguy]

You have added to my excel skills. And I am glad you see (discover ) you on these short videos.

[DKs_youniverse]

Wowwww so very crisp and no-nonsense video. Thanks again Leila!

[Kevin-]

Another option is [Ctrl]+ ` -- this toggles between formulas & values -- locking the cells mitigates these issues

[KgasS]

Thanks. There is also a short cut to high light the differences ctrl+\ . Another way is enter the sales figures as array formula. any change will cause spill error

[isaacmwabila]

Wow! What a great method to find where errors has been done ! Thanks a lot Leila!👏👏

[AcessDBpro]

A faster way to check is to press Ctl + ` to toggle between values view and formula view.

[GauravGLulla]

Alternatively, we can also select the range and use the function CTRL + "~" button on top left side of keyboard. It makes visible all formulas in selected range. 😀✅

[truthrevealer]

You know, I am learning excel from your videos. They are very well explained and easy to understand.
Brilliant.

[racr]

Have been using Excel for 20+ years. Did not know about this! Thank you!

[hulem]

Very useful for me. Now I can find out if the files uploaded from the stores have been manipulated