How to Show Difference Between Two Lines in Excel Graph

In this article, we will learn How to show the difference between two lines graphically  in Excel

A sample of the final output is given below:

Suppose you have a data table with the sold quantity of several products and the forecasted quantity of the products.

Now you want to show the difference between the sold quantity and the forecasted quantity graphically in Excel.

We will divide the steps into three sections:

1. Preparing Data for showing the difference between two lines
2. Creating chat from the data table
3. Making the difference visible 

Section A. Preparing Data for Showing the Difference Between Two Lines

Step 01: Create a new column named Mid_line and enter the following formula in cell E3.

=C3+(D3-C3)/2

Then, AutoFill the remaining cells in column E 

This formula will give data for creating a new  line in the middle of the sold quantity and the forecasted quantity

Step 02: Create a new column named Difference and enter the following formula in cell F3

=(D3-C3)

Then, AutoFill the remaining cells in column E 

Section B. Creating Chat from the Data Table

Step 03: Select the cell range B2:E10 and go to the Insert tab

Step 04: From the Charts section, click on the Line chart.

The result is shown below

Section C. Making the Difference Visible 

Step 05: Click on the Forecasted line. Then, navigate to the Chart Design tab

Step 06: Click on the Add Chart Element

Step 07: Then keep the mouse pointer on the Lines option and click on the High/Low lines

The result should be similar to the image given below

Step 08: Click on the Mid_line. Again, go to the Chart Design tab.

Step 09: Click on the Add Chart Element

Step 10: Then keep the mouse pointer on the Data Labels option and click on the Center option.

Data labels will be visible as shown below

Unfortunately, the data label shown in the chart does not meet our requirements.

Step 11: Double-Click on the Data labels on the chart, and then the Format Data Labels tab will appear on the right side of the monitor.

Step 12: Click on the Label Options and check in the Select Value From Cells. A new input field named “Data Lable Range” will pop-up

Step 13:  Select the data in the column named Difference or enter the following formula in the field of the Select Data Label Range. Then press the OK

=Sheet1!$F$3:$F$10

Step 14: From the Format Data Labels  tab, Uncheck the Value option

Step 15: Then, click on Click on the Mid_line. The Format Data Series tab will appear on the right side of your monitor. Click on the Fill and Line option and then select No Line 

The result is 

Now you don’t need the data label named Mid_line. To remove this, just click on the label (Mid_line) and press Delete.