Up and Down Arrows in Excel Using Conditional Formatting

Having up and down arrows in the proper places of datasets makes them more presentable and visually pleasing. It is one of the features Microsoft Excel offers that not only helps spreadsheets look good on the eyes but also studying a dataset easier. You can use these arrows both with and without the number they are representing. But using default arrow formatting usually inserts three types of arrows. We may not want that in many cases. In this tutorial, we are going to show how we can insert only up and down arrows in Excel using conditional formatting.

Insert Up and Down Arrows in Excel Using Conditional Formatting: 2 Suitable Examples

In the tutorial, we are going to demonstrate two different examples to better explain the method. Each example will contain a slightly different method. But in the end, it won’t vary much in terms of results. In each example, we are going to represent the upward or downward direction of prices with up and down arrows. And we are going to use Excel’s conditional formatting feature for that.

Example 1: Inserting Up and Down Arrows Based on Oil Prices

First, take a look at the following dataset.

For some entries, the price has gone up. And for others, the price has gone down. We are going to represent them with up and down arrows consecutively.

To measure the upward or downward direction of the price first, we can use different methods. But for this example, we are using the SIGN function. The SIGN function takes a number as an argument and returns a negative or positive one depending on the sign of the argument. At the same time, the output will be zero if the input is zero and the output will give us an error for non-numeric values.

Follow these steps to see how we can use the function and then use up and down arrows using conditional formatting in Excel.

Steps:

First of all, select cell E5.
Then write down the following formula.

=SIGN(D5-C5)

After that, press Enter.

Now select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

Next, select the range if it is not selected already.
Now go to the Home tab on your ribbon.
Then select Conditional Formatting from the Styles group.
Now select New Rule from the drop-down menu.

As a result, the New Formatting Rule box will open up. First, select Format all cells based on their values in the Select a Rule Type section.
Then select Icon Sets as the Format Style under Format all cells based on their values.
Under that, select Arrows as the Icon Style.

Next, select Number in both the Type drop-downs as shown in the figure.

Then select No Cell Icon for when <0 and option.

You can also check/uncheck the Show Icon Only option, depending on whether you want to show the value beside the icon or not. We are opting not to choose the numbers to show.

Finally, click on OK.

You will see the dataset will change to something like this.

This way, you can insert up and down arrows in Excel using conditional formatting.

Example 2: Inserting Up and Down Arrows Based on Stock Prices

Now let’s look at another example. Here is a dataset of stock prices at different periods.

In a similar fashion, we can indicate the upward or downward motion of the stock price with up and down arrows. Instead of using the SIGN function, we can also use the IF function to determine the increase or decrease in price among many other methods. Let’s do that for this example.

The IF function takes two primary arguments- a condition and an output if the condition is true. It can also take another one and output if the condition is false. Otherwise, it goes on to print a blank value on the cell.

Follow these steps to see how we can use the function and then insert dynamic up and down arrows in Excel using the conditional formatting feature.

Steps:

First of all, select cell E5.
Then write down the following formula in it.

=IF(C5>D5,-1,1)

Now press Enter.

After that, select the cell again and click and drag the fill handle icon bar to the end of the column to replicate the formula for the rest of the cells.

Now go to the Home tab while the column is still selected.
Then click on Conditional Formatting from the Styles group.
After that, select New Rule from the drop-down menu.

Thus the New Formatting Rule box will open up. First, select Format all cells based on their values in the Select a Rule Type section.
Now select Icon Sets as the Format Style under Format all cells based on their values.
Under that, select Arrows as the Icon Style.

Next, select Number in both the Type drop-downs as shown in the figure.

Then select No Cell Icon for when <0 and option.

Keep in mind that you can also check/uncheck the Show Icon Only option, depending on whether you want to show the value beside the icon or not. We are opting not to choose the numbers to show.

Finally, click on OK.

You will see that the arrows will appear on the cells replacing the numbers.

This way you can also insert up and down arrows in Excel using conditional formatting. Arrows created in this method will be dynamic and will change according to the changed value in cells.

Read More: How to Add Trend Arrows in Excel

How to Insert Up and Down Arrows Without Conditional Formatting in Excel

Previously, you have seen the insertion of up and down arrows using the conditional formatting feature. Excel has other ways to insert up and down arrows without the help of conditional formatting. For example, we can use a combination of the IF function and the CHAR function to achieve a similar result.

The IF function takes a condition as a first argument, and output as a second which it returns if the condition is true. And another optional argument to return if the condition is false. Meanwhile, the CHAR function takes only a number as the argument and returns a character based on the number it represents.

Follow these steps to see how you can achieve this combination to insert up and down arrows in Excel without conditional formatting.

Steps:

First, select cell E5.
Then write down the following formula in it.

=IF(C5>D5,CHAR(200),CHAR(199))

After that, press Enter.

It may not show the exact output yet. But keep on going through the next steps.

Now select the cell again. Then click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

Finally, you need to change the font style of the desired cells where you want to insert up and down arrows. To do that, select the cells first.
Then go to the Home tab and select Wingdings 3 as the font style from the Font group.

You will see that the cell values will change into arrow signs.

This way, you can insert up and down arrows without using conditional formatting in Excel.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Up and Down Arrows Using Conditional Formatting.xlsx

Conclusion

These were the methods you can use to insert up and down arrows in Excel with and without using the conditional formatting feature. Hopefully, you can insert the binary arrows using your preferred method without any problems. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

<< Go Back to Arrows in Excel | Excel Symbols | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Up and Down Arrows in Excel Using Conditional Formatting

Insert Up and Down Arrows in Excel Using Conditional Formatting: 2 Suitable Examples

Example 1: Inserting Up and Down Arrows Based on Oil Prices

Example 2: Inserting Up and Down Arrows Based on Stock Prices

How to Insert Up and Down Arrows Without Conditional Formatting in Excel

Conclusion

Related Articles

Leave a reply Cancel reply