Excel 2013 - Clustered Column Chart w/ Multiple Data Series 1

[beadedbytes]

Please see the example data below. In each Grouping (i.e. A,B,C), '-T' is the total audience for the Group; '-R' is a subset of the total audience.

I've been trying to figure out how to create a clustered column chart that includes more than one Grouping per quarter (i.e. A-T/A-R, B-T/B-R, C-T/C-R). Also, within each Grouping per Qtr, I want '-R' to overlap 100% with '-T'.

If I were working with just one Grouping per Qtr, I know how to create the clustered column chart and overlap. However, I've not been successful in creating this type of chart with multiple Groupings per Qtr.

Any solutions to share? Thanks in advance for feedback!

Volume	GrpA-T	GrpA-R	GrpB-T	GrpB-R	GrpC-T	GrpC-R
Qtr1	173586	47044	37232	10090	37122	10061
Qtr2	191243	45334	48236	11434	44673	10590
Qtr3	192623	39544	79882	16399	48704	9998
Qtr4	135439	34787	58483	15021	27911	7169

 

[SkipVought]

Hi,

Insert a column between the groups and plot the range including the empty columns. The result will give the illusion of three clusters.

 

[beadedbytes]

Hi, Skip,

Thanks always for feedback. Unfortunately, it did not produce what I desired. Let me see if I can describe below.

Basically, the Y-axis would have 4 labels - Q1, Q2, Q3, A4; X-axis would have the quantities.

Above each label, there would be 3 vertical bars (cluster bars) - GrpA-T, GrpB-T & GrpC-T.

Each vertical bar would be 'overlayed' with GrpA-R, GrpB-R & GrpC-R (which is the smaller subset of GrpA-T, GrpB-T & GrpC-T). To create this effect, I would be using the following Excel Chart feature: Format Data Series >> Series Options >> Series Overlap

So, the result would still be 4 'Qtr' labels with each having a cluster of just 3 bars. Each bar would have a 'stacked' appearance.

Make sense?

 

[SkipVought]

so are you saying...

3 clusters (A,B,C) of 2 stacks (R,T) each for 4 quarters?


A-R B-R C-R A-R B-R C-R A-R B-R C-R A-R B-R C-R
A-T B-T C-T A-T B-T C-T A-T B-T C-T A-T B-T C-T
--------------------------------------------------
Q-1 Q-2 Q-3 Q-4

 

[SkipVought]

SORRY...

[PRE]
A-R B-R C-R A-R B-R C-R A-R B-R C-R A-R B-R C-R
A-T B-T C-T A-T B-T C-T A-T B-T C-T A-T B-T C-T
--------------------------------------------------
A B C A B C A B C A B C
Q-1 Q-2 Q-3 Q-4

[/PRE]

 

[SkipVought]

I'm going on that assumption

Change your chart
[pre]
Qtr Grp T R
Qtr1 A 173586 47044
Qtr1 B 37232 10090
Qtr1 C 37122 10061

Qtr2 A 191243 45334
Qtr2 B 48236 11434
Qtr2 C 44673 10590

Qtr3 A 192623 39544
Qtr3 B 79882 16399
Qtr3 C 48704 9998

Qtr4 A 135439 34787
Qtr4 B 58483 15021
Qtr4 C 27911 7169
[/pre]

select Grp, T, R and make a stacked chart

RIght-click in the chart and select Select Data. edit the horizontal (category axis) to include column A&B

 

[beadedbytes]

Sorry, Skip, for being out-of-pocket. Once again, your solution serves my needs.

FYI ... If I use Excel's Merge & Center feature for each Qtr in column A, it displays the info in columns A&B beautifully.

Thank you again for YEAR's of thoughtful advice that WORK!

Best, Best, Best Always,
BeadedBytes