The sample dataset below contains a list of products including their selling prices with the cost prices of a company. For the two different series of selling prices and cost prices, we will show their differences with the help of a bar chart.

__Method 1 –__Â Show Difference with Actual Values Between Two Series in Excel Bar Chart

__Step 1__: Using Formulas to Calculate Some Values for Bar Chart

âž¤ Add the following formula in cell **E4**.

`=C4-D4`

âž¤ Press **ENTER **and drag down the **Fill Handle **tool.

Result shows the differences between the selling prices and cost prices; the values within the brackets represent the negative values.

âž¤ For determining the minimum value between the selling price and the cost price to get the base difference between them, insert the following formula.

`=MIN(C4,D4)`

âž¤ Use the **AutoFill** feature.

We will get the base differences between the selling prices and the cost prices.

To separate the differences between the two prices according to their positive values and negative values.

âž¤ For extracting the positive values use the following formula in cell **G4**.

`=IF(E4>0,E4,"")`

When the value in cell **E4 **is positive, the **IF **function will return this positive value. If value is negative, the output cell will be blank.

âž¤ Press **ENTER **and drag down the **Fill Handle **tool.

This will result in the positive differences in theÂ **PositiveÂ **column.

âž¤ For extracting the negative values use the following formula in cell **H4**.

`=IF(E4<0,ABS(E4),"")`

**E4 **is the difference between the prices.

**ABS(E4) â†’**The**ABS**function will return the absolute value of the number in cell**E4**neglecting its sign.**Output â†’ 2327****IF(E4<0,ABS(E4),””) â†’**becomes**IF(E4<0,2327,””) â†’****IFÂ**will returnÂ**2327**whenÂ**E4<0,**otherwise a blank.**Output â†’ Blank**

âž¤ Apply **AutoFill**.

This results in the two negative values without their symbols in theÂ **NegativeÂ **column.

__Step 2 –__Â Plotting Differences in Bar Chart

We will now plot the differences between the prices with their actual values.

âž¤ Select the columnsÂ **Product**,Â **Selling Price** andÂ **Cost PriceÂ **first and then for the rest of the non-adjacent columnsÂ **Base Difference**,Â **Positive** andÂ **Negative**Â select by pressingÂ **CTRL**.

âž¤ Go to theÂ **InsertÂ **Tab >>Â **All Chart TypesÂ **Option.

The **Insert ChartÂ **dialog box will pop up.

âž¤ Go to theÂ **All ChartsÂ **Tab >>Â **BarÂ **>>Â **Stacked BarÂ **>> select your desired type ofÂ **Stacked BarÂ **chart >> pressÂ **OK**.

The bar chart will pop up.

âž¤ Select any series from the combination of the series of the stacked bar chart and thenÂ **Right-Click**.

âž¤ Choose the option **Change Series Chart Type**.

The **Change Chart Type **wizard will open up.

âž¤ Change the **Chart Type **from **Stacked Bar **to **Clustered Bar **and check on the **Secondary Axis **option for the two series ** Selling Price **and

**.**

*Cost Price*âž¤ Press

**OK**.

This results appears as shown in the bar chart.

Rename the chart title asÂ ** Difference Between Selling Price and Cost Price**.

**Read More:Â **How to Make a Bar Graph Comparing Two Sets of Data in Excel

__Method 2__: – Show Difference Between Two Series with Percentage in Excel Bar Chart

__Method 2__

__Step 1 –__Â Using Formulas to Calculate Some Values for Bar Chart

âž¤ Add the following formula in cell **E4**.

`=C4-D4`

âž¤ Apply **AutoFill**.

This results in the differences between theÂ *Selling PriceÂ *values andÂ *Cost PriceÂ *values.

âž¤ For extracting the positive values use the following formula in cell **F4**.

`=IF(E4>0,E4,"")`

âž¤ Drag down the **Fill Handle **tool.

This results in the positive differences in the **positive** column.

âž¤ For extracting the negative values use the following formula in cell **G4**.

`=IF(E4<0,ABS(E4),"")`

**ABS(E4) â†’**Here, the**ABS**function will return the absolute value of the number in cell**E4**neglecting its sign.**Output â†’ 2327****IF(E4<0,ABS(E4),””) â†’**becomes**IF(E4<0,2327,””) â†’ IF**will return**2327**, when**E4<0**otherwise a blank.**Output â†’ Blank**

âž¤ Drag down the **Fill Handle **tool.

This results in the two negative values without their symbols in theÂ **NegativeÂ **column.

âž¤ To gain the percentages of the differences with respect to the cost prices of the products apply the following formula in cell **H4**.

`=E4/D4`

âž¤ To gain the percentages for the rest cells use the **AutoFill **feature of Excel.

This results in the percentage differences of the prices with respect to the cost prices.

__Step 2 –__Â Mapping Values with Error Bars in Bar Chart

âž¤ Select the columns **Product**, **Selling Price**, and **Cost Price** and then go to the **Insert **Tab >> **All Chart Types **Option.

TheÂ **Insert ChartÂ **dialog box will pop up.

âž¤ Go to theÂ **All ChartsÂ **Tab >>Â **BarÂ **>>Â **Clustered BarÂ **>> select your desired type ofÂ **Clustered BarÂ **chart >> pressÂ **OK**.

âž¤ Choose the **Cost Price **series of the chart and then click on the **Chart Elements **icon.

The different options will appear.

âž¤ Click on the arrow symbol beside the **Error Bars **option and choose the option **More Options**.

On the right pane, the **Format Error Bars **wizard will open up.

âž¤ Under the **Horizontal Error Bar **options, choose the **Both **option from ** Direction**, and the

**Cap**option from

**End Style**.

âž¤ Click on the

**Custom**option and then select the

**Specify Value**option.

TheÂ **Custom Error BarsÂ **dialog box will pop up.

âž¤ Select the positive differences in the **Positive Error Value **box and then the negative differences in the **Negative Error Value **box.

âž¤ Press **OK**.

This results in the error bars in the series of the cost prices.

__Step 3 –__Â Modifying Data Labels

âž¤ Click on theÂ **Chart ElementsÂ **option and then check theÂ **Data LabelsÂ **option.

âž¤ Select the data labels of the **Cost Price **series and then choose the **Format Data Labels **option.

TheÂ **Format Data LabelsÂ **dialog box in the right pane opens up.

âž¤ Within the **Label Options **choose the **Outside End **option as the **Label Position**, check on the **Show Leader Lines **option, and the **Value From Cells **option.

TheÂ **Data Label RangeÂ **dialog box will pop up.

âž¤ Choose the range of the percentages in the **Select Data Label Range **box and press **OK**.

Select the data labels of the selling price series.

âž¤ Uncheck the **Value **option.

This results in the bar chart with the percentage differences between the prices.

Name the chart title asÂ ** Percentage Difference**.

**Read More:** How to Show Number and Percentage in Excel Bar Chart

**Download Practice Workbook**

**Related Articles**

- How to Make a Bar Graph with Multiple Variables in Excel
- How to Make a Bar Graph in Excel with 2 Variables
- How to Make a Bar Graph in Excel with 3 Variables
- How to Make a Bar Graph in Excel with 4 Variables
- How to Make a Percentage Bar Graph in Excel
- Excel Bar Chart Side by Side with Secondary Axis
- How to Sort Bar Chart Without Sorting Data in Excel
- How to Change Bar Chart Width Based on Data in Excel

**<< Go Back to Excel Bar Chart |Â Excel ChartsÂ |Â Learn Excel**