Formatting Issues

How to Apply Excel Conditional Formatting for Values Lower Than the Cell to the Left

Kushani Nimanthika Oct 7, 2026 869 views

Question details

The user needs to highlight cells, such as weekly meter readings, if their value is strictly lower than the value in the cell immediately to the left.

How to Apply Excel Conditional Formatting for Values Lower Than the Cell to the Left

Product: Excel
Device & OS: not provided
Scenario: Tracking sequential data across adjacent columns where a drop in value needs to be visually identified using a relative formula rule.
Observed behavior: Cells are formatted automatically based on a dynamic comparison with their immediate left neighbor, excluding the very first column from the rule.

Before you start

Ensure your data is organized in adjacent columns from left to right (e.g., sequential dates or weeks) and identify the specific range you want to format, keeping in mind that the first column should be excluded since it has no left neighbor to compare against.

Solution 1Recommended

Use a Relative Formula for Conditional Formatting

Apply a custom formula rule with relative cell references to dynamically compare each cell against the cell to its immediate left.

To make this work, the formula must use relative references instead of absolute references (no dollar signs). This allows the formula to adjust automatically for every cell within your selected range.

Select the target data range

Highlight the cells you want to format, explicitly excluding the first column of your dataset. For example, if your data spans C2 to K11, select the range D2:K11.

Open the Conditional Formatting menu

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting in the Styles group, and select New Rule from the dropdown menu.

Choose the formula rule type

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

Input the relative formula

In the formula bar, enter =D2<C2 (assuming D2 is the top-left cell of your selected range). Ensure there are no dollar signs ($) in the formula so the references remain relative.

Set the cell formatting format

Click the Format button, choose your desired highlighting style (such as a red fill color under the Fill tab), click OK, and then click OK again to apply the rule.

Use a Relative Formula for Conditional Formatting

Understanding Relative References: By omitting the absolute reference symbol ($), Excel automatically iterates through your selected range. For cell E2, it checks =E2<D2; for F5, it checks =F5<E5, matching exactly what you need.

Use WPS Spreadsheet

Highlight Data Trends Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful and intuitive conditional formatting tools to seamlessly highlight values lower than previous cells. It is a highly efficient software that mirrors the exact workflow you are used to, making data visualization quick and error-free.

1. Open your file in WPS Spreadsheet: Launch WPS Office, open your spreadsheet, and highlight the data range excluding the first column (e.g., D2:K11).
2. Access Conditional Formatting: Go to the Home tab, click on Conditional Formatting, and choose New Rule.
3. Enter the comparison formula: Select 'Use a formula to determine which cells to format' and type your relative formula, such as =D2<C2.
4. Apply and save: Click Format to pick a background color, click OK to confirm, and watch your data trends highlight instantly.

Fully compatible with Microsoft Excel (.xlsx) conditional formatting rules and formulas.Familiar, ribbon-based interface for zero learning curve.Lightweight, fast, and completely free for powerful data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong cells?

This usually happens if you accidentally used absolute references (like =$D$2<$C$2) instead of relative references (=D2<C2). It can also occur if the cell references in your formula do not correspond correctly to the top-left cell of the range you selected.

Can I format values that are greater than the cell to the left instead?

Yes. You simply need to change the less-than operator (<) to a greater-than operator (>) in your conditional formatting formula. For example, use =D2>C2.

How do I remove the conditional formatting rule if I make a mistake?

Select the formatted range, go to the Home tab, click Conditional Formatting, hover over Clear Rules, and choose 'Clear Rules from Selected Cells' or 'Clear Rules from Entire Sheet'.

Does this relative formula work for rows instead of columns?

Yes. If your data is listed vertically and you want to compare a cell to the one directly above it, you would adjust the formula to compare rows (e.g., =A3<A2) and apply it to your vertical range.

Document & File Issues