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

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to show the difference between two series with the help of a Bar chart in Excel, then this article is for you. So, let’s get started with our main article.


Excel Bar Chart to Show Difference Between Two Series: 2 Ways

Here, we have 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. Using the following two methods we will try to indicate the differences in actual values format and percentage format.

excel bar chart show difference between two series


Method-01: Show Difference with Actual Values Between Two Series in Excel Bar Chart

In this section, we will manifest the differences between the selling prices and the cost prices with their actual values in a bar chart. To do this we have added some extra columns like Difference, Base Difference, Positive, and Negative.

excel bar chart show difference between two series


Step-01: Using Formulas to Calculate Some Values for Bar Chart

➤ Type the following formula in cell E4.

=C4-D4

Here, C4 is the Selling Price value, and D4 is the value of the Cost Price.

formulas for actual values with difference

➤ Press ENTER and drag down the Fill Handle tool.

formulas for actual values with difference

Then, we will get the differences between the selling prices and cost prices; the values within the brackets represent the negative values.

excel bar chart show difference between two series

➤ For determining the minimum value between the selling price and the cost price to have the base difference between them, we will use the following formula.

=MIN(C4,D4)

Here, the MIN function will define the minimum value between the selling and cost prices.

formulas for actual values with difference

➤ Use the AutoFill feature of Excel.

formulas for actual values with difference

Finally, we have the base differences between the selling prices and the cost prices.

excel bar chart show difference between two series

Now, we will 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 then the IF function will return this positive value otherwise a blank.

formulas for actual values with difference

➤ Press ENTER and drag down the Fill Handle tool.

formulas for actual values with difference

After that, you will get the positive differences in the Positive column.

formulas for actual values with difference

➤ For extracting the negative values use the following formula in cell H4.

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

Here, 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

excel bar chart show difference between two series

➤ Use the AutoFill feature of Excel.

formulas for actual values with difference

Finally, we are having the two negative values without their symbols in the Negative column.

formulas for actual values with difference


Step-02: Plotting Differences in Bar Chart

Now, we will plot the differences between the prices with their actual values.

excel bar chart show difference between two series

➤ 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 them by pressing CTRL.
➤ Now, go to the Insert Tab >> All Chart Types Option.

plotting actual values with difference

After that, the Insert Chart dialog box will appear.
➤ Go to the All Charts Tab >> Bar >> Stacked Bar >> select your desired type of Stacked Bar chart >> press OK.

plotting actual values with difference

Then, the bar chart will appear.
➤ Select any series from the combination of the series of the stacked bar chart and then Right-Click here.

plotting actual values with difference

➤ Choose the option Change Series Chart Type.

excel bar chart show difference between two series

Afterward, 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.

plotting actual values with difference

After that, we will get the following appearance of the bar chart.

plotting actual values with difference

Moreover, you can name the chart title as Difference Between Selling Price and Cost Price.

excel bar chart show difference between two series

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


Method-02: Show Difference Between Two Series with Percentage in Excel Bar Chart

In this method, we will try to show the percentage differences between the two series of prices in a bar chart.

percentage difference


Step-01: Using Formulas to Calculate Some Values for Bar Chart

➤ Type the following formula in cell E4.

=C4-D4

Here, C4 is the Selling Price value, and D4 is the value of the Cost Price.

➤ Use the AutoFill feature of Excel.

formulas for percentage difference

In this way, you will get the differences between the Selling Price values and Cost Price values.

formulas for percentage difference

Now, we will 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 F4.

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

When the value in cell E4 is positive IF will return this positive value otherwise a blank.

➤ Drag down the Fill Handle tool.

excel bar chart show difference between two series

In this way, you will have positive differences in the Positive column.

excel bar chart show difference between two series

➤ For extracting the negative values use the following formula in cell G4.

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

Here, E4 is the difference between the prices.

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

formulas for percentage difference

Eventually, we will have the two negative values without their symbols in the Negative column.

formulas for percentage difference

➤ 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

Here, D4 is the Cost Price and E4 is the difference between the prices.

➤ To gain the percentages for the rest cells use the AutoFill feature of Excel.

excel bar chart show difference between two series

Eventually, we will have percentage differences in the prices with respect to the cost prices.

formulas for percentage difference


Step-02: Mapping Values with Error Bars in Bar Chart

Here, we will plot the percentage differences in the bar chart.
➤ Select the columns Product, Selling Price, and Cost Price and then go to the Insert Tab >> All Chart Types Option.

excel bar chart show difference between two series

After that, the Insert Chart dialog box will appear.
➤ Go to the All Charts Tab >> Bar >> Clustered Bar >> select your desired type of Clustered Bar chart >> press OK.

plotting percentage differences

➤ Choose the Cost Price series of the chart and then click on the Chart Elements icon.

plotting percentage differences

After that, different options will appear.
➤ Click on the arrow symbol beside the Error Bars option and choose the option More Options.

excel bar chart show difference between two series

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.

excel bar chart show difference between two series

Afterward, the Custom Error Bars dialog box will open up.
➤ Select the positive differences in the Positive Error Value box and then the negative differences in the Negative Error Value box.
➤ Press OK.

plotting percentage differences

In this way, you will get the error bars in the series of the cost prices.

excel bar chart show difference between two series


Step-03: Modifying Data Labels

➤To show the percentages click on the Chart Elements option and then check the Data Labels option.

excel bar chart show difference between two series

➤ Select the data labels of the Cost Price series and then choose the Format Data Labels option.

modifying data labels

In this way, you will have the Format Data Labels dialog box in the right pane.
➤ 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.

excel bar chart show difference between two series

Afterward, the Data Label Range dialog box will appear.
➤ Choose the range of the percentages in the Select Data Label Range box and press OK.

excel bar chart show difference between two series

Now, select the data labels of the selling price series.

excel bar chart show difference between two series

➤ Then, uncheck the Value option.

modifying data labels

In this way, you will get the following bar chart with the percentage differences between the prices.

modifying data labels

In addition, you can name the chart title as Percentage Difference.

excel bar chart show difference between two series

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


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

practice


Download Practice Workbook


Conclusion

In this article, we tried to cover the steps to show the difference between the two series with a bar chart in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles


<< Go Back to Excel Bar ChartExcel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo