How to create a comparative bar graph in excel

A side by side bar chart is useful to compare two categories over time. The chart displays the trend of each category as well as the differences between the two categories at each point.

Suppose we have the number of boys and girls participated in one activity in the last 15 years as above and we are making a comparison chart to compare the differences between boys and girls.

Step 1: To re-arrange the data and set one of the columns as minus which will appear on the left of the chart. In this chapter, we put boys on the left and girls on the right; To do so, we need move boys data to an empty area and then set them as minus values;

Step 2: Select column B and C, and click the Insert Tab from the ribbon;

Step 3: Click "Clustered Bar" in the 2-D Bar section;

Step 4: A comparison chart will be created and should like the one below;

Step 5: To set Y Axis as Year: right click on the Y axis, pick up "Select Data" from the dialog box. In the "Select Data Source" window, click "Edit", then select Range A2:A16 in the Axis Labels window;

Step 6: Click OK to return "Select Data Source" window, click another OK to have Y Axis as years.

Step 7: Move Y Axis to left: Right click on Y axis, select "Format Axis", then in the "Format Axis" window, select "Low" Label Position in the "Labels" section;

Step 8: Series Overlap: right-click the bars and select Format Data Series, then set the Series Overlap as 100%; also adjust Gap Width to reduce the space between bars (here we set to 30%);

Step 9: Change X Axis to positive numbers: Right click X Axis, click "Format Axis";

In the "Format Axis" window, select "Custom" in the Number section, then remove the minus (-) sign from "Format Code" box;

Click "Add", the X Axis will change to positive numbers;

Step 10: Now a side by side comparison chart is created, please also change chart title, bar color, and other elements to meet your needs.

Download Example

Excel is powerful data visualization and data management tool which can be used to store, analyze, and create reports on large data. It can be used to visualize data using a graph plot. In excel we can plot different kinds of graphs like line graphs, bar graphs, etc. to visualize or analyze the trend. Let’s learn how to plot a bar chart for comparing data in excel.

Bar graph value is represented using the bars. It is used to compare items side-by-side. Column chart and bar chart is almost same, the only difference is in column chart bar is represented vertically while it is represented horizontally in bar graph. We can easily plot a line chart in excel, following the below steps, For the purpose of demonstration we will compare the sales of three different products over the given years:

 Step 1: Select the cell containing product data.

Step 2: Select ‘Insert’ Tab from the top ribbon and select the bar chart.

Bar chart

we can see that each product is shown with different colors. From the graph we can easily visualize and compare the sales for different years. For example, highest sales is of product C (grey) in year 2022.

Steps to make changes in graph

Step 1: Click on chart title (Sales in above graph)

Step 2: Chart format menu will open, make desired changes.

For example, change the chart title.

Output

You can see that the chart title is changed now.

Bar chart from pivot table

Suppose we have following table given below, and we want to compare the total expenditure by each individual buyer:

Steps to create pivot table

Step 1: Select the data and then select pivot table from insert tab.

Step 2: Select the ‘expenditure by buyer’ option and then select existing sheet option if you want your pivot table to be present in the same sheet.

Step 3: Give the cell value where you want to place the pivot table and then select Insert.

Step 4: Pivot table will get created.

Using this pivot now, we can create line chart. To create line chart follow the steps:

Step 1: Select the pivot table

Step 2: Click on Insert tab and select bar chart option.

Output

From this chart we can compare the total expenditure by each individual. For example, Rohit jha the maximum expenditure.

Article Tags :