3 Advanced Excel Charts Every Analytics Professional Should Try

Ram Dewani 26 Aug, 2021 • 8 min read

Overview

  • Advanced Excel charts are a great way to create effective and impactful stories for our audience
  • Learn 3 advanced Excel charts here to impress your manager and build rapport with your stakeholders

 

Introduction

I was completely focused on analyzing data and using statistical methods to build complex data science models when I first started my analytics journey. In fact, I know a lot of newcomers are fixated on this method of approaching things. I have some news for you – this is actually not the core quality of a good analyst.

The stakeholders weren’t able to understand what I was trying to communicate to them. There was a missing link in the whole scenario – storytelling.

Advanced charts in excel

I was able to level-up by improving my storytelling skills. To convey the story to our management team, a key skill to learn is understanding different types of charts. Usually, we can explain most things with a simple bar chart or a scatter plot but they don’t always fulfill the need.

There is no one size fits all chart and that’s why to create intuitive visualizations, it is imperative that we understand different types of charts and their usage. And Excel is the perfect tool to build advanced yet impactful charts for our analytics audience.

In this article, I am going to discuss 3 powerful and important advanced Excel charts that are going to make you a pro in front of your audience (and even your manager).

This is the sixth article in my Excel for Analysts series. I highly recommend going through the previous articles 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

  1. Advanced Excel Charts #1 – Sparklines
  2. Advanced Excel Charts #2 – Gantt Charts
  3. Advanced Excel Charts #3 – Thermometer Charts

 

Advanced Excel Charts #1 – Sparklines

I will start off with one of my favorite chart types – Sparkline charts. These charts really help me out in making amazing dashboards! So what are sparkline charts?

Sparkline charts are typically small in size and fit into a single cell. These charts are wonderful to visualize trends in your data such as seasonal increase or decrease in value.

In Microsoft Excel, we have 3 different types of sparkline charts – Line, column, and Win/Loss. Let us see how can we make a sparkline chart with this problem statement:

You are an analyst in a product-based company and you want to understand the performance of the salespersons during the first six months of the year. You decide to use the sparkline charts for this.

Follow the steps or refer to the video for more understanding of sparkline charts:

Step 1 – Choose your Type of Sparkline Chart

You will first need to select the type of sparkline chart you want to plot. In our case, we will be using a line chart.

Go to Insert -> Line:

Step 2 – Create  Sparklines

Select the first row of the data in the Data Range and the corresponding Location Range:

We have successfully made our Sparkline chart! Now we just need to drag it for all our rows:

Step 3 – Desired Formatting

We have the basic sparklines ready. We can format it so that the chart becomes more intuitive to understand.  In this case, we have selected High Point and Low Point:

Finally, our table looks like this:

Try using sparklines in your reports next time. You will absolutely love it!

 

Advanced Excel Charts #2 – Gantt Charts

If you have previously worked in the field of Project Management, then you must be familiar with Gantt charts. These charts are one of the most popular and useful ways to track activities of a project against time.

Gantt charts are essentially bar charts. Each activity represents a bar. The length of the bar represents the duration of the activity or a task.

To understand Gantt Charts, let us take up a problem statement. There are two roommates – Joey and Chandler. Due to the recent pandemic, they usually stay at home and they decided to build themselves a home entertainment unit. Chandler being interested in analytics and visualization turns this small project into a Gantt chart representation. Let us see how.

You may refer to the video or the steps given below according to your convenience:

Step 1 – Select the Stacked Bar Chart

Since Gantt charts are basically bar charts, let us first select one:

Go to Insert -> 2D Bar -> Stacked Clustered Bar

Step 2 – Put Categories in Reverse Order

You may notice that the sequence of events is actually in a manner opposite to the required sequence. So let’s reverse it.

Select the categories on the axis and right-click -> Go to Format Axis:

Now simply select the tickbox  – “Categories in reverse order”:

 

Step 3 – Make Date Bar chart invisible

