How to create a multi-bar chart

**Gideon1973** · 04-05-2012, 11:36 AM

Hello all!

So, I'm having some challenges with creating a multi-bar chart in Excel 2010. I've created charts in Excel '97 (yes, we've just went through a LONG OVERDUE upgrade!!!), and am having difficulties trying to navigate through the wizard to create the type of chart I'm wanting based on my data.

Attached is an example of what I want the resulting chart to look like in the end along with some sample data.

Maybe this is something that has to be done with expressions/functions or VBA, but perhaps I'm missing something...

Can someone point me in the right direction? Hopefully a little nudge will send me on my way

Thanks!

**JieJenn** · 04-05-2012, 11:41 AM

I am not good with chart, but if you rearrange the data structure and separate date group by an empty row, you might get the result that you're looking for.

**Gideon1973** · 04-05-2012, 12:29 PM

JieJenn,

Thanks for your help!

The blank, vertical lines between the groups/dates weren't necessary -- I only added them for clarity in my example. Sorry for the confusion.

A couple of other questions:

-- How am I able to add the dates along the X axis?
-- How am I able to add a legend to the chart?

Thanks!

**JieJenn** · 04-05-2012, 12:36 PM

For the date, you can use textbox. For legend... I am at work, so you probably need ask someone else on the forum for assistance.

**Gideon1973** · 04-05-2012, 12:44 PM

Thanks again JieJenn. I'm not 100% sure how to implement it with a textbox.

Okay, so does anyone else have any ideas?

One thing to note -- this chart isn't a one-time thing with static data. I will be refreshing the data weekly over time and the number of dates available will increase over time too -- maybe to a maximum of 12 dates. I'm thinking that if I can get a "template" setup, then I can just populate the data sheet with updated data, and then refresh the chart.

Is this something that can be done? I'll keep poking around things, but Excel 2010 is quite a different animal than old, antiquated Excel '97!

**jpr73** · 04-05-2012, 04:24 PM

Hi,
May be a solution with pivot table and pivot chart : see attached
First make a table with your data : automaticaly increase when adding data
Insert a pivot table and a pivot chart with the right format
You can also play with the filter in PT or PC
Don't forget to refresh the pivot table when updating your data
Hope this helps
Best regards

**Gideon1973** · 04-09-2012, 11:25 AM

WOAH! That works perfect. Thanks so much for the idea about a pivot table/chart!

So, I'm going to play around with this, but have a question...I see a SERIES() function on the bars. Is this something that was configured as part of the setup/wizard for the chart or am I going to have to "hand-code" the SERIES() expression?

I'm moving offices today, so I may not get a chance to work back on this today, but will look at it tomorrow sometime.

Thanks again!