Excel Comparison Chart (Table of Contents)
What is Comparison Chart in Excel?
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:
Step 1: Select 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:
This step will allow you to add a blank chart layout as shown in the screenshot below.
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 Select Data option under the Data section.
4.9 (2,356 ratings)
View Course
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:
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.
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.
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.
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:
You can see these two series get added under the Select Data Source window:
Now, if you see at the right-hand side, there is 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 name-Axis Labels. Under 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.
Step 9: Click the OK button one more time to close the Select Source Data window and you are through.
You can see the chart as shown in the screenshot below:
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 the right of it 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.
Under 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:
Step 11: Again, select the chart and click on the + button to be able to add the Axis Titles for X and Y-axis respectively.
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.
This is how we can configure Comparison Chart under Excel. Let’s wrap the 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.
- 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.
Recommended Articles
This is a Comparison Chart in Excel. Here we discuss how to create a comparison chart in Excel together with practical examples and Excel template for download. You can also go through our other suggested articles –
