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. 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.

The Grouped Bar Chart is created, as shown below, 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.

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.

Alternative Method #1

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

• Step 1: Select a cell in the source table and click Insert → Column or Bar → 2-D Clustered Column to create the required Clustered Column chart.

• Step 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 Data → Sort Smallest to Largest.

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

• Step 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.

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.

• Step 4: We will update the source table so the year appears only once for the respective group.

• Step 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.

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.

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

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.

• Step 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.

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

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

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

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.

Alternative Method #2

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

• Step 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.

• Step 2: Select the source table and click Insert → Recommended Charts to open the Insert Chart window.

• Step 3: Next, select All Charts → Bar → Clustered Bar in the Insert Chart window to insert the Clustered Bar graph.

We can also select the source table and click Insert → Column or Bar → 2-D Clustered Bar to insert the required chart.

• Step 4: Adjust the graph size to view the axis details properly.

• 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.

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.

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

• Step 1: Click on the table and select Insert → Column or Bar → 2-D Clustered Column.

• Step 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.

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

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

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

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.

• Step 4: Next, click the chart area and select Design → Change Chart Type to open the Change Chart Type window.

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

• Step 5: Update the source table so that the month appears only once for the respective group in the graph.

• 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.]

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.

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

• Step 1: Select the entire table and click Insert → Column or Bar → 2-D Clustered Bar chart.

• Step 2: Right-click on the created chart → select the Format Data Series option from the menu.

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

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.

• Step 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.

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)

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.

Recommended Articles

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 –

• Bar Chart In Excel
• Waterfall Chart In Excel
• Pivot Chart In Excel