Advanced Excel Summation Techniques Using Pivot Table

Grouping Pivot Table Data in Excel
Grouping Pivot Table Data in Excel

There are many ways to Sum data in Excel.  But when you have lots and lots of data, then you will want to learn a few different techniques.  In our last post, you were presented with a challenge of summing 150,000 data points in several ways.  One is a SumProduct formula, one was a SumIFS formula and one was to do it as quickly as possible using a Pivot Table.  If you want to download the data and try it for yourself, go to this post:  Friday Challenge – Advanced Excel Summation Skills

So that we can check out work on the other advanced Excel formula techniques, we should first create the Pivot Table version so that we know is the right answer.  Some may call this a Check Sum to validate our answers for the SumProduct and SumIFS formulas.

Grouping Pivot Table Data in Excel
Grouping Pivot Table Data in Excel

Normally, I would have added 2 more columns of data to the spreadsheet data.  I would have created one column for month of the transaction date and one for year.  Then I would use those new data points as my columns and filters.  However, this will make your file a lot larger when you are talking about 150,000 data points.  So this is not highly desirable.  Therefore, we should learn some more advanced Excel Pivot Table Options.

The Breakdown

1) Create Pivot Table

2) Create Group on Date





3) Filter Dates

Step-by-Step

1) Create Pivot Table

First, we need to create the Pivot Table by highlighting all 150,000 data points including the header labels and then go to your Insert Ribbon and Click on Pivot Table.  It doesn’t matter where you put it (i.e. on a new sheet or existing sheet, just don’t put it directly on top of the data.  Then, put “New Date” in the Columns, then put “Store ID” into the Rows and put “Sales into the Values section of the Pivot Table Field List.

Your Pivot Table Field List will look like this when you are done:

Pivot Table Field List
Pivot Table Field List

Your Pivot Table results will look like this:

Pivot Table Raw Data
Pivot Table Raw Data

2) Create Group on Date

Now that we have our Pivot Table created, we need to create some groupings.  I will show you the technique I like but I also show you another way in the Video.  It is a short video, show check it out below.



To create our Groupings for the Pivot Table, first Right Click on one of any of the dates that you see in the various columns.  You will then see this pop-up menu:

Right Click on Date for Pop up Menu and Select Group
Right Click on Date for Pop up Menu and Select Group

Next, you will see this Excel Pivot Table Grouping Dialog Box.  From here, you will want to enter in the Start Date of 1/1/2016 and an End Date of 12/31/2016.  Also, choose “By” as Months and click on the Okay button.

Excel Pivot Table Grouping Dialog Box
Excel Pivot Table Grouping Dialog Box

Once you click on okay, your Pivot Table results will change and look as you see here:

Pivot Table Results
Pivot Table Results

You will notice that the Pivot Table is now Grouped on data for months with anything before the start date that we entered under <1/1/2016 and anything after our end date showing up under >12/31/2016.  We will filter these out in our next steps, but you can see that Excel’s Pivot Table Grouping feature is very powerful.

3) Filter Dates

To Filter out the dates that we don’t want (before January 2016 and in 2017 and beyond), we need to simply click on the Sort and Filter Pull Down Options on the Column Labels in the Pivot Table.  Then uncheck the “<1/1/2016” value and also uncheck the “>12/31/2016” values in the filter list.





Filter Pivot Table Columns Labels
Filter Pivot Table Columns Labels

Your final Sales Data Pivot Table by Store by Month for 2016 will look like this:

Final Grouped and Filtered Pivot Table
Final Grouped and Filtered Pivot Table

That was a lot simpler than what I used to do.  We can also use this for the Check Sum against our SumProduct and SumIFS functions/formulas that I will show you in the next several videos.  And finally, I will show you an Array solution as bonus material as well.

Video Demonstration

Check out a video tutorial of this Excel Tip and also the additional way you can do Pivot Table Groupings.

Were you able to do it with a Pivot Table in another fashion?  If so, let me know how you did it using Pivot Tables in the comments below.

Steve=True







LEAVE A REPLY

Please enter your comment!
Please enter your name here