• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Split pivot table?|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Split pivot table?|Dashboards & Charts|Excel Forum|My Online Training Hub

sale now on

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsSplit pivot table?
sp_PrintTopic sp_TopicIcon
Split pivot table?
Avatar
barebulb

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 24, 2016
sp_UserOfflineSmall Offline
1
September 21, 2017 - 12:23 am
sp_Permalink sp_Print

Hi all,

I have a data set with both rows and columns that ideally I would both group (they're not dates, unfortunately) and add a slicer to, but as I can't do that with slicers as far as I know, I'd like to only slicer the rows and have a different pivot chart for each column group.  However, I can't seem to make one slicer control all the charts I need, even though the rows are identical.  The separate charts are not appearing on the list of possible slicer connections - I suspect they have different pivot caches.  I am using the same data set to make the charts - copying it and then modifying the copies - but apparently that is not enough.  I think the only solution is to make charts from different pieces of 1 pivot table.  Is that even possible?

I am certainly open to other solutions... I have been banging my head against this for days so a fresh perspective is welcome.  Thank you!

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4371
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 21, 2017 - 3:26 pm
sp_Permalink sp_Print

Hi Holly,

It's a bit difficult to picture from your description. In particular this sentence isn't clear "I have a data set with both rows and columns that ideally I would both group". I can't imagine what you mean by grouping rows and columns...do you mean group both rows and columns together, or group rows, and separately group columns? A screenshot or sample file would be helpful, but let me try.

In the Slicer connections list you will see a list of PivotTables, not Pivot Charts. Each Pivot Chart has it's own PivotTable, so if they all share the same cache then you'd see them in the list of Slicer connections.

If you want to group items and have a Slicer for those groups then you need to add a column to your source data for this grouping. e.g. let's say you have a list of produce in a column called 'Produce'. This produce column contains the following items: apples, pears, oranges, potatoes, corn and carrots. You want to group these items into departments; fruit and vegetables and insert a slicer for the departments. So, you add another column to your source data for the 'Department' and the items in that department will be Fruit and Vegetables.

I hope that points you in the right direction. If you're still stuck, please upload a sample Excel file so we can give you a specific solution.

Mynda

Avatar
barebulb

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 24, 2016
sp_UserOfflineSmall Offline
3
September 22, 2017 - 5:44 am
sp_Permalink sp_Print

Hi Mynda,

I'm halfway there, thank you!  Sorry I wasn't clear - I did mean to group rows, and separately group columns.  I've attached a sample of my file, but basically this is info for a group of nonprofit health centers, and we surveyed the people who go.  The questions in rows I was able to group as you suggested using an added column I could use a slicer for - questions around service, staff engagement, etc.  The columns I'd also like to group - they are things like Male, Female, Once a Week, Twice a Week, etc. - I'd like them to be categories like Gender, Frequency of Use, etc.  My solution was to make an entire pivot chart for just Gender, and a separate one for Frequency and so forth, changing the groups of questions for every chart at once using a single slicer, since the questions would be the same for each.  That's where I ran into the problem with the pivot cache.

Since the table is half sorted into groups, maybe I will copy it many times to get 1 cache, then filter a table for Gender, the next Frequency etc. and build charts from each of those.  I don't know if that is the best solution, but it will be my next plan of attack!

By the way, I first learned the joys of slicers from your webinar on building dashboards, so you have helped me twice!  I am trying to get my stingy company to get me the full course - I think I'm wearing them down.  But thank you!!

Holly

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4371
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
September 22, 2017 - 11:34 am
sp_Permalink sp_Print

Hi Holly,

Thanks for sharing the data. It looks like the data is already partially summarised and pivoted and this is the cause of your problems. Nothing to do with caches.

If you can get the original data you can then use the PivotTable to summarise it. For example, I'd expect to see columns for:

  • Category
  • Characteristic
  • Age Group
  • Gender
  • Frequency
  • Seeker Type
  • Membership type (? Family, Senior, Passive)
  • Visit Time
  • Score

This will allow you to insert Slicers for the Gender, Frequency etc.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
barebulb

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 24, 2016
sp_UserOfflineSmall Offline
5
September 23, 2017 - 1:26 am
sp_Permalink sp_Print

Thanks Mynda,

I have a message in to our survey company who originally gave us this information.  They use Alteryx rather than Excel, so there is no telling what the data will look like if I can view it, but I will figure it out if they can send it! 

The 'Activity Category' column on the second tab was something I added based on your suggestion, which has already helped a lot.  Thanks again!

Holly

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Alan Sidman
Guest(s) 80
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 858
Velouria: 580
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 214
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 189
Newest Members:
Vincent Starcevich
Patrick Lawrence
Louise Bell
GIJO GEORGE
Kumud Patel
Atos Franzon
Andrew MacDonald
Artie Ball
Jenn Cain
Peter Vandeweg
Forum Stats:
Groups: 3
Forums: 24
Topics: 6079
Posts: 26684

 

Member Stats:
Guest Posters: 49
Members: 31570
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.