Making min-max-average bar charts work

jaakovs

New Member
Joined
May 22, 2017
Messages
12
Hello everybody,


I'm trying to recreate this graphical representation for my data, which is an aggregation that consists of many series each with three columns: min, max, and average in the range from 1.0 to 4.0. I've experimented with floating bar charts, where I've created a fourth column with the difference between the min and the max, and then plotted that column stacked with the min values, with the bar for the min values made invisible.


Do any of you have ideas on how to recreate this visualization? Is there a way for me to plot the averages with markers similar to those pictured? What's the best way for me to overlay this color-coded range?

scXwjKY.jpg



Thanks so much!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hello

o Here is a link to my test workbook: Dropbox - line-chart-with-bands.xlsm
o I used some VBA to format text boxes and data labels.
o Tell me if you need further explanations


Code:
Sub DL()
Dim dtlb As DataLabel, sh As Shape, a, i%
a = Array("tb6", "tb7", "tb9", "tb8")
For i = LBound(a) To UBound(a)
    Set sh = ActiveSheet.Shapes(a(i))
    sh.Left = ActiveChart.Parent.Left + ActiveChart.PlotArea.InsideLeft + i * 82.5 + 4
    sh.Top = 384
    sh.Width = 82.5
    sh.Height = 19
Next
For Each dtlb In ActiveChart.SeriesCollection(5).DataLabels
    dtlb.Height = 17
    dtlb.Width = 7
Next
End Sub


0sUZK62.jpg
 
Upvote 0
Thanks so much for working on a solution for my problem. I examined the spreadsheet that you sent me.


Is it possible to have the gray bars match the range between the minimum and maximum values for each series? I've got my source data organized as follows, where the numerical columns are max, min, and average values for each series, respectively.

2tjJynO.jpg



Also, I'm new to VBA, so I'm doing my best to understand how I can take your code and apply it to my own data. Can you explain what parts of the code I need to modify so that it reads off my data and produces that visualization in my own worksheet?


Thanks! I appreciate the input.
 
Upvote 0
Yes, it is possible, see example below.
I will prepare a worksheet layout that respects your source data structure and generates the chart.

fqni0TX.jpg
 
Upvote 0
See below how it looks like, the formulas used and a link to the workbook. Note that no VBA is required.


Plan2 (2)

CDEFGHIJKL
34Business1,750,750,750,75020,80,061,17
35Mining1,750,750,750,75010,170,060,8
36Stability1,750,750,750,75011,470,060,5
37Skills1,750,750,750,75021,220,060,75
38Health1,750,750,750,75010,720,060,25

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
I34=C3
J34=D3-C3-0.03
L34=B3-D3
I35=C4
J35=D4-C4-0.03
L35=B4-D4
I36=C5
J36=D5-C5-0.03
L36=B5-D5
I37=C6
J37=D6-C6-0.03
L37=B6-D6
I38=C7
J38=D7-C7-0.03
L38=B7-D7

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4




KbBg9Bs.png



Dropbox - chart-with-bands.xlsm
 
Last edited:
Upvote 0
Hello Worf - your solution at has worked really well! I recommend this to anyone who's looking at creating something similar. Thank you so much for all the time and effort you put into it.

Quick question - the chart reads my source data in reverse order for some reason. In other words, the first series (and corresponding bar) are the last ones in my dataset. Do you know why this is happening and how I might be able to fix it?
 
Upvote 0
Quick question - the chart reads my source data in reverse order for some reason. In other words, the first series (and corresponding bar) are the last ones in my dataset. Do you know why this is happening and how I might be able to fix it?
The default for bar charts is to have the first category at the bottom-most position. Select the vertical axis, the category axis, and in the formatting options under Size, will be a checkbox labeled "Categories in reverse order". Check that box and the chart will display as you want.
 
Upvote 0
The default for bar charts is to have the first category at the bottom-most position. Select the vertical axis, the category axis, and in the formatting options under Size, will be a checkbox labeled "Categories in reverse order". Check that box and the chart will display as you want.

I selected the vertical axis and under axis options, I checked the "categories in reverse order" box. It reversed the categories, but not the bars associated with them - they were unchanged. So when I check that option, the bars no longer reflect the correct data for each category.
 
Upvote 0
I should have known this just by looking at the chart: there are two vertical axes. Reversing the categories for the primary vertical axis is only half the job.

The other vertical axis:
When you select the chart or any item on the chart, three helper icons appear to the right of the chart. Click on the "plus" icon, click on the small rightward pointing triangle and check the box for the secondary vertical axis to display.

44STcHj.png


Reverse the categories on this axis as well as the primary axis. Then bring up the Chart Elements menu again and uncheck the secondary vertical axis checkbox. I believe that should fix everything.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top