Grouped Bar Chart

What Is Grouped Bar Chart In Excel?

The Grouped Bar Chart in Excel is a Clustered Bar chart type. It compares multiple categories of data items across different periods, with each data series highlighted by a color varying according to the data value in each set.

Users can use the horizontal and vertical forms of the Grouped Bar graph. These charts are helpful when comparing sales figures for different years.

For example, the table below contains the 2021 quarterly sales data of three branch offices.

Grouped Bar Chart - 1

We will use the Grouped Bar Chart technique, using the Clustered Bar Chart, to graphically plot the above data and perform a visual comparison of the sales figures of each branch office in every quarter.

Grouped Bar Chart - 2

The Grouped Bar Chart is created, as shown above, which is a Clustered Bar graph, but with some modifications, such as setting the gap between the bars as 0 and changing the bar colors.

The graph clearly shows the sales figures of each branch office in each quarter, thus making the quarterly sales comparison between the offices more straightforward.

Key Takeaways
  • The Grouped Bar Chart is a Clustered Bar plot that compares different groups of values over different time intervals.
  • Users can use the Grouped Bar plot to represent sales data in different periods and review variations of one variable with another variable.
  • When working with source data containing multiple groups, the primary variable is time-based and decides the axis of the group. On the other hand, the secondary variable will be a numeric value, such as sales % or score. And it determines the number of bars each group will include in the graph.

How to Create Grouped Bar Chart in Excel?

A few steps to follow while creating a Grouped Bar Chart are,

  1. First, ensure the source table contains complete and accurate data.
  2. Next, click on a cell in the source table, or select the entire table and insert the Clustered Column chart.
  3. Then, sort the specific columns’ data in the source table to get the time-based primary variable and the secondary variable, typically data such as sales % or age.
  4. Next, format the resulting graph by reducing the gap width between the data series to 0% and varying colors by points.
  5. Finally, change the chart type from Clustered Column graph to Clustered Bar chart to achieve the required Grouped Bar plot.

We will understand to Create Grouped Bar Chart in Excel with the following example to review the profits of each region, year-wise.

The table below shows four regions’ profit data during 2018-21.

How to create Groped Bar Chart in Excel

The steps to create the Grouped Bar Chart for the above table using the below steps:

  1. Choose cell A1 → select the “Insert” tab → go to the “Charts” group → click the “Recommended Charts” option, as shown below.


    How to create - Step 1

    The “Insert Chart” window opens. Now, click the “Recommended Charts” tab → Clustered Column in the Insert Chart window to create a Clustered Column chart.



    And once we click OK in the Insert chart window, we will get the below graph.

Alternative Method #1

The steps to create the above chart in a different method are as follows:

  • 1: Select a cell in the source table and click InsertColumn or Bar 2-D Clustered Column to create the required Clustered Column chart.
Grouped Bar Chart - Alternate Method 1
  • 2: To compare profit data year-wise, let us sort the column Year in ascending order. And for that, select the cell range A2:C17 and click DataSort Smallest to Largest.
Alternate Method 1 - Step 2

After we click the required sorting option, the modified source table and the inserted Bar graph are shown below.

Alternate Method 1 - Step 2a
  • 3: Since the grouping is year-wise, we will insert a row between each group in the source table.

Select the rows to insert new rows → right-click and select “Insert”, as shown below.

Alternate Method 1 - Step 3

Leaving a row between each group in the source table provides adequate space between the profit data for each year in the graph. Now, the modified source table and the given chart are shown below.

Alternate Method 1 - Step 3a
  • 4: We will update the source table so the year appears only once for the respective group.
Alternate Method 1 - Step 4
  • 5: To format the graph to get the required Grouped Bar graph, right-click on the chart → select the “Format Data Series” option to open the Format Data Series pane.
Alternate Method 1 Grouped Bar Chart- Step 5

In the “Format Data Series” pane, select the “Series Options” tab → set the Gap Width option as 0%, as shown below. It helps combine bars of each category to appear as a group.

Alternate Method 1  Grouped Bar Chart - Step 5a

The gap between the columns is reduced to 0, as shown below.

