Calculation Issues

How to Find Excel Values When Accounting Formatting Adds Commas

WPS Content Manager Sep 30, 2026 868 views

Question details

The user needs to locate specific numeric values in a spreadsheet, but the standard Find tool fails because the cells use Accounting formatting (which adds comma separators) and contain XLOOKUP formulas.

How to Find Excel Values When Accounting Formatting Adds Commas

Product: Excel
Device & OS: not provided
Scenario: Trying to search for a specific calculated result (e.g., 1445) in cells that contain XLOOKUP formulas and are formatted with standard Accounting formats (displaying as 1,445).
Observed behavior: The Excel Find function does not match the raw unformatted search query with the displayed comma-separated value, nor does it successfully locate the value when searching within formula cells.

Before you start

Determine whether the cells you are trying to search contain static numbers or formulas, as this changes whether you should adjust your Find dialog settings or use a formatting workaround.

Solution 1Recommended

Adjust Find and Replace Settings to Look in Values

Modify the advanced settings of the Find tool to search by the displayed value rather than the underlying formula.

By default, Excel often searches within formulas. If a cell contains a formula but displays a formatted number, searching for the number might yield no results. Changing the search scope to 'Values' allows Excel to look at the final output.

Note that when searching in 'Values', you must type the number exactly as it appears on the screen, including the comma separator added by the Accounting format.

Open Find and Replace

Press Ctrl + F on your keyboard to open the Find and Replace dialog box.

Access Advanced Options

Click the 'Options >>' button to reveal advanced search parameters.

Change Look In to Values

In the 'Look in' dropdown menu, change the selection from 'Formulas' to 'Values'.

Search with Formatting

In the 'Find what' field, type the number exactly as it is formatted (e.g., type 1,445 instead of 1445) and click 'Find Next'.

Adjust Find and Replace Settings to Look in Values

Search Limitations: This method works well for static numbers or simple values but may still struggle with complex array outputs or dynamic formulas depending on your Excel version.

Solution 2

Use Conditional Formatting to Highlight Matches

Bypass the Find tool entirely by applying a conditional formatting rule that highlights cells containing the target numeric value, which works perfectly for formula-driven cells.

Efficient Data Management with WPS

Easily Find and Format Data with WPS Spreadsheet

WPS Spreadsheet provides robust Find and Replace features and intuitive Conditional Formatting, making it easy to locate calculated values and handle complex accounting formats without the hassle.

1. Open Your Workbook: Launch WPS Spreadsheet and open your existing spreadsheet containing the formatted formulas.
2. Use Advanced Find: Press Ctrl + F, click 'Options', and set 'Look in' to 'Values' to search for displayed numbers including their commas.
3. Highlight Results Quickly: Alternatively, use the Conditional Formatting tool located in the Home tab to instantly highlight cells matching your exact calculated values.

Advanced Find and Replace options for values and formulasSeamlessly compatible with Microsoft Excel formats (.xlsx, .xls)Intuitive Conditional Formatting rules for fast data highlightingLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Excel Find work on formatted numbers by default?

By default, the Excel Find tool's 'Look in' setting is often set to 'Formulas'. This means it searches the underlying text of the formula or the raw unformatted data instead of the final comma-separated number you see on your screen.

Can I search for the raw number without typing the comma?

If you are searching within static cells (not formulas) and have 'Look in' set to 'Formulas', you can search for the raw number (e.g., 1445). However, if you are looking at formula results with 'Look in' set to 'Values', you typically must include the comma to match the displayed text.

Does wrapping my XLOOKUP in the VALUE() function help searchability?

No. Wrapping a formula in the VALUE() function only ensures the output is treated as a numeric data type. It does not change how the Find tool interacts with the formula text versus the displayed Accounting format.

Excel & Data Problems