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 Catalan, Czech, Danish, Dutch, Finnish, French, Galician, German, Hungarian, Italian, Norwegian, Polish, Portuguese, Russian, Spanish, Swedish and Turkish.
