What is Comparison Chart in Excel?
When the important decision needs to be taken you may want to have look at the region-wise sales, city-wise sales or any other category-wise sales values. Reading values from table summary is not the quick option because looking at numbers against each other categories taken good enough time, so instead of showing 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 an 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.
- Step 1: Copy the above table data to excel.
- Step 2: Select the data and insert “Column Chart” in excel.
- Step 3: 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.
- Step 4: 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.
- Step 5: 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 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 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 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 chart.
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.
- Combo chart in excel is best suited to compare values.
- Always have a secondary axis 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 downloadable excel template. You may learn more about excel from the following articles –