Excel - Year over Year Pivot Table with Percentage Growth - Episode 2152

Microsoft Excel Tutorial: Build a Year-over-Year report in a pivot table and show the % Growth. Welcome to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be discussing how to calculate year-over-year growth percentage in a pivot table. This is a common task for many Excel users, and we have a special guest, Tobias from Sweden, who will be sharing his expert knowledge on this topic. During my recent seminar at Excelapalooza 6, Tobias and his colleague Robert attended my pivot table session. Tobias, who has written his own pivot table book, showed me a better way to calculate year-over-year growth. Let's dive into this technique and see how it can make your pivot table analysis more efficient. First, we will insert a pivot table and add the date and revenue fields. Then, we will group the dates by months and years. Next, we will move the years field to the columns section and remove the grand total. This is where Tobias's method comes in. Instead of creating a calculated item, which is not allowed when using grouping, we will add the revenue field again and change the calculation to "% difference from" with the base field as year and the base item as previous. This will give us the year-over-year growth percentage for each year. We can then hide the extra column to clean up our pivot table. The advantage of Tobias's method is that the calculation will adjust automatically as the pivot table shrinks or grows. This is especially useful if you are using slicers to filter your data. On the other hand, my previous method required the calculation to be done outside of the pivot table, which had its own set of issues such as the GetPivotData problem. So, a big thank you to Tobias for sharing this great trick with us. If you are interested in learning more about pivot tables, I highly recommend checking out Tobias's book (if you can read Swedish) or my book, "Power Excel With MrExcel" (available in English). Both books cover advanced techniques for working with pivot tables and will help you become an Excel pro. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time! 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-... #excel #microsoft #microsoftexcel #exceltutorial #exceltricks #evergreen #pivottable #pivot_table #excelpivot #excelpivottablestutorial This video answers these common search terms: how to add percentage change in excel pivot table how to calculate percentage change in excel pivot table how to create a difference formula in excel pivot table avoiding getpivotdata in excel Table of Contents: (00:00) Year Over Year Growth Percentage in a Pivot Table (00:11) A Better Way to Calculate Growth Percentage (00:32) Step-by-Step Guide to Adding the Calculation in a Pivot Table (01:17) Limitations of Traditional Method and Introduction to New Method (01:52) Difference From Previous Item (02:41) Benefits of this method (03:00) Clicking Like really helps the algorithm I almost always do the percentage growth calculation as a formula outside of the pivot table But that has issues, such as GetPivotData and the formula needs to handle if the pivot table shrinks or grows Using a technique that I learned from Tobias Ljung, you can build the calculation in to the pivot table. Add revenue a second time to the pivot table. The calculation is % Change from, Years, Previous Item Hide the % change for the first year (they are all blank) Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...