Excel Hack: Using Visualization Charts to Improve Your Data Analysis

Excel Hack: Using Visualization Charts to Improve Your Data Analysis

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.

  • Combination chart

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.­­­

  • Waterfall 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.

  • Radar chart

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.

  • Area chart

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.

  • Doughnut chart

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.

Share This Post:

5 thoughts on “Excel Hack: Using Visualization Charts to Improve Your Data Analysis”

  1. […] are many advantages of freezing panes in Excel. You can easily share one set of data with your team members, freeze the data, and then access the […]

  2. […] resource for creating effective visualizations. So let’s get started and learn how to make Excel charts easy and […]

  3. […] types of data than others. For example, if the data is in a structured format, then tools like Excel chart or SQL might be suitable. On the other hand, if the data is unstructured, then tools like Hadoop or […]

  4. […] excel charts are becoming a popular medium for presenting data analysis. There are some advanced excel visualization as well that you may want to refer as […]

Add a Comment

Your email address will not be published.