Add arrow next to number

Suppose you have a report that shows the sales changes from month to month.

Add-arrow-to-number

You wish to add green arrows to the numbers when the sales increase over the previous month and red arrow when the sales drop. This can be achieved with conditional formatting in Excel. Here is how it is done. 

First select the range that you want to format conditionally. In our example above, choose C3:C13.

Go to the Home tab and select conditional formatting (for Excel 2007 - 2016). Conditional Formatting is grouped within Style.

In the dropdown list, choose Manage Rules.

In the pop-up window, select New Rule.

conditional formatting manage rules window

Follow the following steps in the next pop-up window.

  1. Selec the option "Format all cells based on their values".
  2. Change the Format Stule to "Icon Sets".
  3. Change the yellow arrow to "No Cell Icon".
  4. Change the type to number.
  5. Change the operator to greater than ">".
  6. Make sure the operator for the second one is showing greater than and equal to ">="
  7. Enter the values to zero for both boxes.

When you have completed the steps, you will get the same setting as shown in the picture below:

Conditional Formatting Setup

The 3 arrows (Coloured) icon set is used, but the conditional formatting rule is defined so that only the green up arrow (positive values) or the red down arrow (negative numbers) can appear. No arrow is shown when the variance is 0 (0<=0 and >=0 at the same time).

Click OK. After that you will see a dialog like below.

conditional formatting manage rules completed

Click OK. The arrows are now displayed next to the numbers in C3:C13.

 


New! Comments

Have your say about what you just read! Leave me a comment in the box below.