Overview
- Excel charts are a powerful way to showcase your analytics profile
- Here are three ambitious Excel charts every analyst should be familiar with
Introduction
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:
Table of Contents
- Excel Chart #1 – Waterfall Charts
- Excel Chart #2 – Funnel Charts
- Excel Chart #3 – Pareto Charts
Excel Chart #1 – Waterfall Charts
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.
Step 1 – Add Base, Fall, and Rise columns
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.
- Fall – This column will only signify the values that are falling over time (negative values). In order to do so, we will use this formula:
- =IF(D28<=0,D28*-1,0)
- Rise – This column will only signify the values that are increasing over time (positive values). To do this, we will use this formula:
- =IF(D28 > 0, D28*1,0)
- Base – The base column represents the starting point for the rise and fall column. We calculate this column using:
- Add 0 to 2012 and 2013 columns
-
- =D29+D31-E30
Now, our data table looks like this:
Step 2 – Make a cluster Chart
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.
Step 4 – Make changes to the Base Column
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:
Step 5 – Add horizontal Axis labels
The labels on the horizontal axis don’t provide an intuitive understanding. So let us go ahead and change them:
Step 6 – Proper Formatting
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:
- Add Data labels
- Remove Gridlines
- Remove unwanted data labels
- Add the desired color for the columns Rise and Fall. We have chosen Red to signify the drop in revenue and Green to signify the growth in revenue
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.
Excel Chart #2 – Funnel Charts
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:
Step 1 – Add a column for “Extra Spaces”
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:
- =(LARGE($D$3:$D$6,1)-D3)/2
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.
Step 2 – Add stacked chart
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:
Step 3 – “No Fill” the Extra Spaces column
There is no real relevance of the calculated Extra Spaces column. So we will go ahead and “No Fill” it:
Step 4 – Proper Formatting
We have the skeletal structure ready for the funnel chart. Let us do some formatting and make it pleasing to the eyes:
- Reduce the gap width to 0%
- Add Data Labels
- Remove Gridlines
- Remove unwanted data labels
- Remove Chart title
Great! Now you can go ahead and make a sales funnel on your own!
Excel Chart #3 – Pareto Charts
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:
Step 1 – Add Columns for cumulative percentage
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:
Step 2 – Add a cluster chart
Let us add our bar chart. We will not select the cumulative sum column as it is used only for calculation purposes.
Step 3 – Change chart type for cumulative percentage
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:
Step 4 – Scaling and Formatting
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!
End Notes
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.