Alternate Method 1  Grouped Bar Chart - Step 5b

Next, click on the Fill & Line tab → check/tick the checkbox of the “Vary colors by point” option to view the columns in different colors according to their points.

Alternate Method 1  Grouped Bar Chart - Step 5c
  • 6: Click the chart area to enable the Design tab. Now, select the “Design” tab → go to the “Type” group → click the “Change Chart Type” option to open the Change Chart Type window.
Alternate Method 1 - Step 6

In the Change Chart Type window, select All ChartsBarClustered Bar to convert the Clustered Column graph into a Clustered Bar chart.

Alternate Method 1 - Step 6a

Click “OK”, and we will get a Horizontal Grouped Bar Chart, as shown below.

Alternate Method 1 - Step 6b
  • 7: Click the chart area to access the Chart Elements option (‘+’ icon). Enable the Axis Titles option in the Chart Elements drop-down list.
Alternate Method 1 - Step 7

Finally, update the chart title and the axes titles by double-clicking the respective elements in the chart area. The graph in this Grouped Bar Chart example will thus appear highly professional, enabling us to compare each region’s profit figures in the respective years and thus assess their performance levels, as shown below.

Alternate Method 1 - Step 7a
Alternative Method #2

We can directly use the Clustered Bar chart available in the Insert tab, and the steps are as follows:

  • 1: First, we shall modify, sort, and make the necessary changes in the source table, as explained in the previous steps. And we shall use the updated source table to create the required Clustered Bar chart.
Grouped Bar Chart - Alternate Method 2
  • 2: Select the source table and click InsertRecommended Charts to open the Insert Chart window.
Alternate Method 2 -Step 2
  • 3: Next, select All ChartsBarClustered Bar in the Insert Chart window to insert the Clustered Bar graph.
Alternate Method 2 -Step 3

We can also select the source table and click InsertColumn or Bar2-D Clustered Bar to insert the required chart.

Alternate Method 2  Grouped Bar Chart -Step 3a
  • 4: Adjust the graph size to view the axis details properly.
Alternate Method 2  Grouped Bar Chart -Step 4
  • 5: Finally, we can follow steps 5 and 7 from the previous example to make the appropriate changes to the graph to make it into a Grouped Bar plot.
Alternate Method 2 - Step 5

Examples

We will understand some advanced scenarios and create the Grouped Bar Chart.

Example #1

Consider a sorted source table containing the state-wise carton delivery data from Jan-Jun.

Grouped Bar Chart in Excel - Example 1

The steps to compare each state’s data, month-wise, graphically using a Horizontal Grouped Bar Chart are as follows:

  • 1: Click on the table and select InsertColumn or Bar2-D Clustered Column.
Example 1 - Step 1
  • 2: As the table contains data sorted in the required month-wise groups, insert a row between each group to distinguish them in the table and the graph.
Example 1 - Step 2

The modified table data has six groups, each containing data from three states, as shown below.

Example 1 - Step 2a
  • 3: Right-click a data series and choose the Format Data Series option from the list to open the Format Data Series window.
Example 1 - Step 3

In the “Format Data Series” pane, select the “Series Options” tab → set the Gap Width option as 0%, as shown below.

Example 1 - Step 3a

Next, click on the Fill & Line tab → check/tick the checkbox of the “Vary colors by point” option to view the columns in different colors according to their points.

Example 1 - Step 3b
  • 4: Next, click the chart area and select DesignChange Chart Type to open the Change Chart Type window.
Example 1 - Step 4a
Example 1 - Step 4b

Select the Clustered Bar plot to get the Grouped Bar Chart.

Example 1 - Step 4c
  • 5: Update the source table so that the month appears only once for the respective group in the graph.
Example 1 - Step 5
  • 6: Finally, using the Chart Elements option, update the chart title and axis titles in the chart area to achieve the final Grouped Bar Chart as depicted below. [Refer to the previous section for the detailed steps.]
Example 1 - Step 6

So, now we can visually compare the number of cartons delivered in each state, month-wise.

Example #2

Consider the below source table in the right format that enables us to create a Grouped Bar Chart using the Clustered Bar plot.

