Formatting Issues

Compare Monthly Values with Excel Conditional Formatting Icon Sets

Anum MushtaqAnum Mushtaq Nov 14, 2026 868 views

Question details

Show an icon for each row indicating whether the current month's value increased, stayed the same, or decreased from the previous month.

Compare Monthly Values with Excel Conditional Formatting Icon Sets
Product
Microsoft Excel
Device & OS
not provided
Scenario
A reporting worksheet compares current-month values with previous-month values for multiple rows.
Observed behavior
The desired result is a green icon for an increase, an orange icon for no change, and a red icon for a decrease.
Before you start

Identify the previous-month and current-month columns and check that the rows to be compared contain numeric values. The example below uses column B for the previous month, column C for the current month, and column D as a helper column.

Solution 1Recommended

Use a helper column to apply an icon set to every row

Use this method to calculate each row's change and display the corresponding icon in a separate column.

The helper formula uses relative references so Excel compares the correct cells in each row. The icon set then evaluates the helper values; it does not need a separate relative formula for each threshold.

1
Calculate the direction

In cell D2, enter =SIGN(C2-B2). The result is 1 for an increase, 0 for no change, and -1 for a decrease.

2
Fill the formula down

Select D2 and drag its fill handle down through the reporting rows, such as D2:D100. Excel adjusts the B and C references for each row.

3
Add an icon set

Select the filled cells in column D, then choose Home > Conditional Formatting > Icon Sets and select a three-icon set.

4
Set the thresholds

With the range selected, choose Home > Conditional Formatting > Manage Rules, select the icon-set rule, and choose Edit Rule. Set both threshold types to Number. Set the green icon threshold to >= 1 and the middle icon threshold to >= 0; values below 0 receive the red icon.

5
Show only the icons

In Edit Rule, select Show Icon Only if you want to hide the helper numbers, then choose OK and Apply.

Use a helper column to apply an icon set to every row
Middle icon color: Excel's standard three-icon sets commonly use yellow rather than orange for the middle icon. Choose a suitable available icon in Edit Rule; an exact orange icon may not be available in the built-in sets.
Compare monthly values in WPS

Show monthly change icons in WPS Spreadsheet

Use a helper column in WPS Spreadsheet to calculate each row's change, then apply a three-icon conditional formatting rule.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Calculate the change: With previous-month values in column B and current-month values in column C, enter =SIGN(C2-B2) in D2 and fill the formula down.
  3. 3. Apply an icon set: Select the helper cells, then choose Home > Conditional Formatting > Icon Sets and select a three-icon set.
  4. 4. Edit the rule: Open Conditional Formatting > Manage Rules, edit the icon-set rule, and set numeric thresholds of 1 for the green icon and 0 for the middle icon. Select Show Icon Only if available, then apply the rule.
Show increases, unchanged values, and decreases with a three-icon set.Open and edit Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and conditional formatting controls.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the helper formula use SIGN?

SIGN returns 1 when C2-B2 is positive, 0 when it is zero, and -1 when it is negative. These three results make it straightforward to set numeric icon thresholds.

Can I apply the comparison to all rows at once?

Yes. Fill the helper formula down through the data rows, select the filled range, and apply one icon-set rule to that range.

How do I hide the helper numbers but keep the icons?

In Excel, select the helper range and open Home > Conditional Formatting > Manage Rules. Edit the icon-set rule and select Show Icon Only.

Can I put the icons directly on the current-month cells?

The helper-column method places icons in the helper column. To format the current-month cells directly based on each row's comparison, use formula-based rules with colored fills instead.

What if my data starts on a different row?

Change the formula to match the first data row. For example, if previous and current values are in B3 and C3, enter =SIGN(C3-B3) in the helper cell on row 3 and fill it down.