How to Create and Format a Combo Chart in Excel

Data analysts often need to show different types of data on the same chart to compare them and reveal hidden trends and patterns. In this tutorial, we'll explore a special type of chart that is perfect in such cases, called combo charts. We'll explore their features, types, uses, creation, formatting, and strengths and weaknesses.

If you would like to learn or brush up on the basics of Excel before we get started, our comprehensive Introduction to Excel course is a helpful companion. 

What Is a Combo Chart?

A combo chart, also known as a combination chart, refers to charts that combine two or more chart types, such as line, bar, or area charts, into a single visual. Most often, a combo chart displays an evolution of different types of data over the same period, which makes this kind of visualization particularly helpful for identifying essential discrepancies, tendencies, trends, and patterns.

Key Features of a Combo Chart

What distinguishes a combo chart? While some of its features may be readily apparent, others are more subtle. Let's delve into its key characteristics.

• Multiple Chart Types in One Visual: The defining feature of a combo chart is the inclusion of more than one (different) chart type within the same visual, such as combining a line chart with a bar chart. 
• Multiple Data Series: Combo charts display multiple data series in the chart legend and when interacting with the chart area, similar to stacked bar charts and multi-line charts.
• Secondary Axis: Combo charts often feature a secondary axis for different data series with varying scales, which is also common in multi-line charts.
• Differentiation by Colors and Labels: Data series in combo charts are easily distinguishable by different colors, transparencies, or data labels, a feature shared with other charts like stacked bar and area charts.

To quickly explore Excel's various data visualization options that can help you analyze and interpret your data, feel free to refer to this tutorial: Visualizing Data in Excel.

Why Create a Combo Chart in Excel?

Combo charts offer a lot of advantages and are very helpful for a number of reasons. In this section, we will outline the main reasons in favor of creating combo charts in Excel.

• Comparing Different Data Series: A combo chart allows us to trace the evolution of multiple data series simultaneously. Different chart types in the same visual facilitate effective comparisons, such as a line chart for price changes alongside a bar chart for product sales.
• Highlighting Specific Trends and Patterns: Combo charts help capture major trends and patterns by comparing multiple data series. These insights are more informed and sometimes unique to combo charts, as they cannot be seen in single data type plots.
• Enhancing Data Presentation: Combo charts improve the data-ink ratio, leading to clearer and more effective visualizations by reducing unnecessary elements. They combine multiple chart types into one visual, maximizing information within a single view and utilizing Excel's features to enhance appearance and readability.
• Improving Decision-Making: The comprehensive view provided by combo charts supports well-grounded, data-driven decisions. This aids in planning sound business strategies and offers a broader vision for future market changes.

Popular Chart Combinations

Let's see now what kind of charts we can combine on a combo chart. We'll use a publicly available Kaggle dataset, Lamborghini Sales and Stocks. For now, we are not interested in the real meaning and actual values of the data series. Rather, we are practicing creating different chart-type combinations.

Line chart + bar chart

Combining a line chart with a bar chart is great for showing a trend alongside individual values.

Line and bar chart combo. Image by Author

Multiple line chart + bar chart

Combining multiple line charts with a bar chart is useful for comparing several trends against a backdrop of individual values.

Multiple line and bar chart combo. Image by Author

Area chart + bar chart

Combining an area chart with a bar chart can emphasize the cumulative total and discrete data points.

Area and bar chart combo. Image by Author

Stacked area chart + bar chart

Combining a stacked area chart with a bar chart highlights the composition of values while also showing individual bar values.

Stacked area and bar chart combo. Image by Author

Line chart + grouped bar chart

Combining a line chart with a grouped bar chart is effective for comparing trends across multiple categories.

Line and grouped bar chart combo. Image by Author

Line chart + stacked bar chart

Combining a line chart with a stacked bar chart is useful for displaying the trend line while also showing the breakdown of stacked categories.

Line and stacked bar chart combo. Image by Author

Line chart + 100% stacked bar chart

Combining a line chart with a 100% stacked bar chart is ideal for showing the trend while emphasizing the proportion of categories that make up the whole.

Line and 100% stacked bar chart combo. Image by Author

For a deeper dive into various techniques and nuances of visualizing data in Excel, the Data Visualization in Excel course is a great place to start.

Best use cases table

Using the right chart combination in a combo chart can greatly enhance data interpretation. Here's a heuristic to help you decide which chart combination to use: