When you inherit a massive financial model or budget spreadsheet from a coworker, the biggest danger is hidden “hardcoded” numbers. A spreadsheet is supposed to be a dynamic calculator. If cell C5 is supposed to be “Revenue minus Expenses” (=A5-B5), but your coworker manually typed the number 500 into the cell because they were in a rush, the entire mathematical integrity of the spreadsheet is broken.
If you change the Revenue in A5, the Profit in C5 will not update, because it is no longer a formula. Finding these rogue, hardcoded numbers manually in a sheet with 10,000 cells is impossible.
Instead of clicking on every single cell to check the formula bar, you can use the incredibly powerful ISFORMULA function combined with Conditional Formatting. This will command Google Sheets to instantly highlight every single cell in bright red if someone committed the cardinal sin of typing a raw number over a mathematical equation.
Step 1: Highlight the Target Area
- Open your Google Sheet.
- Highlight the entire range of cells where formulas should be (e.g., Column C, or range C2:F1000).
Step 2: Create the Conditional Formatting Rule
- Look at the top menu bar and click Format > Conditional formatting.
- A sidebar will slide open on the right.
- Under the “Format rules” section, look for the Format cells if… dropdown menu.
- Click the dropdown, scroll all the way to the very bottom, and select Custom formula is.
Step 3: Inject the ISFORMULA Logic
You need to tell the engine to format the cell if it is not a formula.
- In the text box that appears below the dropdown, paste the following exact formula (assuming your highlighted range started at C2):
=AND(NOT(ISBLANK(C2)), NOT(ISFORMULA(C2))) - Under “Formatting style,” click the Paint Bucket icon and select a harsh, bright red color.
- Click Done.
Understanding the Mathematical Trap
ISFORMULA(C2): This checks if the cell contains a mathematical equation (starting with=).NOT(ISFORMULA...): This reverses the logic. It triggers the red alarm if the cell is just raw text or a raw number.NOT(ISBLANK...): This is critical. Without this, every single empty, blank cell in your spreadsheet would also turn red. TheANDstatement ensures the alarm only goes off if a cell actually has data in it, but that data isn’t a formula.
The Result
The instant you click Done, your spreadsheet will light up like a Christmas tree. Every single cell that should be a calculation, but was manually overwritten by a human being, will instantly turn bright red. You can now easily locate the broken links in the chain, delete the hardcoded numbers, and restore the mathematical integrity of the financial model.