Find Inconsistent Formulas in Excel
Updated 2026-06-03
Quick answer
Excel flags an inconsistent formula with a small green triangle and a warning when a cell's formula differs from the pattern of its neighbours (e.g. one cell in a row sums a different range). Review them via the warning's Error Checking options. To audit an entire model for inconsistencies at once, Formula Audit XL colours every cell by formula pattern so broken rows/columns stand out instantly.
An inconsistent formula is a cell whose formula differs from the pattern established by its neighbouring cells in the same row or column. Excel detects this automatically and marks the cell with a small green triangle. That is its way of saying: this formula looks different from those around it, is that intentional? In a financial model where rows and columns should follow consistent patterns, an inconsistent formula is often the cause of a wrong number.
What “inconsistent formula” means
Consider a revenue projection table where each column represents a year:
| 2023 | 2024 | 2025 | 2026 | |
|---|---|---|---|---|
| Revenue | =C3*1.1 | =D3*1.1 | =D3*1.1 | =F3*1.1 |
Column E (2025) references D3 (the prior column), which is correct. But column F (2026) also references D3 instead of E3. That is an inconsistency: it uses the 2024 value as its base instead of the 2025 value. The formula returns a number without error, but the number is wrong.
Excel detects this because it notices that columns C, D, and F follow the pattern =[prior column]*1.1, but column E breaks the pattern by referencing a non-adjacent column.
The green-triangle warning
When Excel detects an inconsistency, it places a small green triangle in the top-left corner of the cell. To investigate:
- Click the cell with the green triangle.
- A yellow diamond warning icon appears to the left of the cell (in the margin).
- Click the diamond to open a menu with these options:
- Help on this error: opens Excel’s documentation.
- Copy Formula from Above (or Copy Formula from Left): replaces the formula with the version from the adjacent cell that establishes the pattern.
- Ignore Error: dismisses the warning for this cell (the formula is unchanged).
- Edit in Formula Bar: lets you fix the formula manually.
- Error Checking Options: opens global settings for inconsistency detection.
Fixing vs dismissing the warning
Before dismissing any inconsistent formula warning, read the formula. Not every inconsistency is an error. Some are correct by design:
- A total row that sums a range (
=SUM(B5:B10)) will look different from the detail rows above it (=B5*C5). This is correct. - A base year column with hardcoded historical values differs from forecast columns with formulas. That is intentional.
- A specific period with a different business rule (e.g. Q4 includes a one-time charge) may legitimately use a different formula.
If the inconsistency is intentional, click Ignore Error. If it is a mistake, fix the formula.
Why one inconsistent cell breaks a model
In a rolling model, one cell with a wrong reference propagates the error downstream. The 2026 column using 2024 as its base (instead of 2025) means all downstream calculations (growth rates, totals, ratios) are wrong for 2026 and all subsequent years. The output looks like a number. Reports are produced. Decisions are made. The error may not surface until a cross-check against actual results.
This is why inconsistency detection matters in the audit process, not just at the time of building.
Running Error Checking for inconsistent formulas
For a systematic review of a sheet:
- Go to Formulas → Error Checking.
- Excel cycles through all flagged issues, including inconsistent formulas.
- At each cell, review the formula and choose to fix or ignore.
Error Checking processes one sheet at a time. For a multi-sheet model, navigate to each sheet and repeat.
Configuring what gets flagged: File → Options → Formulas → Error Checking Rules. The relevant option is “Formulas inconsistent with other formulas in the region”: keep this enabled.
Whole-model consistency check with Formula Audit XL
Formula Audit XL‘s consistency colour view temporarily colours every cell in the workbook by formula pattern. Cells sharing the same formula structure get the same colour; those that deviate stand out in a contrasting colour. You see the entire model’s consistency at a glance, and cells breaking the pattern are immediately visible without reading any formula text, regardless of how many sheets the model spans.
Common pitfalls
- Ignoring all warnings without reading. If you click Ignore Error reflexively to clear green triangles, you will dismiss real errors along with the intentional inconsistencies.
- Copying the wrong direction. “Copy Formula from Above” replaces your formula with the one from the cell above. If the cell above is also wrong, you copy the error. Always verify the formula you are copying from before clicking.
- Disabling the warning globally. Turning off the inconsistent formula warning in File → Options removes a useful safety net, especially in models edited by multiple people.
- Inconsistencies in non-adjacent columns. Excel’s detection is heuristic. Some inconsistencies (particularly those in isolated columns far from any similar formula) may not trigger a green triangle. Show Formulas (
Ctrl + `` ``) and manual review remain necessary for a thorough audit.
Related guides
Frequently asked questions
What does the green triangle in an Excel cell mean?
The green triangle indicates that Excel has detected a potential error, most commonly that the cell's formula differs from the pattern of its neighbours (an inconsistent formula). Click the cell and then the yellow warning diamond to see options: edit the formula, copy from adjacent cells, or ignore the warning.
How do I get rid of the green triangle in Excel?
Click the cell with the green triangle, then click the yellow warning diamond that appears to the left. Choose 'Ignore Error' to dismiss the warning without changing the formula, or choose 'Copy Formula from Above/Left' to replace the formula with the consistent version from the adjacent cell.
Can I turn off the inconsistent formula warning globally?
Yes: go to File → Options → Formulas → Error Checking Rules and uncheck 'Formulas inconsistent with other formulas in the region'. This disables the warning for all inconsistent formulas in the workbook, which is not recommended for shared financial models.
Why would an inconsistent formula be correct?
Some inconsistencies are intentional. A total row that sums a different range, a base year with different assumptions, or a formula that deliberately applies a different logic to a specific period may legitimately look different from its neighbours. Always read the formula before dismissing or copying over it.
How do I find all inconsistent formulas across a large workbook?
Error Checking handles inconsistent formulas one sheet at a time. For a multi-sheet model, use Formulas → Error Checking and navigate through each sheet. To see all inconsistencies simultaneously, a dedicated tool like the consistency colour-check in an audit add-in is more efficient.