If you observe the chart properly, you’ll notice that the blue chart represents the start date. We will remove the color of this chart to attain the desired Gantt chart.

Select the blue chart -> right click ->Format Data Series:

Now go to the fill section and select – No fill:

Now the chart looks something like this:

Step 4 – Select the minimum and maximum bounds

The date in our chart starts from 29 April which we clearly don’t want as it is utilizing unnecessary space. So let us select the bounds for our Gantt Chart dates.

Select the date (Horizontal Axis) -> Right-click -> Format Axis:

Now we simply need to provide a minimum and maximum bound date:

We have made the following chart:

Step 5 – Format your Chart

We have made our Gantt chart but we won’t be showing it to someone unless it looks appealing to the eyes so we’ll do a bit of formatting to make our chart look cool.

Here we have done a few things:

  • Removed the axis
  • Changed the title and
  • Added Tick Marks

Congratulations! You have made your first Gantt Chart! Joey and Chandler must be enjoying their new entertainment unit.

 

Advanced Excel Charts #3 – Thermometer Charts

Thermometer charts are really interesting and they disburse crucial information in a very crisp manner. These charts are great in visualizing the actual value and the target value. It is useful in visualizing sales, the footfall on the website, etc. Let us understand the charts better with a problem statement.

Every New Year’s, people tend to make resolution. Jake had taken a resolution to complete 48 books during the year. That is around 1 book every week. Let us understand how did Jake perform.

You can follow along with the steps and refer to the video for better understanding:

 

Step 1 – Select Clustered Charts

Select the Percentage data as shown below:

Go to Insert  -> 2D Column -> Clustered Column:

Here is our chart:

Step 2 – Combine the Column

Go to Chart Design -> Select Switch Row / Column and click OK:

Now, right-click the Achieved % column -> Go to Format Series ->Select Secondary Axis:

 

This will overlap both the columns into one column.

Step 3 – Select minimum and maximum

We have our overlapping columns but in different axes so let us give it a uniform bounding.

Select the percentages (Vertical axis values) and right-click -> Format Axis:

Now select the minimum bound as 0.0 and maximum bound as 1.0:

Step 3 – Format the chart

Select the Goal % chart and right-click ->  Format Data Series:

Go to Fill options -> Select No Fill and Solid line border:

Now we have a chart that finally resembles a thermometer chart. All we need is some additional formatting.

Here, we have followed the below steps for formatting:

  • Remove the gridlines
  • Rearrange the size of the chart
  • Give the chart title
  • Adding tick marks

Perfect!

 

End Notes

In this article, we covered three beautiful Excel charts of different kinds to help you become an efficient analyst. 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.

Ram Dewani 26 Aug 2021

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Clear

Harish Nagpal
Harish Nagpal 17 Jun, 2020

Nice explanation Ram

Mantej Singh H Dhanjal
Mantej Singh H Dhanjal 17 Jun, 2020

I liked how well you have explained everything

Susan
Susan 18 Jun, 2020

👌

Ayub Njoroge
Ayub Njoroge 18 Jun, 2020

You are god send.So nicely done. Straight up to the point

Akintomide Hannah
Akintomide Hannah 18 Jun, 2020

Good break down.

Debashis Bhattacharyya
Debashis Bhattacharyya 19 Jun, 2020

Very informative

Manju
Manju 21 Jun, 2020

This is very helpful. Thank you so much!

Raphael Iziga
Raphael Iziga 23 Jun, 2020

Please I will need some information about how to perform conjoint analysis. I would appreciate a worked example aswell.Thank you

Alison
Alison 23 Jun, 2020

Thanks for the great info! I'm always telling my clients about the importance of including a storytelling aspect in their content marketing in order to increase engagement and conversions, but data is still important, too. That's why it's so great to see content like this that recognizes the value of both and combines them, rather than treating them like they're mutually exclusive.

Data Visualization
Become a full stack data scientist
  • [tta_listen_btn class="listen"]