Overview
- Advanced Excel charts are a great way to create effective and impactful stories for our audience..
- Learn it 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 started my analytical journey.. In fact, I know that many newcomers are obsessed with this method of approaching things. I have some news for you: in reality, this is not the core quality of a good analyst.
Stakeholders couldn't understand what you were trying to communicate to them. There was a missing link across the stage: the narration.
I was able to level up by improving my storytelling skills. To pass the story on to our management team, a key skill to learn is understanding different types of charts. As usual, we can explain most things with a simple bar chart or scatter plot, but they don't always satisfy the need.
There is no one-size-fits-all chart and that's why, to create intuitive visualizations, it is imperative that we understand the different types of graphics and their use. And Excel is the perfect tool for creating powerful yet powerful charts for our analytics audience..
In this article, I am going to discuss 3 powerful and important advanced Excel charts that will 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 reading the previous articles to become a more efficient analyst.:
I encourage you to check out the resources below if you are a beginner to Excel and Business Analytics:
Table of Contents
- Excel advanced charts n. ° 1: sparklines
- Excel advanced charts n. ° 2: gantt charts
- Excel advanced charts n. ° 3 – Thermometer charts
Excel advanced charts n. ° 1: sparklines
I'll start with one of my favorite chart types: sparkline graphics. These graphics really help me create amazing dashboards!! Then, What are sparklines?
Sparklines are typically small in size and fit into a single cell. These charts are wonderful for visualizing trends in your data., such as seasonal increase or decrease in value.
In Microsoft Excel, have 3 different types of minigraphs: line, column and gain / lost. Let's see how we can make a sparkline with this problem statement:
You are an analyst in a product-based company and you want to understand the performance of salespeople during the first six months of the year. Decide to use sparklines for this.
Follow the steps or watch the video to better understand sparklines:
Paso 1: choose your sparkline type
You will first need to select the type of sparkline you want to plot. In our case, we will use a line chart.
Go to Insert -> Line:
Paso 2: create minigraphs
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:
Paso 3: desired format
We have the basic sparklines ready. We can format it so that the graph is more intuitive to understand. In this case, we selected Decisive point Y Low point:
Finally, our table looks like this:
Try using sparklines in your reports next time. You'll love it!
Excel advanced charts n. ° 2: gantt charts
If you have previously worked in the field of project management, must be familiar with Gantt charts. These charts are one of the most popular and useful ways to track project activities over time..
Gantt charts are essentially bar charts. Each activity represents a bar. The length of the bar represents the duration of the activity or task.
To understand Gantt charts, Let's consider a statement of the problem. There are two roommates: Joey y Chandler. Due to the recent pandemic, they usually stay at home and decide to build themselves a home entertainment unit. Chandler's interest in analytics and visualization turns this small project into a Gantt chart representation. Lets see how.
You can refer to the video or steps below as per your convenience:
Paso 1: select the stacked bar chart
Since Gantt charts are basically bar charts, let's first select one:
Go to Insert -> 2D bar -> Stacked grouped bar
Paso 2: put the categories in reverse order
You may notice that the sequence of events is actually opposite to the required sequence. So let's reverse it.
Select the categories on the axis and right click -> Go to Axis Format:
Now just select the checkbox – “Categories in reverse order”:
Paso 3: make date bar chart invisible
If you look at the graph correctly, you will notice that the blue graphic represents the start date. We will remove the color from this chart to get the desired Gantt chart.
Select the blue chart -> the right button of the mouse ->Format the data series:
Now go to the fill section and select – Unfilled:
Now the graph looks like this:
Paso 4: select minimum and maximum limits
The date in our chart begins on 29 of April, what we clearly don't want, as it uses unnecessary space. Then, let's select the limits for the dates of our Gantt chart.
Select the date (horizontal axis) -> The right button of the mouse -> Format axis:
Now we simply need to provide a minimum and maximum deadline:
We have made the following table:
Paso 5 – Format your chart
We have created our Gantt chart, but we won't show it to anyone unless it looks attractive to the eye, so we will format a bit to make our diagram look good.
Here we have done some things:
- Removed shaft
- Changed the title and
- Tick marks added
Congratulations! You have created your first Gantt chart!! Joey and Chandler must be enjoying their new entertainment unit.
Excel advanced charts n. ° 3 – Thermometer charts
Thermometer charts are really cool and distribute crucial information in a very neat way. These charts are great for visualizing actual value and target value.. It is useful to visualize sales, step on the website, etc. Let's better understand the graphs with a problem statement.
Every new year, people tend to make a decision. Jake had made up his mind to complete 48 books during the year. That's around 1 book per week. Let's understand how Jake acted.
You can follow the steps and refer to the video to understand it better:
Paso 1: select grouped graphics
Select the percentage data as shown below:
Go to Insert -> 2D column -> Grouped column:
Here is our graph:
Paso 2: combine column
Go to Graphic design -> Select Change row / column and click OK:
Now, right click on the Accomplished % column -> Go to Format string -> To select Secondary shaft:
This will overlap both columns in one column.
Paso 3: select minimum and maximum
We have our columns overlapping but on different axes, so let's give it a uniform limit.
Select the percentages (vertical axis values) and right click -> Format axis:
Now select the minimum limit as 0.0 and the maximum limit as 1.0:
Paso 3: format the chart
Select the Target % chart and right click -> Format the data series:
Go to Fill options -> To select Unfilled Y Solid line border:
Now we have a table that finally looks like a thermometer table. All we need is an additional format.
Here, we have followed the steps below to format:
- Remove grid lines
- Rearrange the chart size
- Give the title of the chart
- Add tick marks
Perfect!
Final notes
In this article, we cover three beautiful excel charts of different types to help you become an efficient analyst. Hope these graphics help you create amazing visualizations and save you a lot of time. and impress your boss. 🙂
Let me know your favorite Excel charts that you think make for a great visualization.
