Pivot Charts for non tabular data not slicing by date

[Greenbehindthecells]

Good afternoon,

I hope you are well. I am having a bit of trouble with Pivot Charts. I would need to make a stacked bar pivot chart for a dynamic sheet. The dynamic section would need to be the monthly Numerator and Denominator (also, if possible, the Rate). My data is not tabular in the way the Pivot Table wants it... I think. I can make a Pivot Chart for all February Data (Numerator, Denominator, Rate) but it will not dynamic due- any time I add the other months data, excel starts adding all the numerators to the numerator, denominators to the denominator, etc... This data cant be summed together as it would be counting the same data multiple times.

I am trying to make a dynamic chart where I can use slicers to change the Numerator, Denominator, Rate from February to March to April,etc. When I transpose the data, I cant make the data into a pivot chart (it doesn't show any data). I am not sure what I am doing wrong. I feel like its the way my data is due to having 3 different values for the months. I would appreciate any tips to help make this monthly data be able to be included in a pivot chart so I can slice it by month for a dynamic sheet. Thank you in advance for looking at this.


If I add all the month Numerator, Denominator, Rate to my Pivot Chart and then try to slice it after, it gives me a few ways to slice but not how I would need it to be.

This is what I would like the pivot chart to be able to look like after using slicers for February, March, April, etc.

How the Pivot Table for this data looks:

Pivot Table:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2
3	Row Labels	Sum of Num Feb	Sum of Den Feb	Sum of Rate Feb
4	Blah1	1	1	100.00%
5	Blah10	10	100	10.00%
6	Blah11	11	110	10.00%
7	Blah12	12	120	10.00%
8	Blah13	13	130	10.00%
9	Blah14	14	140	10.00%
10	Blah15	15	150	10.00%
11	Blah16	16	160	10.00%
12	Blah17	17	170	10.00%
13	Blah18	18	180	10.00%
14	Blah19	19	190	10.00%
15	Blah2	0	2	0.00%
16	Blah20	20	200	10.00%
17	Blah21	21	210	10.00%
18	Blah22	22	220	10.00%
19	Blah23	23	230	10.00%
20	Blah3	0	1	0.00%
21	Blah4	0	1	0.00%
22	Blah5	1	400	0.25%
23	Blah6	0	1000	0.00%
24	Blah7	7	700	1.00%
25	Blah8	8	80	10.00%
26	Blah9	9	90	10.00%
27	Grand Total	257	4585	261.25%
28
Sheet10

Table of original Data and how its formated:

Book1.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
4	Column1	Num Feb	Den Feb	Rate Feb	Perc Feb	Num March	Den March	Rate March	Perc March	Num April	Den April	Rate April	Perc April
5	Blah1	1	1	100.00%	95th	1	1	100.00%	95th	1	1	100.00%	95th
6	Blah2	0	2	0.00%	<5th	0	2	0.00%	<5th	0	2	0.00%	<5th
7	Blah3	0	1	0.00%	<5th	0	1	0.00%	<5th	0	1	0.00%	<5th
8	Blah4	0	1	0.00%	<5th	0	1	0.00%	<5th	0	1	0.00%	<5th
9	Blah5	1	400	0.25%	<5th	1	400	0.25%	<5th	1	400	0.25%	<5th
10	Blah6	0	1000	0.00%	<5th	0	1000	0.00%	<5th	0	1000	0.00%	<5th
11	Blah7	7	700	1.00%	<5th	7	700	1.00%	<5th	7	700	1.00%	<5th
12	Blah8	8	80	10.00%	<5th	8	80	10.00%	<5th	8	80	10.00%	<5th
13	Blah9	9	90	10.00%	<5th	9	90	10.00%	<5th	9	90	10.00%	<5th
14	Blah10	10	100	10.00%	<5th	10	100	10.00%	<5th	10	100	10.00%	<5th
15	Blah11	11	110	10.00%	<5th	11	110	10.00%	<5th	11	110	10.00%	<5th
16	Blah12	12	120	10.00%	<5th	12	120	10.00%	<5th	12	120	10.00%	<5th
17	Blah13	13	130	10.00%	<5th	13	130	10.00%	<5th	13	130	10.00%	<5th
18	Blah14	14	140	10.00%	<5th	14	140	10.00%	<5th	14	140	10.00%	<5th
19	Blah15	15	150	10.00%	<5th	15	150	10.00%	<5th	15	150	10.00%	<5th
20	Blah16	16	160	10.00%	<5th	16	160	10.00%	<5th	16	160	10.00%	<5th
21	Blah17	17	170	10.00%	<5th	17	170	10.00%	<5th	17	170	10.00%	<5th
22	Blah18	18	180	10.00%	<5th	18	180	10.00%	<5th	18	180	10.00%	<5th
23	Blah19	19	190	10.00%	<5th	19	190	10.00%	<5th	19	190	10.00%	<5th
24	Blah20	20	200	10.00%	<5th	20	200	10.00%	<5th	20	200	10.00%	<5th
25	Blah21	21	210	10.00%	<5th	21	210	10.00%	<5th	21	210	10.00%	<5th
26	Blah22	22	220	10.00%	<5th	22	220	10.00%	<5th	22	220	10.00%	<5th
27	Blah23	23	230	10.00%	<5th	23	230	10.00%	<5th	23	230	10.00%	<5th
Year pasted values

 

[awoohaw]

What is the reasoning behind having a multiple columns for each month of data. It is nearly impossible for you to achieve "dynamics" with how you have your data as you are forcing yourself to "realign" the source data for your chart(s).

Is there a compelling reason why you need to structure your data this way? You have 2 criteria (column and month) and 4 measurement (num, den, rate, perc) those are the only columns you need. And your month should actually be a date (you can format to display only month) - which will allow you to slice your data by month.

I have unpivoted your crosstab into a flat file, with this you can build your charts as you desire:

Mr excel questions 39.xlsm
			C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
4			Column1	Num Feb	Den Feb	Rate Feb	Perc Feb	Num March	Den March	Rate March	Perc March	Num April	Den April	Rate April	Perc April		Column1	Month	Num	Den	Rate	Perc
5			Blah1	1	1	1	95th	1	1	1	95th	1	1	1	95th		Blah1	Feb	1	1	1	95th
6			Blah2	0	2	0	<5th	0	2	0	<5th	0	2	0	<5th		Blah2	Feb	0	2	0	<5th
7			Blah3	0	1	0	<5th	0	1	0	<5th	0	1	0	<5th		Blah3	Feb	0	1	0	<5th
8			Blah4	0	1	0	<5th	0	1	0	<5th	0	1	0	<5th		Blah4	Feb	0	1	0	<5th
9			Blah5	1	400	0.0025	<5th	1	400	0.0025	<5th	1	400	0.0025	<5th		Blah5	Feb	1	400	0.0025	<5th
10			Blah6	0	1000	0	<5th	0	1000	0	<5th	0	1000	0	<5th		Blah6	Feb	0	1000	0	<5th
11			Blah7	7	700	0.01	<5th	7	700	0.01	<5th	7	700	0.01	<5th		Blah7	Feb	7	700	0.01	<5th
12			Blah8	8	80	0.1	<5th	8	80	0.1	<5th	8	80	0.1	<5th		Blah8	Feb	8	80	0.1	<5th
13			Blah9	9	90	0.1	<5th	9	90	0.1	<5th	9	90	0.1	<5th		Blah9	Feb	9	90	0.1	<5th
14			Blah10	10	100	0.1	<5th	10	100	0.1	<5th	10	100	0.1	<5th		Blah10	Feb	10	100	0.1	<5th
15			Blah11	11	110	0.1	<5th	11	110	0.1	<5th	11	110	0.1	<5th		Blah11	Feb	11	110	0.1	<5th
16			Blah12	12	120	0.1	<5th	12	120	0.1	<5th	12	120	0.1	<5th		Blah12	Feb	12	120	0.1	<5th
17			Blah13	13	130	0.1	<5th	13	130	0.1	<5th	13	130	0.1	<5th		Blah13	Feb	13	130	0.1	<5th
18			Blah14	14	140	0.1	<5th	14	140	0.1	<5th	14	140	0.1	<5th		Blah14	Feb	14	140	0.1	<5th
19			Blah15	15	150	0.1	<5th	15	150	0.1	<5th	15	150	0.1	<5th		Blah15	Feb	15	150	0.1	<5th
20			Blah16	16	160	0.1	<5th	16	160	0.1	<5th	16	160	0.1	<5th		Blah16	Feb	16	160	0.1	<5th
21			Blah17	17	170	0.1	<5th	17	170	0.1	<5th	17	170	0.1	<5th		Blah17	Feb	17	170	0.1	<5th
22			Blah18	18	180	0.1	<5th	18	180	0.1	<5th	18	180	0.1	<5th		Blah18	Feb	18	180	0.1	<5th
23			Blah19	19	190	0.1	<5th	19	190	0.1	<5th	19	190	0.1	<5th		Blah19	Feb	19	190	0.1	<5th
24			Blah20	20	200	0.1	<5th	20	200	0.1	<5th	20	200	0.1	<5th		Blah20	Feb	20	200	0.1	<5th
25			Blah21	21	210	0.1	<5th	21	210	0.1	<5th	21	210	0.1	<5th		Blah21	Feb	21	210	0.1	<5th
26			Blah22	22	220	0.1	<5th	22	220	0.1	<5th	22	220	0.1	<5th		Blah22	Feb	22	220	0.1	<5th
27			Blah23	23	230	0.1	<5th	23	230	0.1	<5th	23	230	0.1	<5th		Blah23	Feb	23	230	0.1	<5th
28																	Blah1	March	1	1	1	95th
29																	Blah2	March	0	2	0	<5th
30																	Blah3	March	0	1	0	<5th
31																	Blah4	March	0	1	0	<5th
32																	Blah5	March	1	400	0.0025	<5th
33																	Blah6	March	0	1000	0	<5th
34																	Blah7	March	7	700	0.01	<5th
35																	Blah8	March	8	80	0.1	<5th
36																	Blah9	March	9	90	0.1	<5th
37																	Blah10	March	10	100	0.1	<5th
38																	Blah11	March	11	110	0.1	<5th
39																	Blah12	March	12	120	0.1	<5th
40																	Blah13	March	13	130	0.1	<5th
41																	Blah14	March	14	140	0.1	<5th
42																	Blah15	March	15	150	0.1	<5th
43																	Blah16	March	16	160	0.1	<5th
44																	Blah17	March	17	170	0.1	<5th
45																	Blah18	March	18	180	0.1	<5th
46																	Blah19	March	19	190	0.1	<5th
47																	Blah20	March	20	200	0.1	<5th
48																	Blah21	March	21	210	0.1	<5th
49																	Blah22	March	22	220	0.1	<5th
50																	Blah23	March	23	230	0.1	<5th
51																	Blah1	April	1	1	1	95th
52																	Blah2	April	0	2	0	<5th
53																	Blah3	April	0	1	0	<5th
54																	Blah4	April	0	1	0	<5th
55																	Blah5	April	1	400	0.0025	<5th
56																	Blah6	April	0	1000	0	<5th
57																	Blah7	April	7	700	0.01	<5th
58																	Blah8	April	8	80	0.1	<5th
59																	Blah9	April	9	90	0.1	<5th
60																	Blah10	April	10	100	0.1	<5th
61																	Blah11	April	11	110	0.1	<5th
62																	Blah12	April	12	120	0.1	<5th
63																	Blah13	April	13	130	0.1	<5th
64																	Blah14	April	14	140	0.1	<5th
65																	Blah15	April	15	150	0.1	<5th
66																	Blah16	April	16	160	0.1	<5th
67																	Blah17	April	17	170	0.1	<5th
68																	Blah18	April	18	180	0.1	<5th
69																	Blah19	April	19	190	0.1	<5th
70																	Blah20	April	20	200	0.1	<5th
71																	Blah21	April	21	210	0.1	<5th
72																	Blah22	April	22	220	0.1	<5th
73																	Blah23	April	23	230	0.1	<5th
greenbehindthecel

Cell Formulas
Range	Formula
S5:V73	S5	=INDEX($D$5:$O$27,MATCH($Q5,$C$5:$C$27,0),MATCH(S$4&" "&$R5,$D$4:$O$4,0))

 

[Greenbehindthecells]

What is the reasoning behind having a multiple columns for each month of data. It is nearly impossible for you to achieve "dynamics" with how you have your data as you are forcing yourself to "realign" the source data for your chart(s).

Is there a compelling reason why you need to structure your data this way? You have 2 criteria (column and month) and 4 measurement (num, den, rate, perc) those are the only columns you need. And your month should actually be a date (you can format to display only month) - which will allow you to slice your data by month.

I have unpivoted your crosstab into a flat file, with this you can build your charts as you desire:

Mr excel questions 39.xlsm
			C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
4			Column1	Num Feb	Den Feb	Rate Feb	Perc Feb	Num March	Den March	Rate March	Perc March	Num April	Den April	Rate April	Perc April		Column1	Month	Num	Den	Rate	Perc
5			Blah1	1	1	1	95th	1	1	1	95th	1	1	1	95th		Blah1	Feb	1	1	1	95th
6			Blah2	0	2	0	<5th	0	2	0	<5th	0	2	0	<5th		Blah2	Feb	0	2	0	<5th
7			Blah3	0	1	0	<5th	0	1	0	<5th	0	1	0	<5th		Blah3	Feb	0	1	0	<5th
8			Blah4	0	1	0	<5th	0	1	0	<5th	0	1	0	<5th		Blah4	Feb	0	1	0	<5th
9			Blah5	1	400	0.0025	<5th	1	400	0.0025	<5th	1	400	0.0025	<5th		Blah5	Feb	1	400	0.0025	<5th
10			Blah6	0	1000	0	<5th	0	1000	0	<5th	0	1000	0	<5th		Blah6	Feb	0	1000	0	<5th
11			Blah7	7	700	0.01	<5th	7	700	0.01	<5th	7	700	0.01	<5th		Blah7	Feb	7	700	0.01	<5th
12			Blah8	8	80	0.1	<5th	8	80	0.1	<5th	8	80	0.1	<5th		Blah8	Feb	8	80	0.1	<5th
13			Blah9	9	90	0.1	<5th	9	90	0.1	<5th	9	90	0.1	<5th		Blah9	Feb	9	90	0.1	<5th
14			Blah10	10	100	0.1	<5th	10	100	0.1	<5th	10	100	0.1	<5th		Blah10	Feb	10	100	0.1	<5th
15			Blah11	11	110	0.1	<5th	11	110	0.1	<5th	11	110	0.1	<5th		Blah11	Feb	11	110	0.1	<5th
16			Blah12	12	120	0.1	<5th	12	120	0.1	<5th	12	120	0.1	<5th		Blah12	Feb	12	120	0.1	<5th
17			Blah13	13	130	0.1	<5th	13	130	0.1	<5th	13	130	0.1	<5th		Blah13	Feb	13	130	0.1	<5th
18			Blah14	14	140	0.1	<5th	14	140	0.1	<5th	14	140	0.1	<5th		Blah14	Feb	14	140	0.1	<5th
19			Blah15	15	150	0.1	<5th	15	150	0.1	<5th	15	150	0.1	<5th		Blah15	Feb	15	150	0.1	<5th
20			Blah16	16	160	0.1	<5th	16	160	0.1	<5th	16	160	0.1	<5th		Blah16	Feb	16	160	0.1	<5th
21			Blah17	17	170	0.1	<5th	17	170	0.1	<5th	17	170	0.1	<5th		Blah17	Feb	17	170	0.1	<5th
22			Blah18	18	180	0.1	<5th	18	180	0.1	<5th	18	180	0.1	<5th		Blah18	Feb	18	180	0.1	<5th
23			Blah19	19	190	0.1	<5th	19	190	0.1	<5th	19	190	0.1	<5th		Blah19	Feb	19	190	0.1	<5th
24			Blah20	20	200	0.1	<5th	20	200	0.1	<5th	20	200	0.1	<5th		Blah20	Feb	20	200	0.1	<5th
25			Blah21	21	210	0.1	<5th	21	210	0.1	<5th	21	210	0.1	<5th		Blah21	Feb	21	210	0.1	<5th
26			Blah22	22	220	0.1	<5th	22	220	0.1	<5th	22	220	0.1	<5th		Blah22	Feb	22	220	0.1	<5th
27			Blah23	23	230	0.1	<5th	23	230	0.1	<5th	23	230	0.1	<5th		Blah23	Feb	23	230	0.1	<5th
28																	Blah1	March	1	1	1	95th
29																	Blah2	March	0	2	0	<5th
30																	Blah3	March	0	1	0	<5th
31																	Blah4	March	0	1	0	<5th
32																	Blah5	March	1	400	0.0025	<5th
33																	Blah6	March	0	1000	0	<5th
34																	Blah7	March	7	700	0.01	<5th
35																	Blah8	March	8	80	0.1	<5th
36																	Blah9	March	9	90	0.1	<5th
37																	Blah10	March	10	100	0.1	<5th
38																	Blah11	March	11	110	0.1	<5th
39																	Blah12	March	12	120	0.1	<5th
40																	Blah13	March	13	130	0.1	<5th
41																	Blah14	March	14	140	0.1	<5th
42																	Blah15	March	15	150	0.1	<5th
43																	Blah16	March	16	160	0.1	<5th
44																	Blah17	March	17	170	0.1	<5th
45																	Blah18	March	18	180	0.1	<5th
46																	Blah19	March	19	190	0.1	<5th
47																	Blah20	March	20	200	0.1	<5th
48																	Blah21	March	21	210	0.1	<5th
49																	Blah22	March	22	220	0.1	<5th
50																	Blah23	March	23	230	0.1	<5th
51																	Blah1	April	1	1	1	95th
52																	Blah2	April	0	2	0	<5th
53																	Blah3	April	0	1	0	<5th
54																	Blah4	April	0	1	0	<5th
55																	Blah5	April	1	400	0.0025	<5th
56																	Blah6	April	0	1000	0	<5th
57																	Blah7	April	7	700	0.01	<5th
58																	Blah8	April	8	80	0.1	<5th
59																	Blah9	April	9	90	0.1	<5th
60																	Blah10	April	10	100	0.1	<5th
61																	Blah11	April	11	110	0.1	<5th
62																	Blah12	April	12	120	0.1	<5th
63																	Blah13	April	13	130	0.1	<5th
64																	Blah14	April	14	140	0.1	<5th
65																	Blah15	April	15	150	0.1	<5th
66																	Blah16	April	16	160	0.1	<5th
67																	Blah17	April	17	170	0.1	<5th
68																	Blah18	April	18	180	0.1	<5th
69																	Blah19	April	19	190	0.1	<5th
70																	Blah20	April	20	200	0.1	<5th
71																	Blah21	April	21	210	0.1	<5th
72																	Blah22	April	22	220	0.1	<5th
73																	Blah23	April	23	230	0.1	<5th
greenbehindthecel

Cell Formulas
Range	Formula
S5:V73	S5	=INDEX($D$5:$O$27,MATCH($Q5,$C$5:$C$27,0),MATCH(S$4&" "&$R5,$D$4:$O$4,0))

I would love to answer your question with a great explanation, except its not so great. I am very new to Pivot charts (charts in general) in excel and I calculated the data in a way that is readable at first glance for what we are watching in my department (not really the best for pivot tables and charts, I am realizing). I knew there was a problem with the date, but I couldn't see my error. Thank you for reviewing my question and my sheets. I hope you have a wonderful evening.

 

[awoohaw]

You're welcome.
One lesson that I often need to be reminded of myself is that DATA and PRESENTATION are usually managed best separately. So, if you haven't gone too far down using a blended approach, I suggest you try to do that. You will find two things: 1. Managing Data is Easier and 2. Your presentations are not only easier but can be more impactful to the viewer.

Here is a great video of things not to do in excel. I think knowing the don'ts is often better than learning neat tricks because once people start relying on bad practices it is hard to UNLEARN them.

 

[Greenbehindthecells]

You're welcome.
One lesson that I often need to be reminded of myself is that DATA and PRESENTATION are usually managed best separately. So, if you haven't gone too far down using a blended approach, I suggest you try to do that. You will find two things: 1. Managing Data is Easier and 2. Your presentations are not only easier but can be more impactful to the viewer.

Thank you so much. This is definitely a lesson in that!

 

[awoohaw]

Thank you so much. This is definitely a lesson in that!

i just edited my last statement with a link to a great video resource.
Mr. Excel, ExcelIsFun, and MyOnlineLearningHub are great youtube channels that subscribing to may be helpful.

 

[Greenbehindthecells]

i just edited my last statement with a link to a great video resource.
Mr. Excel, ExcelIsFun, and MyOnlineLearningHub are great youtube channels that subscribing to may be helpful.

Thank you again!

 

[awoohaw]

my pleasure. I hope that you can find a way to create a basic yet impactful visualization.

Best wishes!