Trend arrows are frequently employed worldwide, particularly in stock market statistics. Making the distinction between positive and negative values becomes easier using these arrows. Here, we will take you through 5 easy and convenient methods for inserting Excel trend arrows based on another cell.
You may download the following Excel workbook for better understanding and practice yourself.
What Is the Meaning of Trend Arrows?
Before going into the details, let’s talk about what “trend arrow” means. Trend arrows are generally up and down pointing arrows. We use them to easily understand the increase and decrease in the price of business commodities and stock prices. Most of the time, we see the use of this arrow in the stock market. We can easily make a decision from the trend arrows whether we should buy or sell this particular stock. Also, we can use this indicator in our business to monitor the sales growth of our sales reps and of the company overall.
5 Methods to Insert Trend Arrows Based on Another Cell in Excel
For ease of your visualization, we’re going to use a Comparison of Monthly Sales reports to show our methods. This dataset includes SalesRep’s name, and sales for June and July in columns B, C, and D correspondingly.
Now, we will utilize this dataset to insert Excel trend arrows based on another cell. If the sales in July increase from the sales in June, then we’ll insert an up arrow. Otherwise, we’ve to enter the down arrow. So, let’s explore them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience. Obviously, don’t forget to comment if you face any kind of inconvenience while doing this.
1. Applying Conditional Formatting
In the first method, we are going to show how we can insert only up and down trend arrows based on another cell in Excel using the Conditional Formatting feature. Having up and down arrows in the proper places on datasets makes them more presentable and visually pleasing. So, without further delay, let’s see how we can do it.
- At the very beginning, construct a new column with the heading Trend Arrows under Column E.
- Then, select cell E5 and enter the following formula.
Here, C5 and D5 represent the sales of John in the months of June and July respectively.
- After that, press the ENTER key.
- Following this, place the cursor at the bottom-right corner of cell E5. Immediately, it will look like a plus (+) sign. Actually, it’s the Fill Handle tool.
- Now, double-click on it.
Instantly, all other cells in the E5:E14 range get filled automatically with desired results.
- At this time, select cells in the E5:E14 range.
- Then, go to the Home tab.
- After that, click on the Conditional Formatting drop-down.
- Later, select New Rule from the drop-down list.
Suddenly the New Formatting Rule dialog box opens.
- In the Select a Rule Type section, choose Format all cells based on their values.
- Then, move to the Edit the Rule Description part.
- Here, select Icon Sets as the Format Style.
- For Icon Style, choose 3 Arrows (Colored).
- After that, check the box of Show Icon Only.
- Following this, write down both the Value as 0.
- Also, change both the Type to Number.
- Lastly, click OK.
2. Using Different Symbols
We can use different types of shapes and symbols in Excel. In some cases, they could be very helpful. Like in this case, we’re going to utilize them to insert Excel trend arrows based on another cell. So, without further delay, let’s dive in!
- First of all, make a new column like Method 1.
- Also, create another range in cells in the B16:C16 range.
- Currently, go to cell B16.
- After that, move to the Insert tab.
- Then, click on the Symbols group drop-down.
- Next, select Symbol from the available options.
Presently, the Symbol wizard gets opened.
- Here, go to the Symbol tab.
- Then, choose Arial as Font.
- To get the Subset, pick Geometric Shapes.
- Then, from the available symbols, select Black Up-Pointing Triangle.
- Consequently, click on Insert.
- Similarly, insert another symbol which is the Black Down-Pointing Triangle in cell C16.
- Henceforth, go to cell E5 and write down the following formula.
Here, C5 and D5 serve as John’s sales for June and July. B16 represents the cell reference of an up-pointing triangle. On the other hand, C16 means the reference for the down-pointing triangle. Here, the IF function implies a logical test that the value in cell D5 is greater than the value in cell C5. If this is TRUE, cell E5 will get the value of cell B16. Otherwise, it will hold the value of cell C16.
In this case, the value of D5 is greater than the value of C5. So, the up-pointing triangle sits in cell E5.
- As usual, tap ENTER.
3. Utilizing Specific Font
In this method, we’ll see the magic of another font that we don’t normally use in daily life Excel. Let’s see the process in detail.
- Firstly, move to cell E5 and paste the formula below.
Here, we’ve used the SIGN function. It returns the sign of a number. Actually, it determines whether a number is positive, negative, or neutral. It returns to 1 for positive numbers. For negative values, it is -1 otherwise it’s 0.
- Secondly, hit the ENTER key.
- At this time, select all cells in the E5:E14 range.
- After that, click on the Font drop-down arrow.
- Then, select the Windings 3 font from the list.
Using this font, the worksheet looks like the image below.
- Again, select cells in the E5:E14 range.
- Then, press the CTRL key followed by 1 on your keyboard.
Actually, it’s the shortcut to open the Format Cells dialog box.
- At first, jump to the Number tab.
- From the Category section, select Custom.
- Then, in the Type box, write down the following.
- Lastly, click OK.
Finally, the trend arrows look like those in the following image.
Read More: How to Draw Arrows in Excel (3 Simple Ways)
4. Inserting CHAR Function
In this section, we’ll use a new function named the CHAR function. It delivers us the symbol or character defined by a numeric value. So, allow me to demonstrate the process below.
- Initially, select cell E5 and paste the following formula into that cell.
This formula works just like the formula in Method 2.
- Then, press the ENTER key.
- Currently, select cells in the E5:E14 range.
- After that, move to the Home tab.
- Then, click on the Font drop-down icon.
- Later, select the Windings 3 font.
Immediately, the symbols get changed into trend arrows.
5. Implementing Custom Format
Additionally, we can get help from the Custom Format options to insert trend arrows based on another cell in Excel. It’s simple and easy. Just follow along.
- Primarily, select cell E5 and put the following formula into the Formula Bar.
- Secondarily, press ENTER.
- Then, select the whole Trend Arrows column just without the heading.
- Again, press CTRL+1 to open the Format Cells dialog box.
- Here, select Custom as Category first.
- Secondly, write down the following in the Type box.
[Green] ↑ $0;[Red] ↓ -$0
- Accordingly, click OK.
Magically, we can see trend arrows along with the values in Column E.
How to Remove Trend Arrows
Trend Arrows is a widely used topic in Excel. Though it is very helpful, we may want to remove these from the sheet after our desired work is done. So, follow the two methods below to remove trend arrows in Excel.
1. Removing Trend Arrows by Clearing Rule
In Method 1, we saw that we could get trend arrows by applying Conditional Formatting. This looks like the following image.
Now, if we want to remove these arrows, how we can do that? To know this answer, follow the steps below very carefully.
- At first, jump to the Home tab.
- Secondly, click on the Conditional Formatting drop-down in the Styles group.
- Thirdly, select Clear Rules from the drop-down list.
- Then, choose Clear Rules from Entire Sheet from the sub-menu.
Amusingly, you’ll see the arrows are no longer available in the worksheet.
Read More: How to Remove Tracer Arrows in Excel
2. Using Custom Format Option
Also, we can use the Custom Format option to do the same task. Using this, we can remove the arrows. In the following picture, we can see the trend arrows available in the sheet.
Presently, we’ll hide them, so it looks like they’ve been removed. So, let’s see it in action.
- First of all, select all the trend arrows in cells in the E5:E14 range.
- Then, press CTRL and 1 key simultaneously on the keyboard.
- In the Format Cells dialog box, select Custom as Category.
- Then, in the Type box, paste the code below.
- Correspondingly, click OK.
Now, they can no longer be seen.
Though we can’t see them in the cell, they are still present here.
- To prove this, select cell E5 and look into the Formula Bar.
For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it by yourself.
This article provides easy and brief solutions to insert trend arrows based on another cell in Excel. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider to explore more.