Compare Monthly Values with Excel Conditional Formatting Icon Sets
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.

- 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.
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.
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.
In cell D2, enter =SIGN(C2-B2). The result is 1 for an increase, 0 for no change, and -1 for a decrease.
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.
Select the filled cells in column D, then choose Home > Conditional Formatting > Icon Sets and select a three-icon set.
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.
In Edit Rule, select Show Icon Only if you want to hide the helper numbers, then choose OK and Apply.

Use relative formula rules to color the current-month cells
Use colored cell fills instead of icons when you want the formatting directly on the current-month values.
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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 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. Apply an icon set: Select the helper cells, then choose Home > Conditional Formatting > Icon Sets and select a three-icon set.
- 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.

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.




