Shajratul Alam Towhid
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.
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.
Firstly, select the whole dataset > go to Insert tab > Insert Column or Bar Chart dropdown > select 2-D Column chart option.
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.
Secondly, change the Chart Title to 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.
A new blank row will be added like this.
We need to repeat the same thing twice for the rows marked below and get the output like this.
Consequently, we’ll see that extra spaces have been added among different companies.
Read More: How to Make a Comparison Chart in Excel (4 Effective Ways)
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.
Firstly, select the whole dataset > go to Insert tab >Â Insert Column or Bar Chart dropdown > select 2-D Column chart.
Eventually, the following chart will appear.
Secondly, select the chart > go to Chart Design tab > select Change Chart Type option.
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.
Finally, we’ll get our chart like this. Change the Chart Title to 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.
Read More: How to Make a Bar Graph Comparing Two Sets of Data in Excel
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.
Firstly, select the whole dataset > go to Insert tab > select Recommended Charts.
Eventually, an Insert Chart window will appear.
Secondly, select the chart type shown below.
Thirdly, click OK.
As a result, our Line chart will appear like this.
Fourthly, change Chart Title to 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.
Read More: Side-by-Side Comparison Chart in Excel (6 Suitable Examples)
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.
Similarly, as before, select the whole dataset > go to Insert >Â Insert Column or Bar Chart dropdown > select the 2-D Bar chart option.
Eventually, we’ll get the output like this.
Secondly, change the Chart Title to Bar Chart.
Read More: How to Create Month to Month Comparison Chart in Excel
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.
Firstly, select the whole dataset > go to Insert >Â Insert Column or Bar Chart dropdown > select Scatter chart option.
Consequently, we get our chart like this.
Secondly, change the Chart Title to 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.
