Pivot Table Sales Report Variances 

Pivot Tables are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

Introduction to Pivot Table Reports

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 yet to 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 role field. So I'll take a Date, and I'll move it into the row field. 

Step Two 

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. 

Step Three

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. 

Step Four 

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. 

Step Five 

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 gonna choose Currency. I'll make sure to remove the decimals because we don't have any decimals here in our example. 

Step Six

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. 

Step Seven 

Now we're gonna 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. 

Step Eight 

Now I can change the format. I'll right-click, choose Value Field Settings, and I can choose Number Format. That'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. 

Step Nine

Then for the title, I'm going to change it to $ Var. If I want I can center this. I'll just go 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. 

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 % Var. Again, I could center this. I could also go to the prior column, the last column I worked on, choose Format Painter and then apply that same format there. 

Recap 

So 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.