It’s a good idea to check if there are actually formulas in all the cells where you expect them to be. It happens all the time that someone accidentally has overwritten a formula with a hard-coded value, and it can be difficult to spot errors like that. Fortunately, there is a very easy way to locate all the formulas in an Excel report: The Go To Special feature.
This is what a sales report might look like. We expect to find formulas in some of the columns, but it’s almost impossible to go through the report manually cell by cell:
To search for the formulas in this Excel report, press Ctrl+G to open the Go To dialog box and click on Special, or go to the Home tab, click on Find & Select and Go To Special.
Click on Formulas (Alt+F) and OK (Enter)
All the cells that contain a formula will be selected, and it’s easy to spot which ones you need to fix.
More easy tricks in Excel:
- How to create a Refresh All button in Excel
- Unhide all sheets in Excel with a simple macro that works for all your documents
- How to get Week Numbers right in Excel using ISOWEEKNUM
- How to Join Text from Several Cells in Excel using TEXTJOIN
Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:
Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish
