Visualization charts in Excel
Microsoft Excel can be a great tool for visualization purposes. There are some very powerful charts functionalities inbuilt in Excel, that can be used to depict numbers visually.
Let us look at some of those interest charts that are already available to use at the click of a button.
As the name suggests, this chart can be used to show a combination of 2 different variables on the same chart. There are typically 2 vertical axes in this chart i.e. the primary vertical axis and the secondary vertical axis. Generally, we plot a column chart on one of the axis and a line chart on the other. A graphical example of a combination chart would look something like below:
In the above chart, we have plotted the absolute Non-Performing Loans of a Bank on the horizontal axis and the % of Non-Performing loans on the vertical axis. Note, the 2 variables are related and can be meaningfully plotted on a combination chart.
A waterfall chart is used extensively in Excel to demonstrate the evolution/contribution of different items to a variable. For example, we can use the waterfall chart to show the sales contribution of different products for a supermarket. It would look something like below:
As you can see in the above chart, the vertical columns show the sales contribution of the different items. They add up together to be equal to the gross sales. From this, the discounts are reduced to arrive at the net sales of the company. Using a waterfall chart is a very effective way to demonstrate the contribution of the different elements of a total. It can be used to show both increases and decreases in the total contribution.
A radar chart is another simple yet powerful chart in Excel which helps to visualize multivariate data on a single chart. It is also called a spider chart since the visual look of a radar chart is similar to a spider web. One typical use case of a radar chart is to show project activity status report on actual completion vs planned completion. See below is a graphical representation of one such illustration.
In the above chart, we have depicted the different activity in a typical real estate construction project i.e. External development, Wooden door frames, marble flooring, etc. The blue line shows the actual completion % against each of the activity and the orange line shows the planned completion. There is a common vertical axis for each activity where the actual vs planned activity is plotted. The gap between the blue and orange lines on each activity represents the under-achievement in the activity. Hence, the project status can be assessed right away by looking at the spider chart above.
The Area chart is another visually appealing chart used to show the contribution of different activities over a period of time. It can be used to demonstrate the evolution of different items that contribute a whole. Again, if we take the above example of a super market, we can show the sales evolution of different items over time.
In the above chart, we have shown the contribution of different products in the overall sales from 2014 to 2018. The area under each product shows the relative contribution of the item in the overall sales. For example, the area shaded under blue color represents the contribution of the Cereals products in the overall sales. Similarly, we can also interpret the area of the different other products shown on the chart. The different products stack up one on top of the other. It is a powerful visual chart to show the contribution of different items over a period of time.
One other interesting chart in Excel is the Doughnut chart, which can be used to show the relative proportions in a whole. It is very similar to how you would use a Pie-Chart, but the look of this chart is in the form of a Doughnut. Just like the pie chart, the total should add up to 100%.
In the above chart, we have shown the sales % breakdown of different items that contribute to the overall sales of a supermarket. The different pie of the doughnut adds up to 100% of the total sales. As we said earlier, it looks very similar to a Pie Chart but is in the shape of a Doughnut.
You love doughnuts, don’t you?
P.S. For a more detailed illustration of many more and customized visualization techniques in Excel, please visit our online course here.
The Learning Hour is a weekly knowledge sharing initiative of SKILLFIN LEARNING. To subscribe to this initiative, simply click on SIGN UP button above! We will add you to the distribution list.