Pivot table help!
Discussion
I have a data set which shows transactions in £ for every payment type in our stores.
I want to have a separate tab for each payment type (so 'Type' will be my filter) so I can drill down further into these.
I know I could just filter by Type in the main pivot and then copy and paste the data into a new tab, but I thought there was a way you could set it up to do it automatically?
So that Excel would Pivot the data for you based on a specific parameter and create new tabs, one for each pivot?
Am I making sense? No?
I want to have a separate tab for each payment type (so 'Type' will be my filter) so I can drill down further into these.
I know I could just filter by Type in the main pivot and then copy and paste the data into a new tab, but I thought there was a way you could set it up to do it automatically?
So that Excel would Pivot the data for you based on a specific parameter and create new tabs, one for each pivot?
Am I making sense? No?
Why not just set up a pivot for a selected payment type showing transactions down the side and £ as data.
Then copy the sheet and select a different payment type.
Set this pivot table to be based on the first one (via the wizard) to save file size and repeat for as many filetypes as you have.
Then copy the sheet and select a different payment type.
Set this pivot table to be based on the first one (via the wizard) to save file size and repeat for as many filetypes as you have.
Chilli said:
Makes perfect sense. Have the main data in sheet 1, followed by a sheet for every product. Then a table on each which covers the whole spreadsheet, and pivot on the specific product.
Each day/month/year simply cut and paste the new data into sheet 1, and refresh all pivots.
That sounds the best method.Each day/month/year simply cut and paste the new data into sheet 1, and refresh all pivots.
Cheers Chilli!
Hmm.
Not quite working how I expected. Here is what I did:
1. Put full data in Tab 1
2. Create PIVOT on data 10 times, creating 10 new tabs
3. Filter 10 tabs by payment type
This works fine.
Replace all data in Tab 1 with new month data - i.e. copy and paste new month data over old data.
Go into one of the Pivot tables and click refresh....
....but this just cleared the Pivot table rather than updating it with the new data.
Is this because there might be a different number of rows in the new data set ro something? What might be confusing it?
Not quite working how I expected. Here is what I did:
1. Put full data in Tab 1
2. Create PIVOT on data 10 times, creating 10 new tabs
3. Filter 10 tabs by payment type
This works fine.
Replace all data in Tab 1 with new month data - i.e. copy and paste new month data over old data.
Go into one of the Pivot tables and click refresh....
....but this just cleared the Pivot table rather than updating it with the new data.
Is this because there might be a different number of rows in the new data set ro something? What might be confusing it?
Legend83 said:
Hmm.
Not quite working how I expected. Here is what I did:
1. Put full data in Tab 1
2. Create PIVOT on data 10 times, creating 10 new tabs
3. Filter 10 tabs by payment type
This works fine.
Replace all data in Tab 1 with new month data - i.e. copy and paste new month data over old data.
Go into one of the Pivot tables and click refresh....
....but this just cleared the Pivot table rather than updating it with the new data.
Is this because there might be a different number of rows in the new data set ro something? What might be confusing it?
If you add rows or columns it won't include them. (The original source data is hemmed in by $$ in the formula remember...)Not quite working how I expected. Here is what I did:
1. Put full data in Tab 1
2. Create PIVOT on data 10 times, creating 10 new tabs
3. Filter 10 tabs by payment type
This works fine.
Replace all data in Tab 1 with new month data - i.e. copy and paste new month data over old data.
Go into one of the Pivot tables and click refresh....
....but this just cleared the Pivot table rather than updating it with the new data.
Is this because there might be a different number of rows in the new data set ro something? What might be confusing it?
If you change the column headers - that will screw it all up.
Try adjusting a couple of entries and you will see what it can cope with.
Gassing Station | The Lounge | Top of Page | What's New | My Stuff