Excel: Spot Hardcoded Numbers Fast

Key insights

• Hardcoded numbers hide inside formulas and are hard to spot because the cell still contains a formula.
  Standard tools like Go To Special miss them since they only find constant cells, not numeric literals inside formulas.
• Practical detection combines FORMULATEXT to extract a formula as text,
  REGEXTEST to check that text for numeric literals, and conditional formatting to highlight offending cells automatically.
  No helper columns, VBA, or add-ins are required.
• Use two regex patterns: one strict and one with a longer exception list.
  Provide a dropdown to switch patterns so you can tune sensitivity for different files.
• Not every number is a problem: the 0 in ROUND, row/column indexes in INDEX, or dividing by 12 for monthly conversion are valid exceptions.
  Build these into your exception list so the pattern ignores intended numeric arguments.
• REGEXTEST is available only in Microsoft 365 and Excel for the web, so this exact approach won’t work in Excel 2024, 2021, 2019 or earlier.
  Plan alternatives if you run older versions.
• For faster tuning and review, use Copilot or an AI prompt to adapt regex rules to your model, and move assumptions into dedicated input cells or a named range to avoid future errors from hidden magic numbers.

Overview of the video and its purpose

In a practical video, Leila Gharani [MVP] demonstrates a method to find hardcoded numbers that hide inside Excel formulas and escape standard checks. She shows why tools like Go To Special miss these cases, because those tools only find cells that contain values, not formulas that include numeric literals. Accordingly, the video aims to highlight every risky formula automatically so reviewers can spot assumptions that should live in input cells instead.

Moreover, the tutorial focuses on an approach that avoids helper columns, macros, or third-party add-ins, making it accessible to users who prefer built-in Excel features. The technique relies on text extraction, pattern matching, and conditional formatting to mark formulas that contain suspicious numeric constants. Therefore, the method is both practical and immediately reusable in many workbooks.

How the detection method works

First, the workflow pulls the formula out of a cell using FORMULATEXT, which converts the formula into plain text you can inspect. Next, REGEXTEST compares that text against a pattern to detect numeric literals, and finally conditional formatting highlights any formula that matches the pattern so the problem becomes visible in the worksheet. Together these parts form an audit that flags formulas containing raw numbers rather than references to input cells.

Importantly, Leila presents two regex patterns: a strict one and a more permissive one with exceptions. She also adds a dropdown and a lookup mechanism so users can switch between patterns based on their rules. As a result, users can tune detection granularity without changing the underlying workbook structure.

Balancing detection and false positives

Leila underscores that not every number inside a formula is an error, and she demonstrates why simple pattern matching can produce false positives. For example, the 0 in ROUND specifies decimal places, INDEX uses row or column numbers as meaningful parameters, and dividing by 12 often converts annual figures to monthly values; these are legitimate uses rather than mistakes. Thus, the video explains why a detector must allow exceptions to avoid noisy results that slow down auditing.

Consequently, she offers two patterns and an exceptions list so teams can balance sensitivity against practicality. The tradeoff is clear: a strict pattern finds more raw numbers but raises more false alerts, while a longer exception list reduces alerts but risks missing unusual problems. Therefore, reviewers should pick the pattern that best matches their model standards and governance needs.

Practical limitations and technical requirements

The tutorial also highlights practical constraints, especially that REGEXTEST is available only in Microsoft 365 and Excel for the web, and is not present in older releases like Excel 2024, 2021, or 2019. This requirement means that teams using legacy Excel must adopt alternative approaches or upgrade to use the same in-sheet solution. Furthermore, very large workbooks or extremely long formulas may slow down pattern checks, so performance considerations matter in high-volume models.

In addition, the video explains that some patterns or scenarios are inherently hard to catch with regex alone, and that maintaining an exceptions list requires governance so that allowed constants remain justified. As a result, teams must weigh the ease of a no-code approach against the ongoing maintenance overhead of rules and exceptions.

AI options, extensions, and final recommendations

Leila closes by showing a Copilot version for users who have a license and demonstrates an AI prompt for tuning the pattern to an organization’s own rules. While AI can speed up pattern creation and spot context-sensitive cases, it introduces its own tradeoffs such as licensing, interpretability, and the need to validate AI suggestions manually. Therefore, AI should complement, not replace, a disciplined review process.

Overall, the video offers a clear, actionable way to reduce hidden risks in spreadsheets by making assumptions visible, and it encourages teams to adopt patterns and governance that fit their workflows. By combining FORMULATEXT, REGEXTEST, and conditional formatting, reviewers can improve model reliability, but they should also plan for exceptions, performance limits, and version compatibility. Consequently, the approach serves as a strong starting point for organizations aiming to make spreadsheets easier to audit and maintain.

Keywords

hardcoded numbers in Excel, find hardcoded values Excel, detect constants in formulas Excel, Excel formula auditing, remove hardcoded values Excel, spot hardcoded formulas Excel, dynamic formulas vs hardcoded Excel, audit spreadsheet for hardcoded numbers