Results 1 to 7 of 7

Easy way to split out sheets from a pivot table?

This is a discussion on Easy way to split out sheets from a pivot table? within the Excel Questions forums, part of the Question Forums category; Hi, I am working on some sales data which I need grouped by Sales Rep Name. I have a pivot ...

  1. #1
    Board Regular
    Join Date
    Jan 2003
    Posts
    55

    Default Easy way to split out sheets from a pivot table?

    Hi, I am working on some sales data which I need grouped by Sales Rep Name. I have a pivot table with it grouped by Name with the data being number of accounts, but now I want to take each reps data and split it into their own worksheet eg: pivot table contains Smith- 4 , Thompson- 10, Ward-20 etc. and I want to make a sheet for each of these people with their respective account responsibilities.

    I know you can double click on the data totals number in the pivot table to break it into a new sheet but I have 40 reps, is there an easy way to split all of these into different sheets without clicking on each total 40 times and then also change the name of the tab to reflect the rep name from the pivot table?
    thanks!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    See the Show Pages... command in the PivotTable toolbar menu to create separate worksheets for each Page field item.

  3. #3
    Board Regular
    Join Date
    Jan 2003
    Posts
    55

    Default

    No matter where I click in the pivot table, the Show Pages button on the toolbar is grayed out however the pivot table wizard and field buttons both work, any ideas?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    Does your PivotTable have a Page field, such as 'Sales Rep Name'?

  5. #5
    Board Regular
    Join Date
    Jan 2003
    Posts
    55

    Default

    oh! no, i just had rep down the left as a row. i've never used the page function. BTW just saw another post on the ASAP utilities and they are incredibly helpful, i love this board!

  6. #6
    Board Regular
    Join Date
    Jan 2003
    Posts
    55

    Default

    I moved the rep name to the page area and did show pages of all however this creates a page for each rep (which i want) but with the pivot table in each page (which i dont want).

    I just want the actual data to appear (eg if you were in the pivot and double clicked on the number of accounts, it would show all of the data that the pivot table is based on). hope that makes sense..

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    Once you get a separate PivotTable for each Sales Rep then you can use drilldown to isolate the pertinent info for each. Another approach would be to use Advanced AutoFilters on the original data list to channel select data to separate worksheets.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com