What is Comparison Chart in Excel?
When the important decision needs to be made, you may want to have a look at the region-wise sales, city-wise sales, or any other category-wise sales values. Reading values from table summary is not a quick option because looking at numbers against each other categories taken good enough time, so instead of showing the table summary alone, we can show those numbers in charts, and that chart is called “Comparison Chart”.
How to Create a Comparison Chart in Excel? (with Examples)
Above is the State-wise and City-wise sales values. When we look at the data, we have the same state for two cities. For example, for the state “California,” we have “Los Aneles & San Francisco” cities; to compare these two cities’ values against each other in the same city, we need to create a comparison chart in excel.
Follow the below steps to create a comparison chart in excel.
- Copy the above table data to excel.
- Select the data and insert “Column Chart” in excel.
- Now, we have a default chart like the below one.
This isn’t the clear comparison chart yet; to make it clear, we need to modify the data slightly.
- Since we have the same state name for multiple cities, let’s merge state values into one cell.
- Now, look at the chart.
As you can see on the horizontal axis, we can see only one state name for multiple cities, unlike the previous one of having state names for all the cities.
We need to still make it clear by adding space between each state.
- A simple technique can add the space between each state. After every state name, inserts a blank row.
Now, look at the chart.
From the above chart, we can clearly compare city-wise sales in the same state. By adding an extra empty row, we can make this difference.
Use Combo Chart as Comparison Chart in Excel
Above is one way of comparing values in the same category; similarly, we can use “Combo Chart” to compare values as well. For example, look at the below data.
Insert column chart for this data by copying the data to excel worksheet. When the column chart is inserted, we can have a chart like the below one.
This is the general way of creating a column chart. But making the “Profit” column bar as the different chart, we can actually compare things even better.
Select the chart, and we can see two extra tabs in the ribbon, i.e., “Design & Format.”
From the “Design” tab, click on “Change Chart Type.”
Now it will open up the “Change Chart Type” window.
Click on the “Combo” option at the bottom.
When you select the “Combo” option, we can see combination chart types; at the bottom, we can see the chart type for each series.
For the “Profit” column, select the chart type as “Line” and make it as “Secondary Axis.”
Now click on “Ok” we will have a comparison chart ready with two different charts.
As you can see above, we have two vertical axes, one on the right side and one on the left side of the chart.
The right side vertical axis is for column chart bars, and the left side vertical axis is for the line chart. From the above chart, we can see for the month of “May” revenue is 15000 and the cost is 11000, but profit is 4000, so this shows that when compared to other months, this month profit values are more.
Things to Remember
- The excel comparison chart is to compare the multiple subcategory values under one main category.
- A combo chart in excel is best suited to compare values.
- Always have a secondary axisSecondary AxisThe secondary axis is the other axis that is used to denote different data sets that cannot be displayed on a single axis. The primary axis, for example, depicts time, whereas the secondary axis displays production. for a combo chart to read better.
This has been a guide to Comparison Chart in Excel. Here we learn how to create a comparison chart in excel and along with examples and a downloadable excel template. You may learn more about excel from the following articles –