Pivot Table - Actual vs Budget Analysis - Part 2

welcome back to buy a pivot table tutorial part 2 in this part we are going to look a bit more at how to share details of a given tour tall and also one or two other things as well but first off if I went to will right click on any value could be a value could be labeled it doesn't really matter as long as it's within the pivot table area if I right click on it I've got the pivot table options the same thing of course within the people tools we can also web access in here if I click on that you get exactly the same thing as what we would expect for the two trolls if I did not want to see these tools here which in some cases may not be necessary our month may not be relevant or useful we can untick that dis box here for grand totals to remove that for the rows so if I do that now and then okay let's total for the rows are gotten but of course the totals for the columns they remain a further thing we can do is well is if I where to them know the details that make up this amount of 12 land or one I can simply double click on these and that would generate a new work shall create a new worksheet and then provide me with the details that make up that twelve 9:01 so just double click on that and we can see here for the budget 901 12 901 there and of course the actual would are double twelve thirteen five one line so if I go back to the people table report couldn't see thirteen five one nine and twelve nine or one we do we can do the same thing a different way as well by right-clicking and then sure details and that does is I think the same thing of course it would create a new workbook each time we sorry and your worksheet is time we try to show details so I would go ahead and delete these worksheets as we delete them yes okay at the beginning we saw that the region was on column a and salesperson and column B we can present that view by again going into the pivot table options going into display and then the classic pivot table layout I would in it I will enable that this tells you that you can now drag the fuels in degreed see if I do ok we can now see that the region is in column a and salesperson is in column P down for the show details we saw earlier if you there would be instances you don't wish people or your users to be able to drill down to be able to see details we can actually a stop that quite easily by going back into the pivot table options then going into data and then on ticking the illiberal show details and okay now if I tried to wim sure details on here who would be we would give me a warning that that facilities been turned off another thing we can do on here as the moment for the central region the total here this two cell arose are probably too close together we could insert a row between them and we do that by placing a cursor within the field to interest it in right click on it good to fill settings for that field goodly layout and print and then we can insert a blank line of we can do the same thing when we also printing the report here as well if I do ok we now have a blank line between those two we can see here that when I did the few settings suddenly when I click on the fill settings for the region we are not getting the same thing as what we had for the values simply because this is a different context these are levels so we're not going to be getting anything like number for months and things like that another thing we can do is well is let's say for example these two cells people will probably like to group them together I could highlight puts of them the right click on that and then do a group and when I group those two now let's say I want down to do a total for this two if I right click on here and then do subtotal for salesperson two which is the new field that has been put in for me we can see here that for those two people this row here it's added both of these numbers together treating them as one and what we can do now since we don't really need that but that could be useful in some instances if I right click on here I can ungroup for example or puddly I need to actually go into the salesperson because that's where we grouped from right-click on it and do ungroup there will a right click on ungroup I'd click on group Justin right okay I would I would down let's remove the salesperson to because we don't really need it if I read click in that cell to remove salt person to we are not back to where we were previously other options very quickly um printing we've looked at so we can set print titles and things like that their job we've looked at how to we're disabled show details and we of course we can save source data as well and this one is useful to refresh data when open the file especially when you are dealing with dynamic data for layout mesh and Center cells with their balls this basically would have sent to justify the palace so that central would come into the middle if you tried that we can see this or bug into the middle I would undo it by going to Mikey but will control Z to undo that and most of this stuff with the people table is essentially using the right-click for instance with this we could summarize data which other where we want and so there is an awful lot here that we can do with just a right-click a further thing as well because it's the same data set instead of doing actual 's and budgets we could just a report and just actual Zoar report and just budgets so let's say we just want to report for actuals for a given year I could remove the budgets by just dragging it away from the little box of the valid box and because we took them to budget out we don't need the actual difference or the achievement I could Douglas artists were by dragging them away or right-clicking them here and remove which probably squeaker right-click and remove that so now we need to just select year 2000 of line I could actually move these to the filter area before I do that actually this here is going to move to here and I want to move the month as the column to become the cop column levels because I'm using drag and drop I can easily move that to there and then move the month that is my cursor you can see it's change to crosshair so I can not move that unless I move it you can see the little blue highlight tries telling woman that it's in the valleys area which you can see from this highlighter here if I move it to the left it would tell me is going to go to a rule as a rule level if I move it across here the little blue thing isn't changed again to just between the data area and the total so that's where I wanted I'll just drop it in there now with these we've got actuals which we can see here it would tell you for year 2009 of course if you wanted that for 2010 only we would get that for year 2010 so that in summary is a simple quick way of going about them creating a pivot table report thanks for watching bye bye

Channel: akparawaNET

Views: 22,932

Rating: 4.9032259 out of 5

Keywords: pivot, table, excel, actual, vs, budget, sales, analysis

Id: dqc8QokPsAU

Length: 10min 11sec (611 seconds)

Published: Mon Jul 18 2011