Bar chart - variance calculation

juul2222

New Member
Joined
Jan 14, 2017
Messages
18
Rookie here, so take it easy on me. I'm trying to make a simple chart that shows the cost to move into two separate locations, and I'd like to graphically show the variance between location A and B but not as a separate bar. Something like a dotted line between the two with the dollar amount shown. Ideas?

ProjectsLocation ALocation B
Base CEC - 400 $ 8,275,685 $ 9,792,741
Incremental CEC - 505 $ 20,042 $ 1,521,014
Total CEC $ 8,295,727 $ 11,313,755
Incremental EMS/Security $ - $ -
NET $ 8,295,727 $ 11,313,755

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,424
You might consider a Stacked Column. I tweaked your data to look like this:

ABCD
1ProjectsLocation ALocation BLocation B
2Base CEC - 400$8,275,685 $1,517,056 $9,792,741
3Incremental CEC - 505$20,042 $1,500,972 $1,521,014
4Total CEC$8,295,727 $3,018,028 $11,313,755
5Incremental EMS/Security0$0 0
6NET$8,295,727 $3,018,028 $11,313,755

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
C2=D2-B2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



The location B column is now the difference. Now just select A1:C6, from the Insert tab > Recommended Charts > Stacked Column > OK. If you hover your mouse over the orange or blue parts of the bars, you'll see the differential.

Hope this helps.
 

juul2222

New Member
Joined
Jan 14, 2017
Messages
18
Ideally, I would have location A and B side by side, and the actual variance would be displayed. This will be put on an executive summary and the audience won't be able to hover. With that said, A is always lower than B, so if I could stack the variance on top of A, that could work but I can't seem to figure that out.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,424
That's how I originally designed it, with the variance on top of location A. Did you put the variance formula in C, not the actual location B values? And then only select A:C, not A:D?

Another option is to leave the location A and B columns alone, and add another column to the right with Variance as the heading. Put the =C2-B2 formula in column D. Then pick the Clustered Column type of chart.
 

juul2222

New Member
Joined
Jan 14, 2017
Messages
18
First of all, thanks so much! And, yes, I did A:D, but A:C only gives you one bar for each scenario as opposed to the locations side by side. When you do the variance in column D and do a clustered column, the variance shows as a third bar, which isn't a good representation. I found the following link to be the exact thing I'm looking for, but I can't recreate it. When I try to copy and paste the example into my worksheet, the table doesn't come with it. I can recreate the table, but I don't know how to create a stacked chart with some columns but not others.

https://www.excelcampus.com/charts/variance-clustered-column-bar-chart/
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,074
For a bar chart, try this.

A
BCDE
1Projects Location B Location A Base Variance
2NET $ 11,313,755 $ 8,295,727 $ 8,295,727 $ 3,018,028
3Incremental EMS/Security $ - $ - $ - $ -
4Total CEC $ 11,313,755 $ 8,295,727 $ 8,295,727 $ 3,018,028
5Incremental CEC - 505 $ 1,521,014 $ 20,042 $ 20,042 $ 1,500,972
6Base CEC - 400 $ 9,792,741 $ 8,275,685 $ 8,275,685 $ 1,517,056

<tbody>
</tbody>


Note: Location A and Location B columns have been interchanged. The order of projects has been reversed. Bar charts require some non-intuitive data-source configurations.

D2:: =C2
E2:: =MAX(B2:C2)-MIN(B2:C2)

Select A1:E6. Insert a Stacked Bar chart.

Select the vertical axis. Bring up the Format pane, Ctrl+1. Select the Bar chart icon, "Axis Options".

Click on Location B series in the chart. In the format pane, set it on the secondary axis. Repeat for the Location A series.

Tip: You can select chart items directly in the format pane. The second line in the pane, just above the icons, has a small downward pointing arrow. Click on the little arrow and make your item selection in the pop-up list box.

From the Design tab, or from the chart's right-click context menu, click on "Change Chart Type". Change one of Location A or Location B series in the pop-up to "Clustered Bar". The other Location series should change simultaneously.

From there, you should be able to follow the ExcelCampus tutorial.

I found Gap widths of 60% and 30% seemed to look OK. To eliminate "Base" from the Legend, select the legend, then select "Base" and press Delete. I formatted the upper axis to the Custom Number Format:
$ 0,,"M";;$ 0
As a final step, I deleted the lower axis.

xQZ3bbP.png


You're on your own for the data labels.
 

Forum statistics

Threads
1,171,629
Messages
5,876,533
Members
433,199
Latest member
guerin47

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top