Learn how to effectively create and customize pivot tables to analyze sales variances, using advanced calculations like "Difference From" and "Percentage Difference From." This article demonstrates detailed steps to structure your pivot table data clearly and professionally.
Key Insights
- Create pivot tables by adding date fields into rows and sales data into values multiple times, then group dates by years and quarters to organize sales information clearly.
- Use the "Difference From" calculation in the value field settings to measure sales variances across years, enabling precise financial analysis.
- Apply the "Percentage Difference From" calculation to effectively display year-over-year sales performance in percentage terms, enhancing data clarity and insight.
Pivot table sales report variances. In this section, we're going to create a pivot table and make it look exactly like the pivot table we're seeing here. In order to do this, we're going to have to use one of the value field settings calculations that we have not yet that we have yet begin to use.
So let's start. Now, if you think it's going to be pretty simple to recreate this pivot table, because you may be thinking to yourself, let me just click on this pivot table and see what fields are selected. You'll be disappointed to learn that this is actually just a screenshot.
This is not actually a real pivot table. So what this will be useful for is using this to compare with our pivot table as we create it and see if it starts to look the same. So I'm going to move it down here right next to the blank pivot table.
Now, it's not that much of a mystery. We do provide you instructions that walk you through the steps to create a pivot table that looks just like the one in the screenshot. So I'm going to click inside the pivot table.
Step one, we want to put the date into the row field. So I'll take date and I'll move it into the row field. Next step, we want to put the sales value into the value field three times.
So I'm going to take sales and move it into the values once, take it again twice, third time, move it into values. Then we want to group the data by quarters and years. That's going to make a big difference because our screenshot has the date information grouped by years and quarters.
I'm going to right click on any individual date and choose group. And when I choose group, I want to use quarters and years as the time periods for my dates. I'll click OK.
That goes a long way to making our pivot table look very much like the one in the screenshot. Now, if I head over to design, I can choose a design that more closely aligns with the one that we have. And it's going to be the last green option in the medium row, very first row of medium.
OK, so far so good. Now I'm taking a look and comparing. And one of the things I can do is make a change to the format for the currency values.
I'm going to right click, choose number format, and I'm going to choose currency. I'll make sure to remove the decimals because we don't have any decimals here in our example. Next, I'll go in and change the name of the title from sum of sales to total sales.
And that first column is pretty much identical. Now we're going to have to focus on using a new calculation that's available in value field settings. I can get to value field settings by simply clicking on any cell within that column, right clicking, and choosing value field settings.
We're looking for a show value as type of calculation. So I'm going to click on show value as. And in the no calculation section, the one we're looking for is called difference from.
I'm going to select it right here. Then we get to choose the base field and the base item. The base field in this situation is years, and the base item is the previous year.
So I'm going to select previous. And then when I click OK, that is now reflecting the difference between 2016 and 2017. That's the variance.
Now I can change the format. I'll right click, choose value field settings, and I can choose number format. It's another way that you can change the format of the values.
I'm going to take away the decimals, click OK, and then click OK. Then for the title, I'm going to change it to dollar sign VAR. If I want, I can center this.
I'll just go over to the home tab, center align. The last column is showing the difference between 2016 and 2017, but it's doing it as a percentage. So another way that I can get to show value as is to simply right click on any field in that third column, and I can bypass value field settings.
And notice that show value as has a submenu that displays all the different calculations from the right click menu. So what I'm looking for is percentage difference from, and I can choose that right from here. Now the base field again is going to be years, and the base item is going to be previous.
All I have to do is click OK, and now I get the difference between these two years as a percentage. I'll go to the title and change the title to percentage VAR. Again, I could center this.
I could also go to my last, the prior column, the last column I worked on, choose format painter, and then apply that same format there. Now as I compare my pivot table with the one in the screenshot, I see that I've successfully recreated that pivot table and used the value field settings show value as calculations, difference from, and percentage difference from.