When working with spreadsheets, it’s important to identify where formulas have been replaced with hard-coded numbers (typed in number). This can help you ensure the accuracy and integrity of your data. Instead of manually checking each cell, Excel provides a quick method to locate these changes.
Table of contents
YouTube: Find Overwritten Formulas in Excel
Identifying Constants
As per the below image, start by selecting the range of cells you want to analyze. Then, navigate to the Home tab, choose Find and Select, and click on Go To Special. In the options that appear, select Constants. Here, you can choose whether you want to see numbers, text, logical values, or errors. If you’re only interested in numerical constants, uncheck the other options and click OK.
The cells containing numbers will be highlighted. To help you easily identify these cells, we change the highlighted colour. Below we’ve changed the colour to blue, so all blue cells we know are numbers only. Note the blue cell at the bottom (indicates a problem already).
Locating Formulas
Using the same data as above, we want to now identify where formulas are used. Repeat the process as shown below: Home, Find and Select, and Go To Special but this time, select Formulas. By choosing all the available options, you can see any cell with a formula (i.e., anything that starts with an equal sign).
Once again, apply a different color to these cells to distinguish them from the constants. Below we have changed the highlighted colour to yellow.
Spotting Overwritten Formulas
With your constants and formulas now color-coded, you can easily spot any discrepancies. For instance encircled below, if a cell that should contain a formula has been replaced with a hard-coded number, it will stand out due to the different colour you applied. This makes it simple to identify where formulas have been overwritten and take the necessary corrective actions.
Tips and Troubleshooting
- Double-check the Highlighting: If the colors you’ve applied to constants and formulas don’t seem to match up, make sure you selected the correct options in the “Go To Special” dialog box.
- Formula not found but you can see them: Make sure you changed the highlighted cells before you do the GOTO Special. If more than 1 cell is highlighted it only runs on those cells.
- Use Conditional Formatting: For larger datasets, consider using conditional formatting to automatically highlight cells with formulas or constants, making it easier to spot discrepancies without manually applying colors.