# How to Show Difference Between Two Series in Excel Bar Chart (2 Ways)

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.

### Method 2: – Show Difference Between Two Series with Percentage in Excel Bar Chart

#### 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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF