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.
Download Workbook
2 Ways to Show Difference Between Two Series in Excel Bar Chart
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.
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.
Step-01: Using Formulas to Calculate Some Values for Bar Chart
➤ Type the following formula in cell E4.
=C4-D4Here, C4 is the Selling Price value, and D4 is the value of the Cost Price.
➤ Press ENTER and drag down the Fill Handle tool.
Then, we will get 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 have the base difference between them, we will use the following formula.
=MIN(C4,D4)Here, MIN function will define the minimum value between the selling and cost prices.
➤ Use the AutoFill feature of Excel.
Finally, we are having the base differences between the selling prices and the cost prices.
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 IF will return this positive value otherwise a blank.
➤ Press ENTER and drag down the Fill Handle tool.
After that, you will get the positive differences in the Positive column.
➤ For extracting the negative values use the following formula in cell H4.
=IF(E4Here, E4 is the difference between the prices.
- ABS(E4) → ABS function will return the absolute value of the number in cell E4 neglecting its sign.
- Output → 2327
- IF(E4becomes
- IF(E4will return 2327, when E4otherwise a blank.
- Output → Blank
➤ Use the AutoFill feature of Excel.
Finally, we are having the two negative values without their symbols in the Negative column.
Step-02: Plotting Differences in Bar Chart
Now, we will plot the differences between the prices with their actual values.
➤ Select the columns Product, Selling Price, and Cost Price firstly, 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.
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.
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.
➤ Choose the option Change Series Chart Type.
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.
After that, we will get the following appearance of the bar chart.
Moreover, you can name the chart title as Difference Between Selling Price and Cost Price.
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.
Step-01: Using Formulas to Calculate Some Values for Bar Chart
➤ Type the following formula in cell E4.
=C4-D4Here, C4 is the Selling Price value, and D4 is the value of the Cost Price.
➤ Use the AutoFill feature of Excel.
In this way, you will get the differences between the Selling Price values and Cost Price values.
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.
In this way, you will have positive differences in the Positive column.
➤ For extracting the negative values use the following formula in cell G4.
=IF(E4Here, E4 is the difference between the prices.
- ABS(E4) → ABS will return the absolute value of the number in cell E4 neglecting its sign.
- Output → 2327
- IF(E4becomes
- IF(E4will return 2327, when E4otherwise a blank.
- Output → Blank
➤ Drag down the Fill Handle tool.
Eventually, we are having 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/D4Here, 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.
Eventually, we will have percentage differences in the prices with respect to the cost prices.
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.
