What chart is best for comparing data?

You’ve got data and you’ve got questions, but what's the best way to visualize that data to get the answers you need? Transforming data into an effective visualization or dashboard is the first step towards making your data make an impact.

As Henry D. Hubbard, Creator of the Periodic Table of Elements said, “There is magic in graphs. The profile of a curve reveals in a flash a whole situation — the life history of an epidemic, a panic, or an era of prosperity. The curve informs the mind, awakens the imagination, convinces.”

Types of Charts and Graphs

Bar Chart

Bar charts are one of the most common data visualizations. You can use them to quickly compare data across categories, highlight differences, show trends and outliers, and reveal historical highs and lows at a glance. Bar charts are especially effective when you have data that can be split into multiple categories.

Line Chart

The line chart, or line graph, connects several distinct data points, presenting them as one continuous evolution. Use line charts to view trends in data, usually over time (like stock price changes over five years or website page views for the month). The result is a simple, straightforward way to visualize changes in one value relative to another.

Pie Chart

Pie charts are powerful for adding detail to other visualizations. Alone, a pie chart doesn’t give the viewer a way to quickly and accurately compare information. Since the viewer has to create context on their own, key points from your data are missed. Instead of making a pie chart the focus of your dashboard, try using them to drill down on other visualizations.

Maps

Maps are a no-brainer for visualizing any kind of location information, whether it’s postal codes, state abbreviations, country names, or your own custom geocoding. If you have geographic information associated with your data, maps are a simple and compelling way to show how location correlates with trends in your data. For example, insurance claims by state, product export destinations by country, car accidents by zip code, and custom sales territories.

One of the most frequently asked questions in the data visualization world is, “What type of chart do I use to compare data in Excel?”.

Following are the best chart types for comparing data in Excel:

1. Column chart
2. Bar chart
3. Line chart
4. Combination chart

When to use a column chart for comparing data

If you want to compare two to four data series, then use a clustered column chart:

Alternatively, avoid creating a column chart with more than four data series.

For example, the following chart contains six data series, and it has started looking cluttered:

If you want to create a column chart containing more than four data series, try switching rows and columns of the chart and then check whether it makes any difference.

To do that, follow the steps below:

Step-1: Right-click on the column chart whose row and column you want to change.

Step-2: Click on ‘Select Data’ from the drop-down menu:

Step-3: Click on the ‘Switch/Row Column’ button:

Step-4: Click on the ‘OK’ button. The column chart will now look like the one below:

Now, this chart is much easier to read and understand.

My step-by-step blueprint for selecting the best Excel charts for data analysis and reporting (40 pages)

Get the FREE e-book on Best Excel Charts For Data Analysis And Reporting (40 Pages)

Yes I want the FREE ebook

Do not use a column chart when the data series you want to compare have different units of measurement 

For example, in the chart below, ‘Orders’ and ‘Conversion Rate’ have different units of measurement. The data series ‘Orders’ is of type number. Whereas the data series ‘Conversion Rate’ is of type percentage:

The best thing to do when the data series you want to compare have different units of measurement is to use the combination chart:

If the values of one data series dwarf the values of the other data series, then do not use the column chart to compare two data series.

For example, in the chart below, the values of the data series ‘Website Sessions’ completely dwarf the values of the data series named ‘Orders’:

The best thing to do in such a situation is to use the combination chart:

If you want to compare data series along with their composition, then use a stacked column chart:

If you want to compare data series along with their composition but the overall size of each data series is not important, then use a 100% stacked column chart:

When to use a bar chart for comparing data

Use a bar chart when the axis labels are too long to fit nicely in a column chart:

When to use a line chart for comparing data

Use a line chart when you want to compare data trends, especially long term trends between the values of the data series:

When to use a combination chart for comparing data

A combination chart is a combination of two or more charts. For example, the combination of a column chart and a line chart.

Use a combination chart when:

#1 You want to compare two or more data series that have different units of measurement:

#2 You want to compare two or more data series that are not of comparable sizes:

Other Articles on Excel Charts

• Which Chart Type Works Best for Summarizing Time-Based Data in Excel
• Five Advanced Excel Charts and Graphs
• Data Visualization in Excel Tutorial
• Best Excel Charts Types for Data Analysis, Presentation and Reporting
• What type of chart to use to compare data in Excel

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own." What graph is best for comparing data?

What is the best way to compare two sets of data?