Calculate Difference to Previous Years - Excel PivotTable Tutorial

hello and welcome to this video tutorial from computer Google comm and in this video we are going to look at how to use a pivot table to calculate the difference from previous year so as you may know pivot tables are an incredible summary tool that are brilliant for your reports and your dashboards and your analytical data so I want to use it on this big list of cells I've got here and I've got two years worth of data which happened to be 2011 and 2012 so a few years ago now but it is merely an example so let me start by clicking insert and pivot table and let's put this on a new worksheet so here we go and I'm going to have to start building it with my fields so what I'm going to do is bring in order date that is for sure and I'm going to put into the rows area originally and I'm going to right mouse click on one of those dates and group it so I'm looking for years and at the moment I have a specific date so I'm going to have two groups is to get the years that I want and I don't need months right now so I'm just going to click OK and I've got my two years for my data now I'm going to bring category into here as well because that is ultimately what I'm looking at comparing I'm going to compare a category cells over these two years hour now drift all the date as it's still called into the columns area so you see this is where we're going to get our comparisons now I need some calculation it's bring total sales value in and now I've got a grand total and I have some figures now I want to know what the difference is between them so I'm going to bring total sales value in again and the reason for that is going to be clear quite soon it's looking pretty bad at the moment you may say I'm repeating what I've got got 2011 here 2012 here and then some totals now first of all I don't need to lots of totals so I'm going to right mouse click and hide one of those totals columns straight away I'm not going to need that twice and then in this second lot I'm going to right mouse click summarize or say show values as and we've got all this fantastic stuff in here now you can see we've got difference from and percentage difference from for this example I'm just going to choose difference from oh the base feels to be the order date remember that means year at the moment because of its grouped status and I'll keep the base item as previous so when I click OK now I have the difference to the previous year now it's going to switch those around in this values area cuz actually I'd like them the other way around here we go like this so I wanted it to be that I've got the 2011 cells the difference and then the 2012 cells so how much he's got up and down it's just a couple they're going down as we can see let's make that a little bit clearer let me right mouse click on one of those values and number format and we need to put this into an accounting format but it also like the negatives to appear in red let's put it in an accounting format Alex be going to this custom area and then hunt that the one I want like this one here and click OK so now that really shines those negatives you can apply conditional formatting rules to pivot tables so that might be a better option right now you've got more scope to do what you want within conditional formatting now we can Bri name these headers along the top you know to create this up this is a report that you're producing regularly you want to make these efforts don't you so it might change some of these headers just simply in the pin that as it is total sales that will update the one over here this one can be last year difference and this first one I can even go as far as hide in that column because it's blank it has no relevance here and then maybe resizing some of these so this is all kind of aesthetics at the moment you know it has its place but just trying to make this report look as you want this pivot table look as you want so still got my total cells over here it's always formatting as well really and we just apply a normal accounting format for that here we go and now we have it we have the total sales for 2011 as it is the previous year of men got the difference shown that two of them dairy in grains have got worse since that year you know minor improvements for condiments and then I had the total sales for 2012 remember there are options in there for percentage difference from as well you may want that in addition to this we could have that in there as well no problem or instead of this but this is some of the you know the great tools or pivot tables you can't just is not limited to standard sum and count an average we have a bunch of good stuff in there you know to generate rankings and comparisons between other different categories or in this case different years I hope you found this video useful please check out some of our other videos on a youtube channel and come check us out at computer gauger com

Channel: Computergaga

Views: 99,431

Rating: 4.7911024 out of 5

Keywords: Excel PivotTables, Previous Year, Last Year Difference, PivotTables, Computergaga, Excel Tutorial, excel pivottable tutorial, excel tutorial pivot table, difference to previous year, pivottable show values as, pivot table show difference between columns, pivot table show difference between two dates, excel pivottable tricks, excel pivottable, calculated field in pivot table, pivot tables for beginners

Id: wdcMZ-FCb4g

Length: 6min 22sec (382 seconds)

Published: Thu Aug 03 2017