Bar chart - variance calculation

juul2222

New Member
Joined
Jan 14, 2017
Messages
16
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>
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,234
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
16
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
10,234
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
16
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.



You're on your own for the data labels.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,147
Members
409,804
Latest member
aceyus_michael

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top