# Troubleshooting Excel Formula Errors in OfficeCLI: A Complete Guide to Detection and Resolution

> Troubleshoot Excel formula errors in OfficeCLI with our guide. Learn to detect and resolve missing references, evaluation errors, unevaluated formulas, and stale cache values efficiently.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: how-to-guide
- Published: 2026-07-10

---

**OfficeCLI detects Excel formula errors by evaluating cells on-the-fly through the `ExcelHandler.ViewAsIssues` method and classifying issues into four specific types: missing sheet references, evaluation errors, unevaluated formulas, and stale cache values.**

OfficeCLI, maintained by the iOfficeAI/OfficeCLI repository, provides deep inspection capabilities for Excel workbooks through its command-line interface. When you need to troubleshoot Excel formula errors in OfficeCLI, the tool leverages a centralized `FormulaEvaluator` and validation helpers to scan every cell and report discrepancies between cached values and live calculations. Understanding this detection pipeline allows you to diagnose whether an error stems from missing references, unsupported functions, or outdated cache data.

## How OfficeCLI Detects Formula Errors

The error detection pipeline is centralized in `ExcelHandler.ViewAsIssues` (implemented at lines 570–660 of [`src/officecli/Handlers/Excel/ExcelHandler.View.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.View.cs)). This method orchestrates a multi-stage analysis that identifies formula problems without requiring Excel to be installed.

### Cell Enumeration and Workbook Traversal

The `ViewAsIssues` method first obtains a cached list of worksheets via `CachedWorksheets`, then iterates over every `<c:cell>` element in the workbook. For each cell containing a formula (`cell.CellFormula != null`), the handler initiates a validation sequence that compares the stored value against a freshly computed result.

### Formula Evaluation Engine

For every formula encountered, OfficeCLI creates a new `FormulaEvaluator` instance:

```csharp
new Core.FormulaEvaluator(sheetData, _doc.WorkbookPart)

```

This evaluator re-computes the formula on-the-fly using the [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs) expression engine. By recalculating the value independently of Excel’s cached results, OfficeCLI can detect when the disk cache no longer matches the actual computed value.

### Error Classification via IsExcelErrorValue

The helper method `IsExcelErrorValue`, located in [`src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs) (lines 31–46), determines whether a display value represents an Excel error. This method recognizes standard error sentinels including `#VALUE!`, `#DIV/0!`, and `#REF!`, while deliberately excluding the internal `#OCLI_NOTEVAL!` sentinel used for unevaluated formulas.

## The Four Formula Issue Types

Depending on the validation results, `ViewAsIssues` emits one of four specific `DocumentIssue` sub-types, each including the cell address (`Path = $"{sheetName}!{cellRef}"`), a human-readable message, and the original formula (`Context = $"={cell.CellFormula.Text}"`).

### formula_ref_missing_sheet

This issue triggers when the cached value contains `#REF!` and the original formula text references a sheet that no longer exists (detected via `FormulaReferencesMissingSheet`). This typically occurs after deleting or renaming worksheets that are referenced by formulas in other sheets.

### formula_eval_error

When the `FormulaEvaluator` reproduces an error sentinel such as `#DIV/0!` or `#VALUE!` during re-computation, OfficeCLI flags the cell with this type. The error indicates the formula contains invalid arithmetic, incompatible data types, or circular references that Excel itself would flag.

### formula_not_evaluated

This type appears when a cell contains a formula but lacks a cached value, and the evaluator cannot produce a result—often because OfficeCLI does not yet support that particular Excel function. These cells display the sentinel `#OCLI_NOTEVAL!` in text view output.

### formula_cache_stale

OfficeCLI flags this issue when a cached value exists but disagrees with the freshly evaluated result. This indicates the workbook’s on-disk cache is out-of-date, which can happen when files are modified by tools that don’t update Excel’s calculation cache.

## Fixing Common Formula Errors

Use the following diagnostic patterns to resolve specific error conditions detected by OfficeCLI.

### Resolving #REF! Errors

When `formula_ref_missing_sheet` appears, verify that the sheet name referenced in the formula actually exists in the workbook. Rename or recreate the missing sheet, or edit the formula to point to a valid sheet reference.

### Addressing Calculation Errors

For `formula_eval_error` classifications, open the workbook in Excel to see the exact error message. Fix the underlying arithmetic problem, division by zero, or invalid reference that causes the `#DIV/0!` or `#VALUE!` sentinel.

### Handling Unevaluated Formulas

When you encounter `formula_not_evaluated`, either open the file in Excel and let it recalculate to generate a cached value, or use the `officecli set` command to rewrite the formula, which forces a fresh cache write and eliminates the `#OCLI_NOTEVAL!` sentinel.

### Refreshing Stale Cache Values

For `formula_cache_stale` warnings, open the file in Excel and perform a full recalculation (Ctrl+Alt+F9), then save. Alternatively, run `officecli view excel ... --issues` after the recalculation, or explicitly use `set` commands to write the corrected values back to the workbook.

## CLI Commands for Diagnosing Formula Issues

Run these commands to inspect formula errors across your workbooks:

```bash

# Show all detected issues for an Excel workbook

officecli view excel my-workbook.xlsx --issues

# Limit output to specific error types

officecli view excel my-workbook.xlsx --issues --type formula_eval_error

# Export issues as JSON for programmatic analysis

officecli view excel my-workbook.xlsx --issues --format json > issues.json

```

To see the raw text view including the `#OCLI_NOTEVAL!` sentinel:

```bash
officecli view excel my-workbook.xlsx --text

```

This outputs cell addresses and values in the format:

```

[Sheet1/row[5]]  A5=#DIV/0!   B5=42   C5=foo
[Sheet2/row[12]]  D12=#OCLI_NOTEVAL!   → =SUM(A1:A10)

```

## Key Source Files and Implementation Details

Understanding the following source files helps when extending or debugging the formula detection logic:

- **[`src/officecli/Handlers/Excel/ExcelHandler.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.cs)** – Provides the `ExcelHandler` class that orchestrates all Excel operations and worksheet caching.
- **[`src/officecli/Handlers/Excel/ExcelHandler.View.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.View.cs)** – Implements `ViewAsIssues` (lines 570–660), `ViewAsStats`, and `ViewAsOutline`, containing the core formula-error logic reused across view commands.
- **[`src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs)** – Contains `IsExcelErrorValue` (lines 31–46) and validation utilities that classify error sentinels.
- **[`src/officecli/Core/FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/FormulaEvaluator.cs)** – The expression engine that performs on-the-fly evaluation of Excel formulas for issue detection and cache-stale checks.
- **`src/officecli/CommandBuilder.*.cs`** – Maps the `view` sub-command and flags (`--issues`, `--format`, `--type`) to the handler methods.

Because the error detection logic is centralized in `IsExcelErrorValue`, any new error codes added by Microsoft in future Excel releases will automatically be caught without requiring code changes to OfficeCLI.

## Summary

- **OfficeCLI** evaluates Excel formulas on-the-fly using `FormulaEvaluator` and reports errors through `ViewAsIssues`.
- Four specific issue types classify formula problems: `formula_ref_missing_sheet`, `formula_eval_error`, `formula_not_evaluated`, and `formula_cache_stale`.
- Use `--issues` to scan workbooks and `--type` to filter specific error categories.
- Fix `#OCLI_NOTEVAL!` sentinels by opening the file in Excel or using `set` commands to refresh the cache.
- The validation logic lives in [`ExcelHandler.Helpers.Validation.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Helpers.Validation.cs) and automatically adapts to new Excel error codes.

## Frequently Asked Questions

### How does OfficeCLI distinguish between a missing sheet reference and other #REF! errors?

OfficeCLI specifically checks for `formula_ref_missing_sheet` by analyzing whether the original formula text references a sheet that no longer exists in the workbook’s `CachedWorksheets`. If the `#REF!` error correlates with a missing sheet name in the formula, it categorizes it as a missing sheet reference rather than a general evaluation error.

### What does the #OCLI_NOTEVAL! sentinel mean in OfficeCLI output?

The `#OCLI_NOTEVAL!` sentinel indicates that OfficeCLI encountered a formula it cannot evaluate, either because the function is unsupported or because no cached value exists and the evaluator failed to compute a result. This appears in `formula_not_evaluated` issues and text view output, distinguishing unevaluated formulas from actual Excel errors like `#VALUE!`.

### Can OfficeCLI detect if Excel’s cached values are outdated?

Yes, OfficeCLI detects stale cache values through the `formula_cache_stale` issue type. When `ViewAsIssues` finds that the cached value in the file differs from the result computed by `FormulaEvaluator`, it reports the discrepancy, allowing you to identify workbooks that need recalculation before being processed.

### Which command should I use to export formula errors for automated processing?

Use `officecli view excel my-workbook.xlsx --issues --format json > issues.json` to generate a JSON dump of all detected issues. This output includes the cell address (`Path`), formula text (`Context`), and issue classification, making it ideal for programmatic analysis in CI/CD pipelines or automated reporting tools.