I love creating out-of-the-box visualizations. Most analytics professionals can come up with a bar chart or a line chart but the ability to take your visualization skill a level further is where analysts begin to excel. And let me be honest – a well-crafted visualization will take you a long way in the analytics space.
Being a good storyteller is key here. So the question is – how do we use Microsoft Excel’s immense flexibility and depth of charts to tell our story in an impactful and effective manner?
There are a wide variety of charts we can choose but we need to understand which visualization suits our use case. These charts need to make our analytics profile stronger, our portfolio more diverse and should also tell a coherent story. The issue is that there’s no one size fits all chart.
So in this article, we will be discussing 3 advanced excel charts that will make you a pro in the analytics and visualization field. And of course, we’ll be using Excel, still the most widely used tool for analytics, to make these charts.
This is the second article in my Excel Charts series. I highly recommend going through the previous article to become a more efficient analyst:
I encourage you to check out the below resources if you’re a beginner in Excel and Business Analytics:
One of the most advanced charts in Excel, the Waterfall Chart gets its name thanks to the resemblance of its structure to waterfalls. This powerful chart provides a visual snapshot of the positive and negative changes to value over a period of time.
In a waterfall chart, the initial and final values are represented by columns. The columns depicting the positive and negative impacts are depicted by floating columns in respective colors. Here’s an example of a waterfall chart which we will be making:
These are widely used across industries, specifically the finance industry.
Let us take an example and build a waterfall chart from scratch in Excel. We have the year-on-year (YoY) revenue generation data of a company along with some of the influencing factors – Environment effect, Manpower Impact, Productivity Impact. We will follow the below steps to make a waterfall chart. You can follow along with the video too for better understanding:
Note: This video is a part of the Beginner to Advanced Excel course by Analytics Vidhya. You can checkout the full course here.
In order to make a waterfall chart, we need to make some changes to the data table. We are going to distribute the value column into 3 columns – fall, base, and rise. Let us see what is the use of it and how to add these columns.
Now, our data table looks like this:
The waterfall chart is essentially a clustered bar chart with a little bit of customization. So let us add one here. We will only be using the rows – Metric, Base, Fall, Rise.
The base column signifies the starting point for the fall and rise column. In simple words, this column helps us lift the fall and rise columns to the desired height. Now we shall choose “No Fill” for it:
The labels on the horizontal axis don’t provide an intuitive understanding. So let us go ahead and change them:
Now, we have achieved a skeleton figure of the waterfall chart using the above steps. We just need to perform some additional maneuvers.
All we need to do is a little bit of formatting:
After a few more formatting changes, our waterfall chart looks like this:
C0ngratulations on building your first waterfall chart! You can think of different scenarios to use this powerful chart in your domain.
Funnel charts are my favorite charting option to depict the sales flow or the marketing lead generation. These charts are also one of the most used visualizations in the sales and marketing domain.
Funnel charts are used to visualize the reduction of data from one stage to another.
Let us understand this better by taking up an example.
We have the phase flow data of an E-Commerce company. The flow can be visualized like this:
Website > Product page > Cart > Checkout page
To understand the drop in traffic from one stage to another, we will plot a funnel chart by following the video or the steps given below:
If you observe closely, funnel charts have an inverted pyramid structure. How can we make this structure using bar charts?
We need to provide an extra column to achieve this “Extra space”. To do this, we can use this formula:
The LARGE() function takes in a range of values and returns the kth highest value. In this case, k = 1. We will understand why we applied this formula in the next step.
A funnel chart is basically a bar chart or a stacked chart with added formattings. So let us add the bar chart in Excel:
Here, the blue columns are the “Extra Space“.
Did you understand why we added the Extra Space column? It is because we required the gap to make it look like a pyramid. But there is still an anomaly – it is not an inverted pyramid! So let us make it that way:
There is no real relevance of the calculated Extra Spaces column. So we will go ahead and “No Fill” it:
We have the skeletal structure ready for the funnel chart. Let us do some formatting and make it pleasing to the eyes:
Great! Now you can go ahead and make a sales funnel on your own!
Pareto charts are particularly intriguing for anyone in analytics or statistics. A lot of organizations rely on Pareto charts for making data-based decisions.
According to the Pareto rule, or the 80/20 rule, roughly 80% of the output results or effects are gained by 20% of the input or the causes.
In simple words, roughly 80% of the company revenue is due to 20% of its products while the other 80% of the products contribute to only 20% of the revenues. Confusing? Don’t worry, we will understand better with an example.
There’s a parent website that has several different sub-domain services within the website, such as News, Job portal, E-mail services, etc. The company has been spending a lot of resources on each of these sub-domains but now they want to cut their costs due to recurring losses. A Pareto chart can help in aiding the decision-making process. Let us see how to make one in Excel!
You can follow along with the video or refer to the steps below:
We need to calculate the cumulative percentage for the website visits. In order to do so, we will add columns – cumulative sum and cumulative percentage. Let us see how:
To add cumulative sum, just input the formula – “=SUM($C$17:C17)”
To add the cumulative percentage, input the formula: “=D17/$D$25”
Finally, our data looks like this:
Let us add our bar chart. We will not select the cumulative sum column as it is used only for calculation purposes.
In the above chart, we have a clustered bar chart consisting of Visit and cumulative percentage. The latter is not visible due to the difference in the scale of values.
We need a line chart for the cumulative percentage so we will change its chart type:
The Pareto chart is ready but we need to make it more understandable and aesthetically pleasing so we will do some scaling and formatting.
We will change the scale of the secondary axis from 120% to 100% and also its units:
We have finally made our Pareto chart and it looks like this:
By looking at the Pareto chart, we notice that 80% of the website visits are coming from the E-mail and News subdomains. Other subdomains only constitute 20% of the visits!
In this article, we covered three beautiful Excel charts of different kinds to help you become an efficient analyst and a better storyteller. I hope these charts will help you in building amazing visualizations, save you a lot of time. and impress your boss. 🙂
Let me know your favorite Excel Charts which you feel make a visualization great.
Lorem ipsum dolor sit amet, consectetur adipiscing elit,
