Meraz Al Nahian
The article will show you how to make a Bar graph comparing two sets of data. Bar graphs are helpful for us to visualize the change in data by its value. In Excel, we can insert one or multiple datasets in a Bar graph and hence, we can compare these data. It helps us to realize how much progress has been made in the business sector or GDP growth or any other practical fields where profit and revenue are concerned. In this article, I’ll illustrate some simple tricks on how to compare two sets of data in a Bar graph.
Download Practice Workbook
4 Ways to Make a Bar Graph Comparing Two Sets of Data in Excel
In the dataset, we have the profit amount of some products in the previous and current years.
1. Making a Simple Bar Graph with Two Sets of Data
The simplest thing we can do to compare our two sets of data in a Bar graph is to simply put them in a Bar graph. Let’s follow the description below.
- First, select the data and then go to Insert >> Insert Column or Bar Chart.
- After that, you will see various types of Bar Charts. I selected the first option of the 2-D Bar
Next, you will see the data in a 2-D Bar Graph. You can see the increase or decrease in profit from the Bar graph. With these data and the Bar graph, you can make a comparison between the profits of previous and current years.
- After that, do a little formatting so that your Bar graph looks a little bit more nice and clean. For that reason, we selected a bar from the chart, right-clicked on it, and chose Format Data Series…
- Thereafter, lessen the Gap Width to make these bars more visible.
After executing this operation, you will get a better visualization of the Bar graph on the Profit Analysis. Note that you can change the Chart Title if you want.
Thus, you can make a Bar graph comparing two sets of data.
Read More: How to Compare Two Sets of Data in Excel Chart (5 Examples)
2. Adding Data Labels to Compare Two Sets of Data
You can also make your Bar graph more detailed to compare two sets of data in a comprehensible way. Let’s go through the procedure below for a better understanding.
Steps:
- First, follow Method 1 to make your Bar graph.
- After that, select the Bar graph and then go to Chart Design >> Add Chart Element >> Data Labels >> Outside End.
Later, you will see the Profit Data besides the bars.
Thus you can make a Bar graph comparing two sets of data by adding data labels.
Read More: How to Make a Comparison Chart in Excel (4 Effective Ways)
3. Inserting Data Table to Compare Data in a Bar Graph
We can also use a data table in the Bar graph and compare two sets of data from it. Let’s get to the description below.
Steps:
- Follow the process of Method 1 to make the Bar graph, select it and then go to Chart Design >> Add Chart Element >> Data Table >> No Legend Keys.
- After that, you will see the Profit data of both of the current and previous years in a table in the graph.
Thus, you can make a Bar graph comparing two sets of data by adding a table to it.
Read More: Side-by-Side Comparison Chart in Excel (6 Suitable Examples)
4. Showing Percentage Change to Compare Two Sets of Data
In this section, I’ll show you briefly how to compare two sets of data in a Bar graph with percentage changes. If you want to know more about this method, please follow the article ‘How to Show Percentage Change in Excel’. We will compare the percentage increase or decrease of profits with respect to the previous year profits.
Steps:
- First, make some columns for Variance in profit increase or decrease, Positive Variance, Negative Variance and Percentage Change.
- After that, type the following formula in cell E6, hit the ENTER button and use Fill Handle to AutoFill lower cells.
=C6-D6
- Then again, type this formula in cell F6 to determine the positive variances of the profits.
=IF(E6>0,-E6,"")
The IF Function stores the increase of profits in column F.
- After that, write down the formula given below in G5, hit the ENTER button and use Fill Handle to AutoFill lower cells.
=IF(E6<0,-E6,"")
This time, the IF Function stores the decrease of profits in Column G.
- Finally, get the percentage values by using the following formula.
=E6/D6
Now we are going to show this information in a Bar graph. Specially our main focus is on Percentage Change. Let’s see how we make a chart for this.
- Follow the steps of Method 1 to create the Bar Chart and open the Format Data Series
- Next, set both Series Overlap and Gap Width to 0%.
- After that, select any of the ‘This Year’ bars (Blue Colored Bar) and right-click on it.
- Select Fill >> No Fill.
- Next, select the chart and go to Add Chart Element >> Error Bars >> More Error Bars Options…
- After that, a dialog box will appear. Our Error Bars will be based on this year’s profit. So we select This Year and click OK.
- Thereafter, you will see the Profit of this year with the Error Bars. Select any of them.
- Now, in the Format Error Bars window, select Custom >> Specify Value from the Error Amount
- Also, select any option from the other sections of your choice.
- Next, insert the following ranges in the Custom Error Bars by selecting them. We actually selected the positive and negative variance values in the Positive Error Value and Negative Error Value
- After that, click OK.
- Thereafter, select the chart and then go to Add Chart Element >> Data Labels >> More Data Label Options…
- Then the Format Data Labels window will appear. Check Value From Cells.
- After that, you will see a dialog box. Select the range H6:H12 (Percentage Column) for the Data Label Range and click OK.
- Next, uncheck Value and Show Leader Lines for convenient visualization of your chart.
After that, you will see the increase and decrease of the profits of This Year in percentage in the chart.
- To make the Error Bars look more efficient, we can just keep the increased profit bars in the chart to compare these two sets of profit data. So we selected Horizontal Error Bars >> Plus.
This will show the increased profits with Error Bars. A decrease in profits will be demonstrated by corresponding percentage values.
This is a very nice and effective way to make a Bar graph comparing two sets of data with percentage change.
Read More: How to Make Sales Comparison Chart in Excel (4 Simple Ways)
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Conclusion
In the end, we can conclude that you will learn some efficient techniques of how to make a Bar graph comparing two sets of data after reading this article. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
