# Bar chart - variance calculation

#### juul2222

##### New Member
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?

 Projects Location A Location 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
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

</tbody>
Sheet4

Worksheet Formulas
CellFormula
C2=D2-B2

</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
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
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
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
For a bar chart, try this.

 A B C D E 1 Projects Location B Location A Base Variance 2 NET \$ 11,313,755 \$ 8,295,727 \$ 8,295,727 \$ 3,018,028 3 Incremental EMS/Security \$ - \$ - \$ - \$ - 4 Total CEC \$ 11,313,755 \$ 8,295,727 \$ 8,295,727 \$ 3,018,028 5 Incremental CEC - 505 \$ 1,521,014 \$ 20,042 \$ 20,042 \$ 1,500,972 6 Base 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.

You're on your own for the data labels.

Replies
0
Views
354
Replies
0
Views
643
Replies
10
Views
481
Replies
1
Views
231
Replies
5
Views
14K

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.

### Which adblocker are you using?

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

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