98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot Table
Pivot Table
Resolved · High Priority · Version 2010
Thu 6 Jun 2013, 12:29Delegate Laura said...
Laura has attended:
Multiple applications course
Pivot Table
I have created a pivot table to help me with some bedroom allocations. The total should be split out between two different statuses as the spreadsheet is. This is either 'Confirmed' or 'Estimated'. It is then split over the days. When I look at the pivot table, it gives me x2 entries per venue obviously for confirmed and estimated and gives me the correct overall total but does not split out confirmed and estiamed rooms accurately. Do you know why this is happening?
Thank you
Thu 6 Jun 2013, 15:44Trainer Simon said...
RE: Pivot Table
Hi Laura,
Thank you for your question and welcome to the forum.
Is the data you are using in columns and all the records in rows? Have you tried rearranging the fields between row and columns or have the status as a page field?
Regards
Simon
Thu 6 Jun 2013, 16:05Delegate Laura said...
RE: Pivot Table
Thanks Simon - It's not really something I can manipulate. if you can imagine all the rows with names etc and then we have a venue coloumn, a C/E (confirmed or estimated) column and then 9 colums for all the nights. what we are trying to show on each night with each venue is the split between what is confirmed and what is estimated. All the room night columns simply have a '1' in them to denote the nights. The pivot table is telling us the total nights combining confirmed and estimated but it is struggling (!) to tell us accurately the split. This is also happeneing when we use the SUMIFS function. It sees our ranges and conditions but does not return the right number?
Any further advice would be great.
Mon 10 Jun 2013, 16:52Trainer Andrew said...
RE: Pivot Table
Hi Laura
Thanks for coming back to us. I think it might be helpful if we can see the sheet or a representative sample of the data.
You can email to forum@stl-training.co.uk
Let me know if that is something you can do. We will take a look at the table and will be in a better position to offer advice or perhaps recommend other options.
Kind regards,
Andrew
Wed 12 Jun 2013, 12:24Trainer Andrew said...
RE: Pivot Table
Hi Laura
Thank you for sending in the file, very helpful.
As a starting point try moving the "estimated or confirmed" field from the Report filter box to the Row box, directly underneath the the "Already allocated" field.
This will split each location's allocation into estimated and confirmed amounts.
Clicking on the Design tab in the Pivot Tables group lets you use the Subtotals button if you would like to show Subtotals at Bottom of Group which should give you a total for each venue for each day.
Give this a try and let me know if this is closer to the output you are seeking or further away.
Kind regards,
Andrew
Wed 12 Jun 2013, 15:23Delegate Laura said...
RE: Pivot Table
Thank you Andrew - this worked. So easy when you know how! Interestingly, the reason we worked out why we were still getting a double count for each venue was because some entries of either 'confirmed' or 'estimated' had a space at the end!
Many thanks
Laura
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Sorting List SubtotalsIf you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows. |