Grouped Bar Chart in Excel - Example 2

The steps to create a Grouped Bar Chart to perform a graphical, term-wise comparison of the student scores are as follows:

  • 1: Select the entire table and click InsertColumn or Bar2-D Clustered Bar chart.
Example 2 - Step 1
  • 2: Right-click on the created chart → select the Format Data Series option from the menu.
Example 2 - Step 2

In the “Format Data Series” pane, select the “Series Options” tab → set the Gap Width option as 0%, as shown below.

Example 2 - Step 2a

Next, click on the Fill & Line tab → check/tick the checkbox of the “Vary colors by point” option to view the columns in different colors according to their points.

Example 2 - Step 2b
  • 3: Finally, update the chart title and axis titles in the chart area (Explained in the previous section) to achieve the following Grouped Bar Chart.
Example 2 - Step 3

Thus, we get a graph showing the marks scored by the students in each term, as shown above.

Uses Of Grouped Bar Chart In Excel

The uses of the Grouped Bar Chart in Excel are:

  • The chart graphically represents the distribution of data points.
  • To check how the secondary level variables vary within each first level variable. And to perform a comparison among the first-level variables.
  • We can compare the different categories’ values across varying periods.

Important Things to Note

  • The source data must be sorted in the required order before we create a Grouped Bar Chart.
  • Select the 2-D Clustered Bar chart to create a Grouped Bar plot. But if we require a vertical Grouped Bar plot, we can use the Clustered Column chart type.
  • Inserting a row between the data groups differentiates the datasets or data groups better.
  • Set the Gap Width to 0% to reduce the gap between the bars within a group.
  • Enable the ‘Vary colors by point’ to ensure the bars are color-coded to make visual analysis more convenient.

Frequently Asked Questions (FAQs)

1. How to create a Grouped Bar Chart for three sets of data?

We can create a Grouped Bar Chart for three datasets using the Clustered Bar chart.

For example, consider the following table containing three sets of warehouse inventory data for Apr-Jun.

Grouped Bar Chart in Excel - FAQ 1

The steps to create Grouped Bar Chart to compare the inventory figures of each warehouse for each month are,

• 1: Select the entire table and click InsertColumn or Bar2-D Clustered Column chart.

FAQ 1 - Step 1

• 2: Insert a row between each group in the table to distinguish them well in the graph.

FAQ 1 - Step 2

• 3: Right-click a data series and select the Format Data Series option from the Contextual menu to open the Format Data Series window.

FAQ 1 - Step 3

Next, set the Gap Width as 0% and the color setting to vary the bar colors.

FAQ 1 - Step 3a

Next, click on the Fill & Line tab → check/tick the checkbox of the “Vary colors by point” option to view the columns in different colors according to their points.

FAQ 1 - Step 3b

• 4: Click the chart area and DesignChange Chart Type to open the Change Chart Type window. And then, select All ChartsBarClustered Bar chart to convert the Clustered Column graph to Clustered Bar chart to achieve the required Grouped Bar Chart.

FAQ 1 - Step 4

We will get the following chart.

FAQ 1 - Step 4a

• 5: Enable the Axis Titles element using the Chart Elements option (‘+’ icon).

FAQ 1 - Step 5

And update the chart title and the axis titles by selecting the respective elements in the chart area individually to get the final Grouped Bar Chart, as shown below.

FAQ 1 - Step 5a

2. What is the difference between a Grouped Bar Chart and a Stacked Bar chart in Excel?

A Grouped Bar Chart helps compare values from different categories. On the other hand, a Stacked Bar chart represents the ratio of the individual data to the whole percentage.

3. Why is Grouped Bar Chart not working in Excel?

The Grouped Bar Chart may not work due to the following reasons:
We haven’t arranged the source data in the required order.
The selected chart type is not the Clustered Bar graph.
The Gap Width option in the Format Data Series pane is not set to 0%.

Download Template

This article must help understand the Grouped Bar Chart, with its charts and examples. We can download the template here to use it instantly.

This has been a guide to Grouped Bar Chart. Here we learn how to create the chart in excel along with examples & a downloadable template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *