Excel Magic Trick 1559: PivotTable: Year Totals, YOY Change, % YOY Change from Daily Sales

using the pivot table feature we need to create this yearly report that shows sales year of a year change and percentage year of your change given that our sales table has daily sales now last video we saw how to do this with worksheet formulas this video we'll see how to do it with the pivot table feature we have a single cell in our proper data set selected we go up to insert there's the table group click on the pivot table button I want to put this on an existing sheet location I'm selecting f2 click ok over on the right we see our pivot table field list sales date and compressor those are the three columns we have in our data set our goal is to get years and then make the calculations for each year we take our dates and that column is filled with daily dates and when we drag it down to rows something magic happens it automatically groups it up by year now if you're in a version that doesn't automatically group you'll see daily dates you simply come over and right-click and use the grouping feature to manually select whichever groups you want to use now we don't need quarters so I'm gonna click and remove it same with dates we see there's a new column over here years there's our years the magic of the pivot table gets even better when I drag sales down to values I get total sales for each one of the years select the top column we're gonna rename the label I'm going to indicate the unit in the header we would like to format all of these numbers and because it's in a pivot table we select a single cell right click point to number formatting we're gonna select number comma 0 decimals click OK now we made a sum calculation on total sales now we drag sales down a second time it did the same default adding based on our year we want to change the calculation so right click summarize values by changes the get function that makes the calculation or this one show values ass we come down to difference from we only have one field in the row area so the base field is automatically selected we don't want to use the base year 11 we want to select previous and when I click OK just like that for 2011 it took current year - previous year and gave us the difference select column header will say year-over-year change and enter right click number formatting will use the same settings sales again down to values for the third time right click and this time we'll select % difference from same settings and this time it automatically added the number formatting will select the column header percent year-over-year change and there's our finished report now if we think back to what we did last video it's much easier to create this type of year-over-year report using a pivot table looking back at what we did last video I want you to look at the 2018 total right there if we come to the data set we're gonna change this to a really big number and watch what happens to that number right there and the subsequent calculations when I hit enter enter they instantly update creating a report like this with formulas is more difficult to create but when you want your report to instantly update when source data changes that's when you use formulas if we make the same change in our pivot table data source watch what happens when I hit enter it doesn't automatically change to update our pivot table we right click refresh that's not hard to do so remember if you use a pivot table and source data changes be sure to refresh [Music]

Channel: ExcelIsFun

Views: 9,873

Rating: 4.9511003 out of 5

Keywords: Excel, Highline College, Mike Girvin, excelisfun, EMT, EMT 1559, Excel Magic Trick 1558, Excel Magic Trick 1559, EMT 1558, PivotTable for Total sales from daily dates, YOY Change in Sales, % YOY Change in Sales, Percentage Change in Yearly Sales, adding sales between the first of the year and the end of the year, YOY Report with PivotTable, PivotTable YOY Report, YOY Change Report, Pivot Table YOY Report

Id: hm55JmasxeE

Length: 4min 20sec (260 seconds)

Published: Sun Jul 07 2019