Clustered Column Charts in Excel: How to Create and Customize Them

Additional Techniques and Features for Clustered Column Charts

Now, let's explore some additional techniques for creating specific varieties of clustered column charts in Excel.

How to change the clustering criterion for a clustered column chart in Excel

What if we want to change the clustering (grouping) criterion for our chart? 

By default, each group of columns refers to a specific category from the column containing category names. In contrast, each column in a group of columns represents a particular type of item taken from the corresponding column header. 

However, in some cases, we may want to swap the categories with the types of items. 

Returning to our cats, dogs, and hamsters example, let's say that we need to cluster them by animal type rather than by pet shop:

1. Select the plot area of your clustered column chart and right-click on your mouse.
2. Select “Change Chart Type…”
3. In the pop-up window, click on the second preview image (to the right of the one selected by default).
4. Press “OK”.

Opening the Change Chart Type window. Image by Author

Changing the clustering criterion for a clustered column chart. Image by Author

How to insert a 3D clustered column chart in Excel

Creating a 3D variant of a clustered column chart in Excel is as easy as making a regular one:

1. Select the columns of your Excel table containing the categories and values to be plotted, including the headers of those columns. If the columns in interest aren't adjacent, press Ctrl on your keyboard before selecting the next column.
2. Open the “Insert” tab on the Excel ribbon.
3. Click on the “Insert Column or Bar Chart” button of the “Charts” group.
4. Click on the “3-D Clustered Column” button of the “3-D Column” group.

Remember, though, that creating 3D clustered column charts isn't considered a good practice, as discussed further in the section on “Best Practices for Clustered Column Charts.”

Creating a 3D clustered column chart. Image by Author

A 3D clustered column chart. Image by Author

How to insert a clustered column and line combo chart in Excel

Sometimes, we may want to combine a clustered column chart with a line chart on a single visual. This type of chart is called a combo chart or a combination chart. 

A combo chart is commonly used to show the evolution of different types of data over the same period. 

Therefore, our table with sold pets isn't the best use case for creating a combo chart since it doesn't contain either a time series or data types that are different by nature (such as sales and prices). 

Let's use it anyway for learning purposes to see how we can build a clustered column and line combo chart in Excel:

1. Select the columns of your Excel table containing the categories and values to be plotted, including the headers of those columns. If the columns of interest aren't adjacent, press Ctrl on your keyboard before selecting the next column.
2. Open the “Insert” tab on the Excel ribbon.
3. Click on the “Recommended Charts” button of the “Charts” group.
4. Open the “All Charts” tab in the pop-up window.
5. Select the “Combo” option from the list on the left side of the pop-up window.
6. Select the necessary chart type for each data series in the lower part of the pop-up window: either “Clustered Column” or “Line.”
7. If needed, tick the “Secondary Axis” box for data series with a different scale. Ensure consistency: the secondary axis should relate to all clustered column series or all line series.
8. Press “OK”.

Creating a clustered column and line combo chart. Image by Author

Consider taking the Excel Fundamentals skill track to fully utilize Excel for visualizing and analyzing data.

Best Practices for Clustered Column Charts

Let's outline some best practices for creating clustered column charts in Excel. 

Fortunately, Excel takes care of many of them by default, including adding an automatic legend, avoiding redundant elements, opting for a colorblind-friendly palette, starting the y-axis from zero, and making clustering obvious. 

For the others, or if you want to adjust the defaults manually, it's recommended to follow the below practices to avoid typical caveats:

• Avoid too many clusters or too many data series in each cluster. Having any of these issues or both of them makes your clustered column chart cluttered and renders it difficult to capture tendencies across the clusters and/or types of items.
• Always add the legend. Given the various categories and types of items on a clustered column chart, it's crucial to keep the information well-labeled. While the cluster names appear on the x-axis, the types of items should be included in the chart's legend.
• Use clear chart and axis titles. Your titles should be informative and concise so that your target audience can immediately grasp what your chart is about.
• Avoid 3D clustered column charts. While we learned earlier how to create a 3D clustered column chart in Excel, remember that 3D variants of charts are almost always to avoid. This also applies to many other types of charts, except for sporadic cases (e.g., surface plots). The reason is that 3D distorts reality and impedes the correct interpretation of graphs.
• Avoid long labels on the x-axis of your chart. If your cluster names are too long, which results in too-long labels on the x-axis of your clustered column chart, consider opting for a clustered bar chart instead.
• Avoid redundant elements in the plot area. Gridlines, trendlines, data labels, pattern or gradient fill, borders, and special effects such as shadows or soft edges should be used cautiously and only when they add value to your data analysis.
• Avoid counterintuitive colors and their combinations. Keep in mind the common visual standards of your audience. For example, if the pet stores from our table were selling, say, wolves and foxes rather than cats, dogs, and hamsters, it wouldn't be a good idea for you to use orange color for wolves and grey for foxes. Doing so would be misleading for your chart's end users and result in wrong conclusions.
• Opt for a colorblind-friendly palette. This is especially important if you're going to present your work's results to a wide audience. The insights from your charts must be easily accessible to everyone.
• Start the y-axis from zero. The columns in a clustered column chart, like in a regular column chart, are supposed to show the absolute magnitude of the values they represent. Hence, cutting the y-axis is a bad practice for this chart type.
• Ensure that the columns within a cluster are closer to one another than the columns in different clusters. If you want to adjust the default gap width manually, make sure that the clusters remain well-distinguishable after your modifications.

A clustered column chart with too many clusters. Image by Author

A clustered column chart with redundant elements. Image by Author

A clustered column chart with counterintuitive colors. Image by Author

A clustered column chart with a cut y-axis. Image by Author

A clustered column chart with unobvious clustering. Image by Author

Conclusion

To wrap up, this article discussed what a clustered column chart is, its purpose, its key features, and why Excel is a good choice for creating this type of plot. 

We also explored how to create and customize a clustered column chart in Excel, including its specific types and what best practices to follow when making them.

If you enjoyed this article, you may find the Data Visualization in Excel and Data Analysis in Excel courses helpful for continued learning.