How to Compare Two Sets of Data in Excel Chart (5 Examples)

If you are looking for how to compare two sets of data in an Excel chart, then you are in the right place. In our practical life, we often need to compare data. It becomes easier to compare when we use Excel charts. In this article, we’ll try to discuss how to compare two sets of data in Excel data.


Download Practice Workbook


5 Examples to Compare Two Sets of Data in Excel Chart

There are various methods to compare data by the usage of different types of charts. To do this, firstly, we have made a dataset named Sales of Companies of USA which has columns as Name of Company, Branch Location, Sales, and Cost. The dataset is like this.

compare two sets of data in excel chart

Let’s discuss various examples of comparing two sets of data in an Excel chart.


1. Using 2-D Column Chart to Compare Two Sets of Data in Excel Chart

We can use a 2-D Column chart when we need to compare data individually among different matters. We need to compare the Sales and Cost data of different branches of different companies individually. Eventually, to do this, we have to work with the following dataset.

compare two sets of data in excel chart, using 2-D Column chart

Firstly, select the whole dataset > go to Insert tab > Insert Column or Bar Chart dropdown > select 2-D Column chart option.

compare two sets of data in excel chart, using 2-D Column chart

Consequently, a 2-D Column chart will appear.
Here, for example in the case of Amitech, we can see the comparison of sales and profit in Georgia and Texas. Similarly, in the case of Walmart, the comparison has been made to sales and profit in  Alaska and Boston and this thing has been repeated in other companies too.

compare two sets of data in excel chart, using 2-D Column chart

Secondly, change the Chart Title to 2-D Column Chart.

compare two sets of data in excel chart, using 2-D Column chart

We can see from the picture above that different companies are not separated. There is no space to easily understand each portion.
Thirdly, right-click on the joining point of 7th and 8th Row and select Insert to make a distinction.

compare two sets of data in excel chart, using 2-D Column chart

A new blank row will be added like this.

compare two sets of data in excel chart, using 2-D Column chart

We need to repeat the same thing twice for the rows marked below and get the output like this.

compare two sets of data in excel chart, using 2-D Column chart

Consequently, we’ll see that extra spaces have been added among different companies.

compare two sets of data in excel chart, using 2-D Column chart


2. Utilizing Combo Chart to Compare Two Sets of Data in Excel Chart

We can use this chart when we need to compare two sets of data by mixed types of charts. We need to work with the following dataset named Utilizing Combo Chart which has column headers as Month, Sales, and Cost.

compare two sets of data in excel chart, using Combo chart

Firstly, select the whole dataset > go to Insert tab >  Insert Column or Bar Chart dropdown > select 2-D Column chart.

compare two sets of data in excel chart, using Combo chart
Eventually, the following chart will appear.

compare two sets of data in excel chart, using Combo chart

Secondly, select the chart > go to Chart Design tab > select Change Chart Type option.

compare two sets of data in excel chart, using Combo chart

A Change Chart Type window will appear.
Thirdly, go to Combo > make Chart Type as Line for Cost and add a click to Secondary Axis.

compare two sets of data in excel chart, using Combo chart

Finally, we’ll get our chart like this. Change the Chart Title to Combo Chart.

compare two sets of data in excel chart, using Combo chart

Here, Sales is represented by a 2-D Column chart, and Cost is represented by Line chart with a secondary axis on the right side of the chart. These two different types of charts help to compare Sales and Cost data easily.


3. Using Line Chart

Only the Line chart is also useful to compare two sets of data. We need to apply this method to the following dataset.

 using Line chart

Firstly, select the whole dataset > go to Insert tab > select Recommended Charts.

 using Line chart

Eventually, an Insert Chart window will appear.
Secondly, select the chart type shown below.
Thirdly, click OK.

 using Line chart

As a result, our Line chart will appear like this.
Fourthly, change Chart Title to Line Chart.

compare two sets of data in excel chart, using Line chart

We can see from the chart that Sales is always in a higher position than Profit and can relate Sales with Profit in every specific point of the chart.


4. Applying Bar Chart

The bar chart is another type of chart which we can use to compare data from a different angle in the chart. We need to work with the following dataset of Using Bar Chart.

Bar Chart

Similarly, as before, select the whole dataset > go to Insert Insert Column or Bar Chart dropdown > select the 2-D Bar chart option.

Bar Chart

Eventually, we’ll get the output like this.
Secondly, change the Chart Title to Bar Chart.

Bar Chart


5. Utilizing Scatter Chart

A Scatter chart is also a type of chart which we can use when we don’t need any continuous output of data but we only need to compare data in some specific points. To do this, we need to work on the following dataset of Utilizing Scatter Chart.

Scatter Chart

Firstly, select the whole dataset > go to Insert Insert Column or Bar Chart dropdown > select Scatter chart option.

Scatter Chart

Consequently, we get our chart like this.
Secondly, change the Chart Title to Scatter Chart.

Scatter Chart


Things to Remember

  • Bar Chart and Column Chart actually represent the same thing. But the difference is the change in axis. In the Bar Chart, we can see the values on the horizontal axis and names on the vertical axis. In the 2-D Column Chart, the axis just reverses so the shape of the chart.
  • The Scatter Chart is not friendly when we need to get a continuous output of a graph to get the unknown data through connecting. It is more useful when we need to add a Regression Line and need to Forecast.

Conclusion

We can compare any kind of two sets of data by using any type of chart if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.

Towhid
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo