Formatting Issues

How to Highlight an Excel Row When One Value Exceeds Another

Nimra Malik Oct 9, 2026 870 views

Question details

The user wants to format a complete row in a spreadsheet so that it highlights automatically when a specific value in that row is larger than another value within the same row.

How to Highlight an Excel Row When One Value Exceeds Another

Product: Excel
Device & OS: not provided
Scenario: Comparing two columns of data (such as actuals vs. budget or current vs. previous sales) and visually flagging the entire row where the condition is met.
Observed behavior: The user needs to apply formatting across an entire row based on a cell-to-cell comparison, ensuring the condition properly evaluates each row individually without breaking.

Before you start

Ensure your data is organized in clear columns without merged cells, and note the exact row number where your data begins, as this is crucial for the conditional formatting formula to work correctly.

Solution 1Recommended

Use Conditional Formatting with a Custom Formula

By using a logical formula in the Conditional Formatting menu, you can lock the comparison columns while allowing the row numbers to adjust automatically down your dataset.

To highlight an entire row rather than a single cell, you must use absolute column references (with a dollar sign) and relative row references in your formula. This tells Excel to strictly compare specific columns but apply the logic row by row.

Select the data range

Highlight the entire dataset where you want the color applied. For example, select A2:F100. Do not include your column headers in this selection.

Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the comparison formula

Input your formula referencing the first row of your selection. For example, if comparing Column D to Column A starting at row 2, type: =$D2>$A2

Apply formatting

Click the 'Format' button, go to the 'Fill' tab to choose your desired highlight color, and click 'OK' twice to apply the rule.

Use Conditional Formatting with a Custom Formula

Understanding the Dollar Signs ($): Placing a $ before the column letter (e.g., $D2) locks the column so the formatting stretches across the whole row. Leaving the row number without a $ allows Excel to shift the comparison down for each subsequent row.

Easily Format Spreadsheets with WPS Office

Highlight Rows Dynamically in WPS Spreadsheet

WPS Spreadsheet provides powerful Conditional Formatting tools identical to Microsoft Excel. You can quickly set up custom formulas to highlight entire rows based on cell comparisons, making data analysis highly visual and intuitive.

1. Select your data: Open your workbook in WPS Spreadsheet and highlight the range of cells you wish to format (excluding headers).
2. Access New Rule: Go to the 'Home' tab, click 'Conditional Formatting', and choose 'New Rule'.
3. Input the formula: Select the formula option and type your comparison, such as =$D2>$A2, ensuring you use the correct starting row.
4. Set the highlight color: Click 'Format', pick your preferred background color, and click 'OK' to instantly highlight the matching rows.

100% compatible with Microsoft Excel (.xlsx) formats and existing conditional formatting rules.Intuitive and user-friendly interface for managing complex data validation and formatting.Lightweight, fast-loading, and free to use for everyday data analysis and reporting.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This usually occurs if the row number in your formula does not match the first row of your selected data range. If you selected A2:F10, your formula must reference row 2 (e.g., =$D2>$A2). If it references row 1, the highlighting will be offset by one row.

Can I highlight a row if a value is less than or equal to another?

Yes. You simply need to change the mathematical operator in your formula. For 'less than', use =$D2<$A2. For 'less than or equal to', use =$D2<=$A2.

How do I remove the highlighting rule later?

To remove the formatting, go to Home > Conditional Formatting > Clear Rules. You can choose to clear rules from the currently selected cells or from the entire sheet.

Can I compare three columns instead of two?

Yes, you can combine multiple comparisons using logical functions like AND() or OR(). For example, =AND($D2>$A2, $D2>$B2) will only highlight the row if the value in Column D is greater than both Column A and Column B.

Document & File Issues