Multi-sheet Pivot Table

Audie17

New Member
Joined
Apr 25, 2018
Messages
4
Hello,

I'm new at this and trying my hardest to create a pivot table that reads multiple worksheets in my file - but I keep hitting brick walls! I'm wondering if anyone is able to help me, pretty please?

My file is for someone who has customers ordering beauty products from her. Each worksheet has columns titled 'No. ' (for the number of times the product was ordered), 'Category' (eg. Face, body, etc), 'Description' (eg. Moisturiser, etc) and 'Price'.

I'd like to know how to create a table that shows, from all customers, the total number of times the product (Description) was ordered, sorted per Category. This is so she can see straight away what's the most popular type of products are being ordered (Category) and within that category the popular products ordered (Description). Would love to show $'s against the categories too, if that's possible.

Hope I haven't confused anyone as much as I am at the moment! Thanks in advance for your amazing help.

:biggrin:
Audie17
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
If the sheets are the same, you can Consolidate the data.
See: http://www.contextures.com/xlPivot08.html

Basically, you need to use the original Pivot Table wizard which can only be accessed with the shortcut ALT+D. It has an option to consolidate ranges from multiple sheets in a workbook.
 
Upvote 0
Thank you for your help and fantastic link. I’vebeen able to set up my consolidated pivot table, giving each worksheet fieldnames, etc, but unsure how to do the last bit to display the result I want.It's the Pivot Table Fields area on the right-hand side that's confusing me.It's not the same as if I was doing this for a single worksheet, where thecolumn names appear. My columns are as follows:

No. Category Description Price

I would like to see how many products(Description) were ordered per Category, which would be obtained from the No.column - the Price is relevant for indicating total expenditure. Maybe this isn’t possible?

:rolleyes:
Audie17
 
Upvote 0
If I understand you correctly, you'll need to change your tables so the Category column is the leftmost column on all the sheets, then recreate your pivot table.
 
Upvote 0
Thank you! This is not what the many internet sites I've visited has told me while trying to figure this problem out. Lesson learned and appreciated.

:biggrin:
Audra
 
Upvote 0
So I did understand you correctly? :) I wasn't too sure.
 
Upvote 0
You understood perfectly. My pivot table is now functional and makes sense, showing Description (or products) that were ordered per Category and how many of each. Thanks again.

:biggrin:
Audie17
 
Upvote 0
Glad to help!
I just wish Consolidated tables option would just merge the data together into a single table, instead of that summary like setup.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top