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.
Watch Video – Insert a Clustered Column Chart in Excel
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.
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.
Then you’ll see the following initial chart.
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.
⏩ Next, click on Switch Row/Column, and press OK.
Now, the output is as follows.
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.
⏩ Next, assign the title of the axis and chart.
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.
⏩ 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
Now, the stuffing values are turned into visually readable.
Read More: How to insert column in Excel (Quick 5 methods)
Similar Readings
- Excel Fix: Insert Column Option Greyed out (9 Solutions)
- How to Insert a Column to the Left in Excel (6 Methods)
- Insert Column with Name in Excel VBA (5 Examples)
- Combine Texts from Two Columns in Excel (6 Easy Tips)
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.
Step 1:
⏩ Create the clustered column chart for the monthly sales data (as demonstrated in the first example) like the following screenshot.
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.
⏩ Then fix the Data Label Range as $C$11:$F$11, and press OK.
⏩ Repeat the process for other categories (city).
Ultimately, you’ll get the following output where the monthly sales with the percentage share of each city are depicted.
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.
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.
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.
Step 1:
First of all, create an initial chart for the above dataset.
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.
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.
Now, fix the Axis label range as $B$5:$C$12, and press OK.
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.
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.
