Excel Year-Over-Year and Period-Over-Period Analysis with PivotTables
HTML-код
- Опубликовано: 22 июл 2024
- In excel you can do Period-over-Period (POP) analysis and Year-over-Year (YOY) analysis. Year-over-Year analysis will compare the selected period to the same period last year. For example, comparing August of the current year to August of the previous year. YOY can be weeks, months, quarters, or years. Period-over-Period will compare the selected period to the preceding period. For example, August of the current year to July of the current year.
We will use PivotTables to do our YOY and POP analysis. We use Difference from and we will calculate percentages.
Chapters:
00:00 Introduction
01:25 Create PivotTable
03:47 Subtotals in Group
04:21 YoY by Month
05:16 Compare to the previous year
07:17 Period over Period
08:30 Percentages
10:11 Highlighting negatives
11:26 Negative percentages in red font
12:12 Closing remarks
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ RUclips keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!
Excellent video. Always simple and to the point. Thank you!
You're welcome!
I needed this for a project I am currently working on. Checked youtube and I stumbled on this. It doesn't get an simpler than this. Well explained in clear and simple terms. This has to be the 1% I learnt today in my 1% EVERYDAY journey. Thank you.
Thanks for this video Chris! Very useful.
Very simple and clear. Easy to understand. Happily keep popping back to this channel for tips an tricks.
Thank you.
Thanks, Jacob.
Helped me get what I needed. Thank you!
Thanks Chris! Great Video!
Me in my coporate job taking notes! Thank You!
LOL. Thanks!
This was fantastic! You exactly answered my questions. I knew I needed to use the "Difference As" function, but I couldn't see why I wasn't getting the result I needed. Now I'm a pro! (almost) :) Thank you!
Awesome. Thanks, Chrystal!
Thanks Chris for your wonderful Excel presentations, all very informative and helpful 😊
Thank you! I appreciate the positive feedback.
Thank you so much! This really helped me out with my project at work today!
Great to hear!
Great video thanks Chris, I've never used the 'Show Values as' before!
Thanks, Joanne.
Thank you Chris! This has definitely helped me and I look forward to learning more from your channel, have subscribed.
Awesome, thank you!
Great Video! Thanks for sharing.
You bet!
chris you the man! thank u so much !
Thank you!
One million dollars worth question, I added the field "%Difference from", however instead of showing me the index - 2024/2023 SO data, like you have in the video, it shows me difference between these two values in % ((2024 value - 2023 value)/2023 value), Can't find out how to change that calculation to get the same results, have you any idea how to sort this out please?
Hi Chris, great video thanks! Is it possible to filter and show only the negative percentages in red?
That was so helpful! thank you so much. have you covered how you could then present that information in a chart?
Very helpful
This video is great… Could I use your same methodology for week over week order data in units? Or could I use this to compare weekly forecast data? I want to have the pivot table built and just be able to add new data every week and refresh the pivot.
Hi Chris, Nice Video. Thanks.
Can you explain what to add as a helper column if i want to see Jan growth over Dec (in Period Over Period)
Hi Chris. Loved this video. I learned a few tricks, so thanks for that. Question 1 is does my 'raw' data have to be in a certain format for the pivot table to display correctly? Qn 2 - In 2022, I am new to dashboards but was able to create a dashboard where senior staff can view one employee's productivity against every employee performing the same tasks. My employer wants to see the data as daily, monthly, and now year over year. Would you happen to know how to create a dynamic DAILY comparison of an employee against other employees?
I see a problem in sorting the values from the most recent date to less recent. All the data are messed up. Do you have a solution for this? Thanks.
great tutorial...
Thank you!
what about if data is on two different sheets?
Hi Chris, in the video, u were showing us how to do period over period (month vs previous month). Will you able to show us how to Compare Month-Over-Month (Previous Month and Same Month Last Year) ? Thanks in advance, Chris.
I think you can achieve that by selecting the month attribute above the year attribute in the pivot table field list. In another words, swap the two items in the Rows section.
nice!
Thanks!