Pivot table - showing text value and multiple charts

Question

I hope that it is clear what I am trying to achieve here. You'll have to forgive me, I'm a complete and utter novice who navigates excel usually using just the standard buttons on the ribbon! I want to create a pivot table to show the varying different communications and events that happen within a given month within my organisation.

Desired Pivot

Ability to filter by month(s) with a general count of event/communication types. Then the ability to show more information for selected types (e.g. the date of).

Current data

Columns: Date, Month, Name, Campaign, Type, Service/s, Send to and Sent from

NB. Campaign, services, send to and sent from will only have a value if the data is about a mailing rather than an Awareness Day or Charity event.

Pivot Attempt

My current Pivot is OK but not "quite there". I can filter with months and it'll show me all the happenings within that month, which is great but I want a little more, if possible.

1) For the date, sent from and send to information to show when I filter too (e.g. would know the Mental Wellbeing EDM would be sent on 23rd May to Active and Inactive volunteers from VET)

2) On a separate table (using same filter) to show a simple count of each type e.g. Filter to June, then show EDM - 4, Awareness Days - 5, Internal Comms - 3 etc.

3) The "service/s" column could have multiple options (e.g. caring and money, volunteering and fundraising) is there a way to count this. So I could say e.g. in May, there are 4 events which fall under Caring (this would count an EDM just noted as 'caring' in service column but also 3 awareness days which were put under services as 'mental wellbeing, caring and money' for example. (I hope that's clear??)

Any thoughts, really appreciated!

matt mickle · Answer

Calleigh-Marie-
&nbsp;
Maybe try something like the attached file.&nbsp; You need to put the Pivot Table in Tabular Format (This is on the PivotTable Tools Tab &gt; Layout &gt; Report Layout (far left).&nbsp; Another option is to double click the counts.&nbsp; This will drill to the detail behind the summary figure.
&nbsp;

calleigh-marie lawrence · Answer

Hi Matt,&nbsp;&nbsp;That's really useful and actually really helped with what I was doing :) Thank you, I appreciate it. Could I ask for your expertise on another stumbling block?&nbsp;&nbsp;I'd like to use the splicer to separate out topics. Issue is, many of the mailings/events above&nbsp;cover multiple&nbsp;topics c.&nbsp;e.g. Walking Challenge EDM could come under 'wellbeing' and 'volunteering'. As below:&nbsp;&nbsp;What I'd like to be able to do is have a splicer with a list of all the individual topics and be able to select multiple from that to show for example, all mailings&nbsp;including&nbsp;wellbeing as a topic. So the aforementioned example, would come up in a splice for both wellbeing and volunteering. Does that make sense? I'd like my splicer to look like this:&nbsp;&nbsp;Currently under topics I'm having to type "Fundraising &amp; Volunteering", "Fundraising &amp; Other" etc. which isn't ideal when you see the Splicer.&nbsp;Thanks in advance!!!!&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

matt mickle · Answer

Hey, happy that the previous file I provided was able to help you out a bit.&nbsp; I understand what you would like to do based on your description, however I don't think what you want is possible without modifying the data structure.&nbsp; I'm attaching the updated file for your reference (See attached).&nbsp; I believe you could do a few different things:
&nbsp;
1. Before Worksheet: Use the Ctrl Key to filter on multiple selections within the slicer
2. After Worksheet: Modify the data to contain 1 row for each category....&nbsp; i.e. a Fundraising and Volunteering record would become two records one for Fundraising and one for Volunteering.&nbsp; For a clear example please refer to the example file.
&nbsp;
Hope this helps.

Forum Discussion

Pivot table - showing text value and multiple charts