How To Create A Comparative Bar Chart In Excel

Comparison Chart in Excel

Excel Comparison Chart (Table of Contents)

What is Comparison Chart in Excel?
How to Create a Comparison Chart in Excel?

What is Comparison Chart in Excel?

A comparison chart is a graphical representation of different informational values associated with the same set of categories, which can be compared for different geographical regions or for different categories etc. For example, comparing sales value for one particular product in two different regions of your country might be your area of interest. Or maybe how much margin your business is making in two different regions for the current product so as to decide the market strategy for you. In such cases, only numbers may not be enough to have a better idea of businesses running. A graphical visualization would be better under these cases. We will see in this article how to create a comparison chart in Excel step by step.

How to Create a Comparison Chart in Excel?

Let's dive deep and check how can we create a comparison chart in Excel. Suppose we have data for sales associated with the different regions as shown in the screenshot below:

You can download this Comparison Chart Excel Template here – Comparison Chart Excel Template

Comparison Chart in excel 1-1

Step 1: Select the Insert tab through the Excel ribbon and then navigate to the Charts section. Under the Charts section, click on Insert Column or Bar Chart dropdown and then select the Clustered Column chart option under the 2-D Column Chart section. See the screenshot below:

Comparison Chart in excel 1-2

This step will allow you to add a blank chart layout, as shown in the screenshot below.

Comparison Chart in excel 1-3

Now, we need to add Data to this blank chart so that we can see the sales values for countries in a comparative manner. Follow the steps below:

Step 2: Select the graph by clicking on it and navigate to the Design tab; click on the Select Data option under the Data section.

Chart Design - Select Data

Step 3: As soon as you click on the Select Data option, a new window "Select Data Source" will open up in Excel as shown below:

Select Data Source

This window helps you modify the chart as it allows you to add the series (Y-Values) as well as Category labels (X-Axis) to configure the chart as per your need. Under Legend Entries (Series) inside the Select Data Source window, you need to select the sales values for the year 2018 and year 2019. Follow the step below to get this done.

Step 4: Click on the Add button placed at the left-hand side of the Select Data Source window.

Select Data Source - Add

Step 5: As soon as you click on the Add button, a new window will pop up named "Edit Series". Under Series Name: select the B1 as an input (since B1 contains the name for sales value 2018.

Edit Series - Sales Value 2018

Step 6: Under Series Values: section, delete "={1}" which is a default value for a series and then select the range of cells that contain the sales values for the year 2018 associated with different countries. It varies from B2:B7. Click on OK once done.

NOTE: You can add Sheet 1 or Sheet 2 under Series Value.

Edit Series- Select Data

As soon as you click OK, you can see behind the Select Data Source window; the bars are being added for the Sales Value 2018 column.

Step 7: Click on the Add button one more time to be able to add the values associated with Sales 2019. Follow the same procedure as of the previous step. Use C1 as a reference for Series name and C2:C7 as a reference for Series values. It should look as shown in the screenshot below:

select the data - sales value 2019

You can see these two series get added under the Select Data Source window:

Horizontal Axis Lables

Now, if you see at the right-hand side, there is a Horizontal (Category) Axis Labels section. This is the one where you need to edit the default labels so that we can segregate the sales values column Country wise.

Step 8: Click on the Edit button under the Horizontal (Category) Axis Labels section. A new window will pop up with the name Axis Labels. Under the Axis label range: select the cells that contain the country labels (i.e. A2:A7). Click on the OK button after you select the ranges.

Axis Lables

Step 9: Click the OK button one more time to close the Select Source Data window, and you are through.

Select Data Source - Axis lables

You can see the chart as shown in the screenshot below:

Comparison Chart

Please note that there is no such option as Comparison Chart under Excel to proceed with. We just have added a bar/column chart with multiple series values (2018 and 2019). However, adding two series under the same graph makes it automatically look like a comparison since each series values have a separate bar/column associated with it.

Step 10: Select the chart area and click on the + button that pop-up at its right once you click the same. In the list of options, select Chart Title by ticking it, and you can see a chart title text box that appears at the upper side of the chart.

Chart Title -Comparison Chart

Under the Chart Title text box, you can add a title of your choice for this chart. I have added it as a "Comparison Chart" for the one I have created. See the screenshot below:

Chart Title - Comparison Chart

Step 11: Again, select the chart and click on the + button to be able to add the Axis Titles for the X and Y-axis, respectively.

Axis Title 1

I have added Axis Titles as Sales Values for Y-Axis and Countries for X-Axis. You can use any suitable names for your choice. Just make sure the names you are adding for the chart title and axis title are relevant to your chart data. Usually, chart headers are used as axis labels/titles.

Countries and Sales value

This is how we can configure Comparison Chart under Excel. Let's wrap things up with some points to be remembered.

Things to Remember

There is no chart with the name as Comparison Chart under Excel. However, we can add multiple series under the bar/column chart to get the Comparison Chart.
A comparison chart is best suited for situations when you have different/multiple values against the same/different categories, and you want to have a comparative visualization for the same.
Comparison Charts are also known with a famous name as Multiple Column Chart or Multiple Bar Chart.