Is there an easy way to break pivot table rows or pages down into non-linked workbooks
August 20, 2008 11:16 AM Subscribe
I have an Excel pivot table for sales performance data. I want to break that down for regional managers so that they only have access to their own offices' data while preserving the nice formatting that the pivot table provides. Is there an easy way to do that?
My pivot table structure is as follows:
Rows: Divisional Manager, Regional Manger, Office Name, Sales Person Name
Data (sums of): New Clients, Product 1, Product 2, Product 3
It's easy enough to check only one of the regional managers to show their data only and hide the raw data sheet, but any sophisticated manager could easily find other regions' data in that method, and pivot tables tend to be huge, so I don't want to e-mail them.
What I've been doing is check an individual regional manager only, copy, in a new workbook, paste special values (to break the association to the pivot table), paste special formats (to pretty it up), save as regional manager's name, go to 10. It works, but there are a ton of managers, and it seems like a huge waste of time -- the kind of thing computers were built to automate.
I'm sure there's some trixsy way to automate it using VB, but I don't know VB.
I found a way to explode the regional managers out into their own worksheets if they're in pages instead of rows, but that preserves the association with the pivot table, and they're all in the same workbook. There has to be an easier solution that I don't know about.
My pivot table structure is as follows:
Rows: Divisional Manager, Regional Manger, Office Name, Sales Person Name
Data (sums of): New Clients, Product 1, Product 2, Product 3
It's easy enough to check only one of the regional managers to show their data only and hide the raw data sheet, but any sophisticated manager could easily find other regions' data in that method, and pivot tables tend to be huge, so I don't want to e-mail them.
What I've been doing is check an individual regional manager only, copy, in a new workbook, paste special values (to break the association to the pivot table), paste special formats (to pretty it up), save as regional manager's name, go to 10. It works, but there are a ton of managers, and it seems like a huge waste of time -- the kind of thing computers were built to automate.
I'm sure there's some trixsy way to automate it using VB, but I don't know VB.
I found a way to explode the regional managers out into their own worksheets if they're in pages instead of rows, but that preserves the association with the pivot table, and they're all in the same workbook. There has to be an easier solution that I don't know about.
Response by poster: Saving to CSV loses any formatting. There are a lot easier to read with that formatting in place, so I'd like to preserve it if I can. Your solution is faster though, and I guess the data is there.
posted by willnot at 11:41 AM on August 20, 2008
posted by willnot at 11:41 AM on August 20, 2008
The same logic would work if you exported to something like a PDF or even screen-capped it. I like the idea of being able to highlight and re-format the numbers (hence my idea to keep it in a spreadsheet).
I am not as familiar with various types of "password locking" in Excel so I can't be of much service there. It is possible that you would be able to "Hide" the entire raw data table, select the appropriate manager, then password lock the file so that nothing can be reconfigured. I don't know all of the ins and outs with that process so I am not sure what is and what is not possible on that front. It would probably be faster than what you are doing, but it would still require a number of manual steps.
Another alternative would be to set up the pivottable as you are currently doing, then go back to the raw data table and sort by manager. Then you can just delete all of the other managers, go to the pivottable, and refresh it, then save the file. Then try to undo a couple of times, delete different managers, refresh the table and save again. This way, you are only ever pivoting one manager at a time.
Does that sound better?
posted by milqman at 12:13 PM on August 20, 2008
I am not as familiar with various types of "password locking" in Excel so I can't be of much service there. It is possible that you would be able to "Hide" the entire raw data table, select the appropriate manager, then password lock the file so that nothing can be reconfigured. I don't know all of the ins and outs with that process so I am not sure what is and what is not possible on that front. It would probably be faster than what you are doing, but it would still require a number of manual steps.
Another alternative would be to set up the pivottable as you are currently doing, then go back to the raw data table and sort by manager. Then you can just delete all of the other managers, go to the pivottable, and refresh it, then save the file. Then try to undo a couple of times, delete different managers, refresh the table and save again. This way, you are only ever pivoting one manager at a time.
Does that sound better?
posted by milqman at 12:13 PM on August 20, 2008
This thread is closed to new comments.
I just tried "Saving As" to a .csv with one of my pivot tables, and it worked just fine. If you want to do more elaborate formatting after that, try poking around with macros (they have a super nifty record feature). The CSV communicates everything that your current method does, but it does it faster. You could probably make a macro that would do all of this at the same time, but that might be out of your scope.
So yeah, try saving to a CSV after clicking whatever managers you want to display.
posted by milqman at 11:30 AM on August 20, 2008