How to Insert a Clustered Column Chart in Excel

Without a doubt, charts in Excel are an outstanding tool to portray any data graphically instead of showing a complex table with lots of fields. More importantly, we may use a clustered column chart to visually represent and compare visually many variables at a time. In this article, I’ll discuss how to insert a clustered column chart in Excel with some meaningful examples that might be helpful for you.


Download Practice Workbook


Basics of Clustered Column Chart

Clustered Column Chart – a type of chart presented in vertical columns – exhibits many variables in columns and also provides the comparison of one set of variables with another set of variables. We can simply use a column chart if we have one variable in our dataset. But, if there are a set of variables and numerous data series, a clustered column chart might be a better option to deal with such types of datasets.

And this is the basic difference between a column chart and clustered column chart.


Benefits of Clustered Column Chart

  • Allows comparison between sets of variables.
  • Provides a presentation of data graphically over a time period.
  • Visually viable for representing multiple data series across the category.

Drawbacks of Clustered Column Chart

  • Graphically complex if unlimited categories are added to the chart.
  • Not suitable for larger datasets.

4 Suitable Methods to Insert a Clustered Column Chart in Excel

In the first example, I’ll show the process of inserting a clustered column chart elaborately. Later, I’ll demonstrate 3 necessary examples with some key facts.

Let’s dive into the main section.


1. Clustered Column Chart of Region-Wise Quarterly Sales

Quarterly sales of some products are given based on the five regions of the U.S. Now, we are going to insert a clustered column chart for this dataset in a step-by-step process.

Region-wise Quarterly Sales

Step 1: Inserting Clustered Column Chart

⏩ Firstly, select the whole dataset.

⏩ Go to Insert tab > Insert Column/Bar Chart  > choose Clustered Column from 2-D Column.

How to Insert a Clustered Column Chart in Excel

Then you’ll see the following initial chart.

How to Insert a Clustered Column Chart in Excel

Step 2: Switching Row/Column

If you look closely at the initial chart, you might see that data series (regions) are existing in the Series (horizontal axis) instead of the Quarter (Q1,Q2…). So, we need to switch the row/column.

⏩ For doing this, choose Select Data by right-clicking while keeping the cursor over the initial chart.

How to Insert a Clustered Column Chart in Excel

⏩ Next, click on Switch Row/Column, and press OK.

How to Insert a Clustered Column Chart in Excel

Now, the output is as follows.

How to Insert a Clustered Column Chart in Excel

Step 3: Adding Axis Title & Chart Title

Additionally, we need to add axis title and chart title to make it visually viable. However, you might visit the Charts Elements of Excel article to explore the details of chart elements.

⏩ If you click on the Plus (+) sign on the upper-right corner of the chart, you’ll see some options.

⏩ Then check the box before the Axis Titles.

How to Insert a Clustered Column Chart in Excel

⏩ Next, assign the title of the axis and chart.

How to Insert a Clustered Column Chart in Excel

Step 4: Adding & Fixing Data Labels

Again, if you click on the Data Labels, you’ll see the following output where the values are stuffing. Hence, we need to fix the problem.

How to Insert a Clustered Column Chart in Excel

⏩ Therefore, if you click on the values, you’ll see the following feature namely Format Data Labels on the right side of Excel.

⏩ Click over the Number option.

⏩ Choose Custom and input the following format as the Format Code.

[>=1000]#,##0,"K";0

How to Insert a Clustered Column Chart in Excel

Now, the stuffing values are turned into visually readable.

Created Clustered Column Chart

Read More: How to insert column in Excel (Quick 5 methods)


Similar Readings


2. City-Wise Monthly Variation of Sales with Percentage

In this example, we’ll see how we can add two data series for each category.

Before doing that let’s understand the dataset.

Here, we have monthly sales in 4 cities. In addition to this, the percentage of monthly sales for each region is also given. That means data is available for the percentage of sales in a month for each city which can be calculated using the following formula (for C13 cell).

=C5/SUM(C5:C8)

Here, C5 is the sales of Houston in Jan, C5:C8 is the total sales in Jan, and the SUM function is used to aggregate the value of the sales.

Whatever, we have to display the sales & percentage for each city in a month.

City-wise Monthly Variation of Sales with Percentage

Step 1:

⏩ Create the clustered column chart for the monthly sales data (as demonstrated in the first example) like the following screenshot.

Clustered Chart City-wise Monthly Variation of Sales with Percentage

Step 2:

This is the crucial step where are going to add new data labels.

⏩ Pick the Label Options from the Format Data Label (located at the right side of Excel).

⏩ Check the box before Value From Cells and fix New Line as Separator.

⏩ Click on Select Range.

Clustered Chart City-wise Monthly Variation of Sales with Percentage

⏩ Then fix the Data Label Range as $C$11:$F$11, and press OK.

⏩ Repeat the process for other categories (city).

Clustered Chart City-wise Monthly Variation of Sales with Percentage

Ultimately, you’ll get the following output where the monthly sales with the percentage share of each city are depicted.

How to Insert a Clustered Column Chart in Excel City-wise Monthly Variation of Sales with Percentage

Read More: How to Insert Column without Affecting Formulas in Excel (2 Ways)


3. Seasonal Difference of Product Sales

In the following dataset, product sales are given based on the seasons of 2021.

Seasonal Difference of Product Sales

It is a simple example and you can do that if you understand the first example. I added this example to enrich your experience with the clustered column chart.

Seasonal Difference of Product Sales

The above picture reveals that the sales of smartphones are increasing drastically over the season of 2021. However, if you don’t grasp the process of how to insert the clustered column chart in Excel, share your thoughts in the comments section. Obviously, I can assist to solve your problem while creating the chart.


4. Yearly Purchase Price vs Selling Price across States

Lastly, this is an interesting example where you’ll see the comparison of two data series over years for each category.

In the following dataset, purchase price and selling price are provided for 2020 & 2021 for some products.

Now, we have to present this dataset as visually viable.

Yearly Purchase Price vs Selling Price across States

Step 1:

First of all, create an initial chart for the above dataset.

Yearly Purchase Price vs Selling Price across States

Step 2:
We need to remove the Product and Year categories as these are inserted in the Series (Legend Entries) by default. For this, open the Select Data Source dialog box by right-clicking over the chart and choosing the Select Data option.

Yearly Purchase Price vs Selling Price across States

Step 3:

As we want to make Product and Year as Category (horizontal axis), we have to add these.

So, click on the Edit option.

Yearly Purchase Price vs Selling Price across States

Now, fix the Axis label range as $B$5:$C$12, and press OK.

Yearly Purchase Price vs Selling Price across States

If you do that, you’ll get the following output where the purchase price and selling price are representing over 2020 & 2021 for each product.

Yearly Purchase Price vs Selling Price across States


Things to Remember 

  • Don’t add numerous categories that might not work properly.
  • Try to skip the 3D column while inserting the clustered column chart.

Conclusion

This is how we may insert a clustered column chart in Excel. Thus, we can easily compare multiple data series along with categories efficiently. I hope this article might be beneficial for you. Anyway, if you have any queries or suggestions, please let them below.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo