How to Create and Format a Combo Chart in Excel

How to Create a Combo Chart in Excel

Creating a combo chart in Excel involves a few simple steps. 

• Select the data in your Excel worksheet.

• Open the Insert tab and go to the Charts group.

• Click on the Recommended Charts button.

• Open the All Charts tab.

• Select the Combo option at the end of the list.

• Select a chart type for each data series. You will likely need to select a secondary axis for one of them.

• Press OK.

Selecting data. Image by Author

Opening recommended charts. Image by Author

Opening combo chart option. Image by Author

Setting data series for a combo chart. Image by Author

How to Format a Combo Chart in Excel

Once you have created your combo chart, the next step is to customize its elements to enhance clarity and visual appeal. Here's how you can add or remove chart elements to better represent your data:

How to add or remove chart elements

• Select the chart area.
• Press the Chart Elements button.
• Select the chart elements you want to be displayed.
• You can also hover over the name of the chart element until a drop-down appears to refine your choice.

Selecting the chart area in Excel. Image by Author

Opening the available chart element options in Excel. Image by Author

How to change chart style and color scheme

• Select the chart area.
• Press the Chart Styles button.
• Select a suitable style for your chart on the Style tab.
• Select a suitable color scheme for your chart on the Color tab:

Selecting a color scheme. Image by Author

How to add or remove filters

• Select the chart area.
• Press the Chart Filters button.
• To add a filter, deselect the unwanted series and/or categories on the Values tab and press Apply.
• To modify the filter, introduce the necessary modifications on the Values tab and press Apply.
• To remove the filter, Select All for both series and categories on the Values tab and press Apply:

Opening the available chart filter options in Excel. Image by Author

Adding a chart filter for a chart in Excel. Image by Author

Having clean data is a crucial prerequisite for building meaningful data visualizations and being able to extract from them precious insights. The course on Data Preparation in Excel will help you understand how to prepare Excel data through logical functions, nested formulas, lookup functions, and pivot tables.

How to format data series

• Select the data series you want to format, right-click and select Format Data Series. 
• For a line chart, you may want to adjust the Color, Transparency, Width, and Dash type on the Line sub-tab of the Fill & Line tab. You may also want to adjust the marker Type, Size, Color, and Border on the Marker sub-tab of the Fill & Line tab.
• For a bar chart, you may want to adjust the bar Fill, Color, Border color, Transparency, Width, and Dash type on the Fill & Line tab.
• You may also want to adjust the bar Gap Width on the Series Options tab.

Opening the pane to format data series. Image by Author

Adjusting line properties for a line chart. Image by Author

Adjusting marker properties for a line chart. Image by Author

Adjusting bar properties for a bar chart. Image by Author

Adjusting bar gap width for a bar chart. Image by Author

How to change legend position

• On the chart area, select the legend, right-click on your mouse, and select Format Legend. The Format Legend pane will appear on the left side of your Excel worksheet.
• Select a suitable legend position on the Legend Options sub-tab of the Legend Options tab.

Opening the pane to format the legend. Image by Author

Selecting the legend position. Image by Author

How to customize text

• Select the chart element containing the text you want to customize, right-click and select Font.
• On the Font tab of the pop-up window that appears, you may want to adjust the font itself and/or font Style, Size, and Color.
• Press OK.

Opening font options for a chart element. Image by Author

Adjusting font options for a chart element. Image by Author

Considerations When Using Combo Charts

While a combo chart in Excel has numerous advantages, it also has some intrinsic weaknesses that we should consider. 

• Scale/Magnitude Differences: The use of a secondary axis can cause discrepancies in the perceived impact of data series. A change in one scale may not correspond to a similar magnitude change in the second scale, leading to potential misinterpretation of the data.
• Information Overabundance: Combining more than two chart types in a combo chart can result in a cluttered and visually overwhelming presentation. This makes the chart difficult to interpret and reduces its effectiveness.
• Restricted Choice of Chart Types: While Excel offers many chart types for building combo charts, it does not include every possible chart type. This limitation can sometimes necessitate the use of other software or tools for more specialized charting needs.

Conclusion

In this tutorial, we learned what a combo chart in Excel is, when and why it's helpful, what key features it has, what chart types can be combined on it, how to create and format a combo chart in Excel, and what potential issues we can encounter when working with this kind of data visualization.

To propel your Excel skills to the next level, consider taking an all-inclusive and beginner-friendly Excel Fundamentals skill track that will teach you everything you need to know to carry out data analysis in Excel in real-world scenarios.