Excel: Create Chart to Show Difference Between Two Series

Often you may want to create a bar chart to visualize the difference in values between two series in Excel.

This tutorial provides a step-by-step example of how to create the following bar chart that shows the difference in product sales each month between two years:

Let’s jump in!

Step 1: Enter the Data

First, let’s create the following dataset that shows the total product sales each month at some company during two consecutive years:

Step 2: Calculate Differences Between Two Series

Suppose we would like to create a bar chart to visualize the difference in total sales each month between the two years.

Before we create this chart, we can first type the following formula into cell D2 to calculate the percentage difference in sales between year 1 and year 2 for January:

```=IF(MAX(B2:C2)=C2,"+"&ROUND((C2-B2)/B2*100,1)&"%",ROUND((C2-B2)/B2*100,1)&"%")
```

We can then click and drag this formula down to each remaining cell in column D:

Column D now shows the percentage change in sales each month between the first and second year:

Step 3: Create Bar Chart with Differences

Next, highlight the cell range A1:C13, then click the Insert tab along the top ribbon, then click Clustered Column within the Charts group.

The following chart will be created:

The blue bars show the product sales during each month of year 1 and the orange bars show the product sales during each month of year 2.

Step 4: Add Labels to Bar Chart

Next, click any of the orange bars on the chart. Then click the tiny green plus sign in the top right corner of the chart, then click the dropdown arrow next to Data Labels, then click More Options:

In the Format Data Labels panel that appears, check the box next to Value From Cells, then choose D1:D13 as the Data Label Range, then click OK. Then uncheck the box next to Value.

Data labels will appear above each orange bar in the chart to show the percentage change in sales for each month between the first and second years:

Step 5: Customize the Chart (Optional)

Feel free to add a title, customize the colors, and adjust the width of the bars to make the plot more aesthetically pleasing: