Formula Errors

Fix Excel Conditional Formatting Failing on Every Sixth Row

Elise Williams Oct 22, 2026 868 views

Question details

Users need to resolve an issue where conditional formatting works for most of a selected range but unexpectedly fails on specific rows, such as the sixth row.

How to Fix Excel Conditional Formatting Failing on Every Sixth Row

Product: Excel for Microsoft 365
Device & OS: not provided
Scenario: Applying a formula-based conditional formatting rule across a large dataset to highlight specific rows.
Observed behavior: The formatting skips or incorrectly formats the sixth row of the selected range because of an incorrect relative formula reference in the rule.

Before you start

Before modifying your formula, check the Conditional Formatting Rules Manager to verify the exact 'Applies to' range, as mismatched ranges and formulas often cause row-skipping errors.

Solution 1Recommended

Correct the Formula with Absolute References

Changing relative references to absolute references prevents the rule from shifting incorrectly across rows during evaluation.

When using a formula to determine which cells to format, Excel evaluates that formula for every cell in the 'Applies to' range relative to the active cell. If your reference isn't properly locked with dollar signs ($), the formula will shift as it moves down the rows, causing unexpected failures on specific rows like the sixth row.

Open the Conditional Formatting Rules Manager

Select the range of cells where the formatting is applied. Go to the Home tab on the ribbon, click Conditional Formatting, and select Manage Rules.

Edit the failing rule

Locate the rule that is failing on the sixth row, select it, and click Edit Rule.

Update the formula references

Modify your formula to use absolute references for the target criteria cell. For example, change =Storage!AR91="Yes" to =Storage!$AR$91="Yes". The dollar signs lock the reference so it stays constant for every row in the range.

Apply changes

Click OK to close the Edit Rule dialog, then click Apply in the Rules Manager to instantly update the formatting across your dataset.

Correct the Formula with Absolute References

Pro Tip for Row Highlighting: If you want to highlight an entire row based on a single column's value, lock only the column reference (e.g., =$A1="Yes"). This allows the row number to change while keeping the evaluation fixed on column A.

Solution 2

Realign the 'Applies to' Range with the Formula

Ensuring the first row in your selected range matches the first row in your formula prevents offset errors.

Advanced Spreadsheet Software

Manage Conditional Formatting Easily in WPS Spreadsheet

WPS Spreadsheet provides a robust and intuitive Conditional Formatting Rules Manager, allowing you to highlight data, apply complex formulas, and fix reference errors seamlessly with no hassle.

1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel file.
2. Access conditional formatting: Highlight your data range, go to the Home tab, and click Conditional Formatting.
3. Manage your rules: Select Manage Rules to view all applied rules. Double-click the failing rule to edit its formula.
4. Lock your references: Ensure your target cells are locked with absolute references (using $ signs), then click OK to apply the changes perfectly across your dataset.

100% compatible with Microsoft Excel (.xlsx) formats and formulasIntuitive Rules Manager to easily troubleshoot and edit conditional formattingLightweight, fast, and completely free to download and useFamiliar interface ensures a seamless transition with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does conditional formatting shift to the wrong row in my spreadsheet?

This happens when your formula uses relative references (like A1) instead of absolute references (like $A$1). As Excel evaluates the rule down the selected range, the relative reference shifts, causing the formatting to skip or evaluate the wrong cells. Locking the reference with dollar signs fixes this.

How do I quickly lock a cell reference while editing a rule?

While editing your formula inside the Conditional Formatting Rules Manager, place your cursor on the cell reference and press the F4 key on your keyboard. This will automatically toggle the dollar signs (e.g., from A1 to $A$1).

Can I use formulas from another worksheet in conditional formatting?

Yes, but you must correctly reference the sheet name followed by an exclamation mark and use absolute references (e.g., =Storage!$AR$91="Yes"). Ensure the referenced worksheet actually exists and is spelled correctly.

Why is my conditional formatting applied one row below where it should be?

This offset error occurs when the starting row of your 'Applies to' range doesn't match the row number written in your formula. For example, if your range starts at A2 but your formula is =$B1="Done", every highlighted row will be offset by one. Correct the formula to =$B2="Done" to fix it.

Excel & Data Problems