Formula Errors

How to Fix the No Cells Found Error in Excel Conditional Formatting

Elise Williams Oct 13, 2026 869 views

Question details

The user encounters a 'No cells found' error when applying a custom conditional formatting formula.

How to Fix the No Cells Found Error in Excel Conditional Formatting

Product: Excel
Device & OS: not provided
Scenario: Setting up custom conditional formatting rules based on a specific formula to highlight a selected data range.
Observed behavior: Excel displays a 'No cells found' warning prompt and refuses to apply the conditional formatting rule because the formula reference does not correctly match the selected range.

Before you start

Verify the exact cell address where your highlighted data range begins, as your custom formula must perfectly reference this top-left starting cell.

Solution 1Recommended

Match the Formula Reference to the First Cell of the Range

This is the most common fix. The error triggers when the formula's cell reference does not match the active first cell in the selected range.

When you highlight a range of cells to apply conditional formatting, Excel evaluates the custom formula starting from the active cell (the first cell you clicked). If your formula references a cell outside this initial selection, Excel cannot map the formatting properly and returns the 'No cells found' error.

Select your target range

Highlight the entire block of cells where you want the conditional formatting to apply (for example, select cells from A2 down to A100).

Identify the active starting cell

Note the exact cell address of the very first cell in your highlighted selection. If you started selecting from A2, your active cell is A2.

Open the Conditional Formatting menu

Navigate to the Home tab on the ribbon, click 'Conditional Formatting', then select 'New Rule'.

Enter the formula

Choose 'Use a formula to determine which cells to format'. Type your formula using the active starting cell reference. For example, enter =$A2>=0.8.

Apply and save the rule

Click the 'Format' button to set your desired highlighting colors, then click 'OK' twice to save the rule without encountering the error.

Match the Formula Reference to the First Cell of the Range

Relative vs Absolute References: Ensure you are using the correct dollar signs ($). If you want Excel to evaluate each row independently, lock the column but leave the row relative (e.g., $A2). Do not lock the row as $A$2 unless you want every cell to look only at row 2.

Solution 2

Correct Invalid or Missing Formula References

Fix conditional formatting rules that have broken due to deleted rows, columns, or referenced worksheets.

Advanced Data Formatting

Easily Manage Conditional Formatting Rules in WPS Office

If you frequently work with complex data, WPS Spreadsheet offers an intuitive conditional formatting rules manager. It easily processes custom formulas and avoids confusing reference mismatch errors, allowing you to highlight critical data efficiently.

1. Select the target data range: Open your workbook in WPS Spreadsheet and highlight the cells you want to format, noting the top-left cell.
2. Access Conditional Formatting: Navigate to the Home tab and click on the 'Conditional Formatting' icon in the main toolbar.
3. Create a new formula rule: Select 'New Rule' from the drop-down menu and choose 'Use a formula to determine which cells to format'.
4. Input your condition: Type your custom formula starting with the exact first cell of your highlighted range (e.g., =$A2>=0.8).
5. Set format and apply: Click 'Format' to choose your highlight colors or fonts, then click 'OK' to instantly apply the rule to your data.

Fully compatible with Microsoft Excel conditional formatting rules and custom formulas.Clear, easy-to-navigate Conditional Formatting Rules Manager to prevent reference errors.Free, lightweight, and professional spreadsheet tool available for Windows, Mac, and Linux.Familiar user interface ensuring zero learning curve when migrating from other office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting formula apply to the wrong rows in Excel?

This usually happens when your formula's starting cell reference doesn't match the first cell of the range in the 'Applies to' box, or if you locked the row number with a dollar sign (e.g., $A$2 instead of $A2), causing the formula to evaluate the exact same cell for the entire range.

Can I reference a cell on another worksheet in a conditional formatting rule?

Yes. You can reference another sheet by including the sheet name followed by an exclamation mark (e.g., =Sheet2!$A$1>10). However, be careful that the referenced sheet isn't deleted later, or it will cause an error.

What exactly does the 'No cells found' error mean in Excel?

This error simply indicates that the conditional formatting feature cannot map your formula to the selected data range. It occurs most often because the formula uses a cell reference that is outside the highlighted area or is completely invalid.

Excel & Data Problems