Formatting Issues

Excel Conditional Formatting Based on the Previous Cell Value

Steven ParkerSteven Parker Nov 14, 2026 869 views

Question details

Color percentage cells according to whether their values are higher, lower, or equal to the preceding cell in the same row.

How to Use Excel Conditional Formatting Based on the Previous Cell
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to compare consecutive percentage values across one or more rows without third-party tools.
Observed behavior
Each numeric cell is formatted according to its relationship with the numeric cell immediately to its left.
Before you start

Make sure the percentages are stored as numbers, not text. Identify the first cell to format and the cell immediately to its left; the example below formats B2:M3 using A2 as the first comparison cell.

Solution 1Recommended

Create Formula-Based Conditional Formatting Rules in Excel

Use three rules to color numeric values based on the cell immediately to their left.

The formulas use relative references, so Excel adjusts them for each cell in the selected range. Both cells must contain numbers for a rule to apply.

1
Select the target range

Select B2:M3, starting with B2 as the active cell. Each selected cell will be compared with the cell immediately to its left.

2
Create the higher-value rule

Go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format. Enter =AND(ISNUMBER(A2),ISNUMBER(B2),B2>A2), choose Format > Fill, select green, and confirm.

3
Create the lower-value rule

Choose Home > Conditional Formatting > New Rule again. Enter =AND(ISNUMBER(A2),ISNUMBER(B2),B2<A2), select a red fill under Format, and confirm.

4
Create the equal-value rule

Add another formula-based rule with =AND(ISNUMBER(A2),ISNUMBER(B2),B2=A2). Choose a different fill color for equal values and confirm.

5
Check the applied range

Open Home > Conditional Formatting > Manage Rules and verify that Applies to covers B2:M3 for all three rules. Change the range if needed.

Create Formula-Based Conditional Formatting Rules in Excel
Adapt the references: For a range starting at C2, replace A2 and B2 in each formula with B2 and C2. Keep the references relative, without dollar signs.
Conditional formatting in WPS Spreadsheet

Compare Previous Cell Values in WPS Spreadsheet

Use formula-based conditional formatting in WPS Spreadsheet to color each percentage according to the value immediately to its left.

  1. 1. Open and select the range: Open your workbook in WPS Spreadsheet and select B2:M3, with B2 as the active cell.
  2. 2. Add the increase rule: Go to Home > Conditional Formatting > New Rule, choose the formula-based rule option, and enter =AND(ISNUMBER(A2),ISNUMBER(B2),B2>A2). Set the fill to green and confirm.
  3. 3. Add the decrease and equal rules: Create two more formula-based rules: =AND(ISNUMBER(A2),ISNUMBER(B2),B2<A2) with a red fill, and =AND(ISNUMBER(A2),ISNUMBER(B2),B2=A2) with a different fill.
  4. 4. Verify the rules: Open Home > Conditional Formatting > Manage Rules and check that each rule applies to B2:M3. Save the workbook when finished.
Highlight increases, decreases, and equal values across rows.Open and edit Microsoft Excel XLSX and XLS files.Apply the same relative-reference rules across multiple rows.Use a familiar spreadsheet interface without third-party tools.
microsoft office alternative - wps office

Frequently Asked Questions

How do I compare cells starting in column C instead?

Select the range beginning at C2 and use B2 as the previous-cell reference. For example, the increase formula is =AND(ISNUMBER(B2),ISNUMBER(C2),C2>B2).

Why are some percentage cells not changing color?

The rules apply only when both the current cell and the cell to its left contain numbers. Check for blank cells or percentages stored as text, and verify the applied range under Home > Conditional Formatting > Manage Rules.

Can I apply the rules to more than one row?

Yes. Select a multirow range such as B2:M10 before creating the rules, or update Applies to in Conditional Formatting > Manage Rules. Relative references make each cell compare with the cell to its left in the same row.

How do I change the color for equal values?

Open Home > Conditional Formatting > Manage Rules, select the equal-value rule, and choose Edit Rule > Format > Fill to select another color.