Occasionally, I’ll see an Excel chart that tells such a fascinating story that I want to frame it.
A great businessperson can often take an entire slide deck and turn it into one dual-axis Excel chart.
Can it be possible to summarize an entire story into one simple chart?
Yes. The basic motivation behind data visualization is to take a large amount of information and transform it into something you can understand in just a few seconds.
And you’d think that incredible charts would be difficult to build, but like most things in Excel, you can pick the skill up in about 20 minutes with some instruction.
By the end of this blog post you’ll be able to easily visualize Excel data into intuitive charts and tables.
Motivations behind data visualization (Excel charts and tables)
Often we need to extract the story out of a large Excel data set; however, people often misunderstand how to tell most straightforward and simple story of the data using data visualizations.
The core purpose of visualizing data is to summarize it in a way to simplify the data, tell a story, and ultimately create intuitive insights.
To take a large amount of information and transform it into something you can understand in just a few seconds.
Simply visualizing data does not necessarily simplify it, if fact, sometimes it does the opposite.
We’re going to cover three of the most common ways to visualize data in Excel:
Now let’s discuss how to choose when to use a chart (graph) vs. a table to present a data set.
How to choose between Excel tables and charts
Often we are tasked with summarizing a data set to help someone else understand it. Most often, we’ll have the option to present data in a simple Excel table, or visualize it in a chart.
In order to decide whether an Excel table or chart is appropriate, there are four things we must first consider:
1. Big data or small data?
As a rule of thumb, you don’t generally want to visualize small data in a chart.
Small data would be just a few data points, maybe a 5, 10, or 20 pieces of data.
Big data would represent hundreds, thousands, or even millions of records. Big data is impossible to understand without a chart or deeper analysis.
2. Will your user ask you for the underlying data if you create a chart?
If the answer is yes, then you’ve created a chart that is less helpful than the actual data by itself.
If your end-user looks at a chart and tries to reverse engineer it to figure out the underlying data - you’ve been unsuccessful. Just give them the data upfront and don’t bother with a chart.
3. Have you underestimated your audience?
The next point to remember is not to underestimate your audience.
People are smart. And often the people that you’re presenting data to are the people you report to. Don’t oversimplify things.
A helpful way to remember that raw numbers are easy to consume is to look at the ESPN website.
ESPN is packed with data. All kinds of statistics. No charts. But the website is built for sports fans, not academics.
The thing to remember is that regular people can easily consume a tremendous amount of raw data.
4. What story are you telling?
The last piece is – what story are you telling?
Make sure your visualization transforms the data into a simple story.
Example data set – should we use a table or a chart to visualize this data?
So in our example, we have four records that contain a date, a sales number, and a gross profit number. We’re trying to decide whether to present this data in a table or a chart.
Which presentation is better?
Let’s think about our four questions:
Well, we’re dealing with small data here. And if we give our users the chart, will they ask for the underlying data? They probably will.
Have we underestimated our audience? Which presentation tells a complicated but more nuanced story?
And what is the story we want to tell?
The rising revenue is easy to see in a chart or a table; however, the volatility in the gross profit margin is easier to see in the table.
So in this case, I would use a table to present the data.
Now let’s learn how to visualize a data set using a basic Excel chart.
Creating a basic chart
In this section, we will visualize our sample data set into a basic chart.
To begin, let’s highlight our entire sample data set including the headers. Then, click the Insert tab and find the Recommended Charts option.
This will quickly give you an idea of what different charts will look like.
Let’s select the third option - a clustered column chart.
Here is our initial Excel chart.
The first thing I like to do when working on a chart is increase the size of my chart by dragging the corner down and to the right.
This makes it much easier for me to design.
Pro tip - if you lose the chart design menu by clicking a cell off the chart, you can always come back to the menu by double clicking the chart again.
Now let’s look at some ways to make this chart more clear.
First, in the top left corner of the screen click add chart element and let’s add a title for our vertical axis.
Go to Axis Title, and select Primary Vertical.
Ok. The title I’ll add is ($ in 000s) which means “the financial numbers in this chart are in thousands”.
Double click the box on the vertical axis, delete the text and write "$ in 000s".
But I want the size to be bigger, so I’ll go to the home tab and increase the size to 12. Better.
Now our chart needs a title. Double click the chart title and let’s write “Sales & Gross Profit”.
Now double click the chart again. Is there anything else we should add?
Let’s look at Gridlines. Click add chart element, go to Gridlines.
Ok, maybe we switch to Primary Major Vertical. That will help the chart look a bit cleaner.
Now we are done. This chart is ready to present.
It is a clean, simple story.
In our next section we’ll look at creating a dual axis Excel chart visualizing financial and web traffic data at the same time.
Creating a dual axis chart
In this section, we’ll learn how to design a dual axis chart.
A dual axis chart visualizes two types of data on the same chart.
The two different types of data must share a horizontal axis data type, which in this case, and most frequently, is time.
Each data type will have its own vertical axis, so we will have a primary and secondary vertical axis (“dual” axis!).
We’ll design a dual axis chart that visualizes two types of data on the same chart:
Both of these types of data occur during the same time, so we can plot them together on the same chart.
Our first step is to highlight the data set including the headers.
Next, go to the Insert tab and click Recommended Charts.
Now, click the All Charts tab, and we’re going to click the Combo chart, which is the last option.
This is a dual axis chart (a combo chart).
Now we must tell Excel which type of data needs its own vertical axis.
In our case, let’s click the Secondary Axis box next to conversion rate. Now click ok.
Now increase the size of the chart.
You can see here that the vertical axis on the left, our primary vertical axis, is showing a financial measurement.
While the vertical axis on the right, our secondary vertical axis, is showing the conversion rate.
Let’s first add a chart title. How about, “Financial Performance vs. Conversion Rate”.
Now let’s take a look at the scaling of the secondary vertical axis (conversion rate).
Is 0.0% - 3.0% the correct range?
Let’s adjust it to test other potential options.
Double click the chart.
Then, to the right, in format chart area, click the down arrow next to Chart Options.
This is the place where you can adjust many small design elements in a chart.
We want a very intuitive story, so designing the right chart can take some creativity and experimentation.
Lets choose Secondary Vertical Axis.
Now we don’t want fill, effects, size, we want Axis Options. Click on it once.
Now expand Axis Options by clicking the sideways triangle next to it.
Here you’ll see the minimum and maximum bounds.
The maximum is the highest value on the secondary vertical axis. 3%.
Let’s try adjusting it to 5%.
Click out of the box.
The conversion rate like is out of place with the chart scaled at 5%.
I think we had it right the first time.
Let’s change back to 3%.
Let’s try something else.
In the Units section below, we can also change the scaling.
We’re currently using 0.5% increments on our conversion rate axis. Let’s change it to 0.25% for the “Major” units.
Click into another box to apply the changes (I clicked into the "Minor" box).
Now, let’s click out of the chart altogether.
Let’s add some more gridlines.
Double click the chart, now to navigate to Add Chart Element, Gridlines, and I think Primary Major Vertical Gridlines would be quite helpful. Click ok.
The dual axis Excel chart looks ready.
It tells a very interesting story about how the website conversion rates may have affected the profit margins of the business.
The chart tells a very complex story in just a few seconds.
Understanding when to use charts vs. tables, and how to build beautiful dual axis charts are critical in the business environment.
Luckily, these skills are straightforward and easy to learn – you should be a pro now!
Remember, when you have a data set, you must choose the best way to present the data.
You want to simplify the data and tell a story with it.
Don’t forget the four considerations!
Tables are great for small data sets (~10-20 pieces of data).
Charts work well to tell the story behind big data sets (hundreds or millions of records) with two types of data (ex: time vs. revenue).
Dual Axis Charts work well to tell the story behind big data sets with three types of data (ex: time vs. revenue vs. website conversion rates).
To go deeper with the rest of Microsoft Excel, join the army of students on Udemy.com taking my video-based course: Become An Excel Power User in 2.5 Hours. Take a look below!