I have sent in an updated version of my solution that combines the layout that you suggest and the dynamic updating that my original solution has.

This has proven to be a worthy challenge. 🙂

Good job Steve. I thought I had tried every combination of data, but I would never have thought of this one.

Thanks

Thanks for this solution, do you also have one for adding error bars to this chart?

Tried this 3 times in XL2010 but it turned out a pig’s breakfast each time. Then manually selected the data and it worked perfect. Great outcome, thanks.
Eg. Series 1 = data from C2:C22
Series 2 = data for D2:D22, etc.
X-values in series 1 = A2:B22.

Thank you for having this challenge still posted out here. My chart just amazed my Executive. I feel like a genius!!

Can i change the Series to say something different or remove it completely this is great and just to solution i needed but i don’t need the series headings.

Wow, this is a really cool technique.
Unfortunately, the vast majority of users I know (including IT people) would tell me that VBA is not an option for them which often means missing out on good stuff like Steve’s macro. So I tried reproducing the result using a pivot table, a really cool Excel feature that has been covered on many pages here at EDT. It works just great!
Allow me to share:
1. Analyse your data set (If you have none, grab the data from the table at the beginning of this article and follow along!). In order to use the pivot table feature, you will want to have your data in the list format, in case of this article’s sample data like this:
Series Subject Value
Series 1 Subject 2 1
Series 1 Subject 3 1
Series 2 Subject 3 1
Series 3 Subject 1 5
…
If your data is in the list format, go to step 2. Otherwise, your data may be in a format like the data set at the beginning of this article. That is, the possible values of one field (“Subject”) are not in a column but spread out along the horizontal axis like the “Subject 1||Subject 2||Subject 3”. This format can be thought of as the result of pivoting the Subject column to the the horizontal axis. So in order to obtain a list-format data set, we need to “unpivot” the subject field back into a column. And that is exactly what this great article is about.
https://www.exceldashboardtemplates.com/how-to-convert-an-existing-excel-data-set-to-a-pivot-table-format/
If you followed the steps at the link destination, your resulting list-formatted data set will be an Excel table – auto-formatted and with drop-down menus next to the field titles. If your original data set had empty cells like this article’s sample data, the list will contain some records with an empty Value field. You can then use the filter in the drop-down menu to filter out all empty-Value records and copy the filter result over to a new sheet.
2. In order to have spaces between the main category (“Subject”) clusters, we need to add to our list as many dummy rows with Series being a single space and Subject being incrementing number of space (one, two, three). We use spaces because we need something invisible and the number of spaces – and thereby the value – must be different because otherwise the pivot table would aggregate those multiple records into one.
3. Copy the subcategory column (“Series”) and paste it to the right of your set. I explain why later.
4. Select some cell in your data list and press Alt > N > V > C. Follow the wizard to create a pivot chart. You will have the pivot table panel on the right of the Excel window. Now drag the main category (“Subject”) from the field list to the ROWs box, then drag the subcategory (“Series”) below that. Since you copied the “Series” column in your data, you can drag the corresponding field to the COLUMNS box and lastly the “Value” field to the VALUES box. And boom! You can already see the pivot chart that looks pretty much like the desired result. Since we added those rows with spaces to the data list, those show up at the top of the vertical axis. Hover your mouse pointer over one of those at a time till the pointer becomes a cross with arrow tips and drag that space “Subject” between the actual subjects to create the spacing.
5. If all the bars have the same height now – 1 -, open the drop-down menu from the arrow next to the “Value” item in the VALUES box, select field settings and change the aggregation mode from count to sum.
If someone at EDT likes this trick, maybe they can make a video about it. It looks like a lot to do but is actually more easily done than explained and contains a bunch of useful techniques.

When I try this, I get lines in between each Series Label in the chart and a slightly longer line separating the Subjects. In format table options I select no line but unfortunately gets rid of each line (including between subjects). Any way to get rid of the lines between Series and not the Subjects? Thanks

Hi there, such a cool tutorial, it helped me sort out my problem too. But now I am stuck again because I need to add error bars to my chart and the way I usually do it (creating a table with inputed error values as the custom value for the error) doesn’t work…I really need help with this. Many thanks in advance! Happy new year everyone!

Thank you so much Steve. This came as a timely help for me. Much appreciated.

This is fun…..do we think this would be possible if creating floating bars? I can’t quite get my head wrapped around it. Lots of floating bars made here, and above is right on track for good visual, but I am showing salary ranges (for same position, at multiple employers, not all employers reported a response for each position (hence me finding this for closing those gaps)), but no salary begins at zero of course….!

Didn’t work for me.