Home / Business & Management / Big Data & Analytics / How to Use Microsoft Excel for Data Analysis / Comparing Year Over Year Values Using the Pivot Table
Comparing Year Over Year Values Using the Pivot Table
.
[INTRO MUSIC PLAYING] Hello, welcome back. In this part, we are going to do something a little different and to go beyond the capabilities of the pivot table, see some of the deficiencies of a pivot table, and how can we overcome it. And, this is again related to my back and forth negotiation with this new Sales Manager, Lucy. Last time I showed her where we are, she actually said that there’s another category, a whole category, of types of analysis she’s interested in. And it’s very typical, I was not surprised. This is about comparing year over year.
And so, she wanted to see last year sales, this year sales, and the differences between those, how are we going to, are we going up down, in each category is each geography, and so on. So, I went back to my pivot to try and achieve this kind of analysis and I encountered some limitations to the pivot table technology. And we’ll see, how can we together work around them. So, let’s go to the Excel again. I created this pivot that has countries and categories nested. Notice this is– previously, we had like countries and state categories and subcategories, but there’s nothing wrong with having countries and categories nested below.
There’s no reason to only have there like the hierarchy of country and state. It could be any combination that makes sense for the purpose of this analysis. And, I have on columns the year 2015 and 2016. Now, the question is, how can I actually create a comparison between 2016 and 2015? We could go and look into calculated fields. It’s going to be too hard and actually almost impossible. Usually, I find that people are solving this issue by referencing the pivot from the outer, from a regular Excel formula. So, I would try to say like, create another column here, which is not part of the pivot, and this column will be– let’s call it growth.
It’s going to represent the growth between openings. The growth sometime is going to be negative growth between 2016 and 2015. So, I’m going to reference this sales and see what will happen here. When I click on the cell of 2016, instead of saying like in a regular Excel formula, C3, it comes with this function called Get People Data. This was introduced way back as a way to actually reference sales in the pivot. So, for the moment, I can go with it and try to understand it later. So I say, this minus this, and I got a number, which is–, oh actually I did. Yeah. Yeah, it’s actually negative in this case. Makes sense, and I can extend it.
I can just double click here, and immediately, I see one of the problems with this Get People Data when I dragged it down. So, first of all, let’s see what’s good about this. So, before we even extend it, let’s see what’s good about this Get People Data. It’s a function that was created exactly for this purpose of referencing into a pivot, and it includes a reference to the pivot itself saying, what is the value saying, and where is the like the anchor of the pivot? The pivot start at A1, and it says show me the value for year 2016, the country is Australia, and here it says year 2015. So, it’s kind of logically referencing an area in the pivot.
The good thing about it is that if I, for example, will go ahead and change the positions of those two.
Actually it didn’t really maintain the– What is that? Because the whole idea of the Get People Data is that if I will, let’s take it aside for a moment and let’s put it here, and I will just introduce, let’s say the months, in which after the–, Oh, it’s not enough space. Let’s put something simpler like the gender. So, you see here that even though the whole pivot moved, and gender is now in and the columns below the year, the value for the Get People Data here is still valid, because it’s still referencing the same sales and the same logical places in the pivot. So, that’s the idea of the Get People Data.
But, the disadvantages, as we saw before, is that when you try to drag it, it actually remains constant and it doesn’t adjust to when you drag it down or across like a regular Excel function. So, that’s why it’s really not very useful. So, I can just try to– you can get rid of it altogether. You can go to the ribbon, the part regarding the pivot table and you can go to Options here, and uncheck the option for generating Get People Data. So now, when I go and click on this, it will just give me regular reference, name, number, remember?
And now, it actually works. I can say growth, and I can even calculate growth percent. In the growth percent, I would say, this would be the growth divided by sales in 2015. So, I see the difference as a percentage. Format this as a percent, then we’ll click in the corner here, and I see for each combination of a country and category the percentage, and I can go ahead and add to it conditional formatting, as we saw before. So, all this is working pretty good, but this is very sensitive to any changes in the pivot. So, even if I actually take it, like take this area, and I just move it aside to make more space for it.
Now if I try, like I did before, to take the gender column and introduce it for some reason. Let’s say one gender under years, everything breaks. So, those references that were actually– this change in the period did not cause Excel to adjust the formulas in any way, and the formulas still are pointing to column C and B, but column C and B are now something completely different. So, we have to be very careful if we reference a pivot from the outside to any changes in the pivot. We cannot really change the structure of the pivot. Sometimes people actually do it differently. They copy also. They just reference all of the pivot.
So they say, let’s take this one and drag it across, break it down like this. And also, let’s take the actual– so just copying everything aside to regular formulas, to have a whole range of formulas, instead of the original range. The original way that I did it was partly the pivot itself and partly like here. This is a good way, but you can see the numbers. For example, you cannot create a chart that is partly the pivot and partly the areas outside the pivot. So, in order to create the chart, you will have to copy everything. So, these two options of using Get People Data and to reference in a regular way have their advantages and disadvantages.
In the next module, I will show you another way to actually replace the pivot altogether and calculate the aggregates with formulas. So, actually it’s not a pivot, doesn’t have the flexibility of the pivot. On the other hand, it’s all pure formulas, and with pure formulas there’s no surprises, there’s no– the pivot change suddenly and breaks all the formulas. So, we’ll see that in the previous one with another example of a type of analysis that they can do on top of the numbers. But this time, without a pivot at all. See you next time. [CLOSING MUSIC]
In the previous section, we experimented with calculated columns and conditional formatting as a way to analyse data and find anomalies in the data.
Although the Pivot is a useful and powerful tool, it does have some deficiencies. In this next step, you will discover some pivot table limitations and how these limitations can be overcome.
Comparisons
One limitation of pivot tables is the ability to view comparative data. It is not possible to use calculated columns in your pivot table to visualise your comparative data.
To get around this, you can create a column outside of your pivot table to reference data from your pivot table. This will result in the GETPIVOTDATA function.
The disadvantage of combining pivot data with non-pivot data is that you are unable to visualise the data in a chart.
Join the discussion
Why, in your opinion, is it so necessary for the growth of a business to compare year over year data?Use the discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as Complete button to check the step off, then you can move to the next step.
This article is from the online course:
How to Use Microsoft Excel for Data Analysis
Created by
This article is from the free online
How to Use Microsoft Excel for Data Analysis
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
