Excel - Easy Year-over-Year in a Pivot Table - Episode 1998
2,785 views · Published 25 August 2016 · 4:03 · Indexed 31 October 2026
Channel: MrExcel.com · 2016 · Education
Microsoft Excel Tutorial - Year over Year in Pivot Table. Welcome to the MrExcel podcast, episode 1998. In this video, we will be discussing how to create a year-over-year report using Pivot Tables in Excel. If you're new here, make sure to check out the entire book on Pivot Tables by clicking the "i" on the top right-hand corner for the MrExcel Excel playlist. I'm Bill Jelen, and today we will be starting with a data set that spans two years, from 2018 to 2019. To create a Pivot Table, go to Insert, PivotTable, and click OK. This will create a new worksheet for the Pivot Table. Next, drag the Date field to the Rows area. In Excel 2016, the dates will automatically be grouped, but for those using Excel 2013 or 2010, press Ctrl+Z to undo and follow along. Now, we will take the Revenue field and drag it to the Values area. This will give us an initial report with hundreds of rows, as it includes every single day of sales. To make it more manageable, go to the very first date field, go to PivotTable Tools, Analyze (or Options in 2010 and 2013), and choose Group Field. Make sure to choose a date before grouping and select Months and Years, Quarters and Years, or Months and Quarters and Years. Click OK and a new virtual field called Years will appear in the Rows area. There are a few annoyances with this report, such as not having Year totals. To fix this, go to Field Settings and select Automatic. If you prefer to have everything in its own column, go to Design and select Show in Tabular Form. Then, select Repeat All Item Labels to fill in the blanks on the left-hand side. However, this step is not necessary if you plan on moving the Years field to the Columns area, as shown in the final report. To calculate the percentage change from year to year, we will need to remove the Grand Total by right-clicking and selecting Remove. Then, we will need to create a formula outside of the Pivot Table using the %Change formula. Make sure to type in the formula instead of using the mouse or arrow keys, as it will result in the incorrect function. Format the result as a percentage and double click to copy it down. This is just one of the 40 tips included in this book. To learn more, click the "i" on the top right-hand corner to purchase the book. Remember, to create a year-over-year report using Pivot Tables, simply insert a Pivot Table, drag the date field to the Rows area, undo the automatic grouping in Excel 2016, drag the revenue to the Values area, group the date field by Months and Years, move the Years field to the Columns area, remove the Grand Total, and calculate the percentage change using the %Change formula. Thank you for watching and we'll see you next time for another netcast from MrExcel. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Pivot Table for Year over Year analysis (00:14) Setting up the data and creating a Pivot Table. (00:37) Grouping the date field by Months and Years. (01:24) Customizing the Pivot Table for a better view. (02:00) Using the %Change formula to calculate year over year changes. (03:15) Clicking Like really helps the algorithm #excel #microsoft #yearoveryear This video answers these common search terms: how to graph year over year in excel pivot how to make a year over year chart in excel how do you make a graph in excel year over year how to do year over year in excel how to calc year over year growth in excel how to make year over year chart in excel how to group dates by year in excel pivot table how to make excel pivot table group dates by years how to group by month in excel pivot how to group by month in excel pivot table how to group dates by month in excel pivot table how to group column dates into months in excel pivot You have 2 years of detail data in Excel. Use a pivot table to quickly compare last year to this year. Recap: Start with multiple years of data Insert, Pivot table Drag date field to row area Excel 2016: Press Ctrl+Z to ungroup dates Drag revenue to values area Select any date in row area Use Group Field Choose Months & Years How to add subtotals to years field after grouping a pivot table Use Tabular form in a pivot table to give each row field its own column Repeat All Row Labels to fill in the blanks in a pivot table Drag years to the column area Right-click Grand Total column heading and remove How to avoid GetPivotData function when formula points to a pivot table Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153826/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920