Excel - a charting question!

[Jonboy_t]

I do this all day every day, but am just stuck fast on something and can't get my head around how to do it so hoping someone more intelligent than me can flick a switch in my brain for me!

I have 2 datasets, both based on the same three parameters - 'value', 'month' and 'category'. I need to stick this into a Stacked Bar-type chart to show per month, what the 'value' is in each of the 'categories', childish drawing of what I'm imagining is below



This is a screenshot of the datasets. I've no idea what I'm missing, but I just cannot get Excel to do what I need! I can't even get it to chart one of the datasets properly



Any ideas?!

[Munter]

So you want a stacked bar chart for each month, showing each categories value, stacked on each other. Is that right?

[Munter]

[Munter]

Or do you need to sum each column for the 2 data sets:

[Jonboy_t]

Munter said:

Or do you need to sum each column for the 2 data sets:

That one, but it needs to have categories on the vertical axis and month on the horizontal with the chart showing both datasets stacked

[Munter]

Jonboy_t said:

Munter said:

Or do you need to sum each column for the 2 data sets:

That one, but it needs to have categories on the vertical axis and month on the horizontal with the chart showing both datasets stacked

I think you are visualising an impossibility. You can't use the category as the vertical scale on a bar chart. What would be the value to be plotted on the chart? (Unless you are counting instances of each category)

Are you sure you're not after a 3 dimensional scatter graph. Showing a point where category month and value intersect? (No I'm not sure how to do that )

I guess the other question is. What answer are you looking for from the data?

[Jonboy_t]

Munter said:

Jonboy_t said:

Munter said:

Or do you need to sum each column for the 2 data sets:

That one, but it needs to have categories on the vertical axis and month on the horizontal with the chart showing both datasets stacked

I think you are visualising an impossibility. You can't use the category as the vertical scale on a bar chart. What would be the value to be plotted on the chart? (Unless you are counting instances of each category)

Are you sure you're not after a 3 dimensional scatter graph. Showing a point where category month and value intersect? (No I'm not sure how to do that )

I guess the other question is. What answer are you looking for from the data?

To be honest, the more I look at it, the more I agree that it's not possible. It's trying to plot something with 3 categories and it's just never going to work. In all fairness though, I think that's probably the answer that I need to give! The boss has this great habit of coming up with these 'could you just..' moments which inevitably turn into (like this one has) over 100 individual macros that output something he looks at once, humphs a little bit and then never looks at again.

The end result is supposed to be an exec summary that visualises something like 'in June, the category with the highest sales and highest margin was x/y/z' (margin and sales aren't the metrics, btw), but it's going to have to just go on two individual charts and the big bods will need to use their eyes to make sense of it

Last chance saloon would be to do each month on an individual graph and just position them so they look like they flow from one to the other.

[mmm-five]

Jonboy_t said:

To be honest, the more I look at it, the more I agree that it's not possible. It's trying to plot something with 3 categories and it's just never going to work. In all fairness though, I think that's probably the answer that I need to give! The boss has this great habit of coming up with these 'could you just..' moments which inevitably turn into (like this one has) over 100 individual macros that output something he looks at once, humphs a little bit and then never looks at again.

I have a similar 'boss'.

Keeps asking me to do a chart, with the supplied data, then asks me to make one bit or another 'look' bigger. So I make it stand out with a bolder colour...but no, he want's it BIGGER.

He's just found bubble charts too! FFS

[Munter]

Jonboy_t said:

The end result is supposed to be an exec summary that visualises something like 'in June, the category with the highest sales and highest margin was x/y/z' (margin and sales aren't the metrics, btw), but it's going to have to just go on two individual charts and the big bods will need to use their eyes to make sense of it

Last chance saloon would be to do each month on an individual graph and just position them so they look like they flow from one to the other.

Sounds like a job for Splunk. (Which I await my Splunk for Dummies training on)

We've exhausted my skills here. I wish you luck!

[Zetec-S]

I'm probably misunderstanding what you're after, but is it anything like this?

[mmm-five]

Zetec-S said:

I'm probably misunderstanding what you're after, but is it anything like this?

I'd probably do something like that, but prefix the categories with the 2 group names, and then stack them as one column.

So you'd have RNOS-Label1, RNOS-Label2, ... and then NOOS-Label1, NOOS-Label2, ...



But I'd want the 2 categories to be different colour schemes so the can be easily differentiated.

Edited by mmm-five on Friday 12th April 16:33

[Abbott]

Is this the sort of thing you are looking for?

[Jonboy_t]

Cheers for the ideas chaps. Whilst they would sort of do the job, I’m not sure they’re exactly what’s needed for it as they’re not as ‘clean’ as the chief is after - he just wants one line per category with the 2 values in it . He also wants the moon on a stick and some grated rainbow and unicorn tear soup, but that’s another matter

[Whoozit]

As per other posters, that doesn't compute, sorry.

I'd ask if it is more important to see the total amount sold per month per sub-cat, or by type of widget? That should get him thinking.

Also, as additional analysis, absolute and % change per month?

[bigandclever]

This any use to you?

“Vertical Category Axis.

How do you arrange your chart so the categories are displayed along the Y axis? The method involves adding a dummy series along the Y axis, applying data labels to its points for category labels, and making the original Y axis disappear.”

https://peltiertech.com/Excel/Charts/Y_CategoryAxi...