Hey everyone! we are going to learn how to use symbols in formulas and insert them into our chart. The symbols are also known as Unicode characters. Especially we are going to use up and down arrows....
Where we can find these symbols? If you click on Insert Tab and then Symbols Icon, a new window appears. From Font choose Arial and from Subset choose geometric shapes Now you see the arrows we want. Of course, I’ve already put them into my data table.
Now let’s look at the table: Here we have the sales of 2 years, 2014 and 2015 for all months. Also, we have a simple formula that calculates the percent of change between 2014 and 2015.
In cells G2 and G3 I have the up and down arrow. In column E we will calculate the arrows, using IF statement. Let’s get started: go to E2 cell and start typing : equal if C2 is greater than B2 then apply G2 which is up arrow , otherwise apply G3.Don’t forget the absolute reference in cells G2 and G3. Close parenthesis and press enter. Copy the formula all the way down. Great
The next step is to create the chart. So, select all my data and from Insert tab and recommended charts icon, I’m going to choose clustered column chart and move it below the table. We have to make some changes to the chart, starting from horizontal axis. Right-click on it, select data and from the new window click on edit button on your right. The axis label range that we want to appear on the chart is from D2 to E13. Also from the legend entries we will delete 2014 , percentage change and change. Press ok and from quick layout button, select layer 5 which shows the table with sales. Get rid of the legend, gridlines, vertical axis and title.
The chart is ready. If you want you can change the font of axis, the color of columns and the gap width. The symbols and text gives you more information about data and these types of charts are used in dashboards very frequently.
Thank you very much and waiting for your questions.
Click here to download the Excel project File
Click here to download the Excel Sample File
You can see the detailed video tutorial below or on YouTube
If you want to learn how to make amazing Dynamic Excel Charts, Dashboards and Pivot Charts in a few weeks, take our Udemy Course for only $25 !! (original price: $195 - 87% OFF!)
- How to Import-Convert Data from Word Document into Excel worksheet
- How to generate Random Decimal Numbers between 0 and 1
- Blocking a user to interact outside a specific range – ScrollArea Property
- How to create a Tornado Chart in Excel - Ideal for Showing Financial Analysis
- How to create a Bi-directional Bar Chart in Excel - Ideal for comparing data