Excel Pivot Table Grouping Tutorials

Group By Custom Dates With Excel Pivot Tables

In this Group By Month post, we grouped our Sales by each month.
However the cool thing with Excel is that we can take that a step further and customize our grouped date range!
In the example below I show you how to get the Sales Grouped by Custom Dates:

STEP 1: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet

STEP 2: In the ROWS section put in the Order Date field.
Notice that in Excel 2016 (the version that I am using) it will automatically Group the Order Date into Years & Quarters:

STEP 3: Right click on any row in your Pivot Table and select Group so we can select our Group order that we want:

STEP 4: We need to deselect Quarters, Years and make sure only Months are selected (which will be highlighted in blue). This will group our dates by the Months only.
We can now define a custom date range!
For example, we can only show the Sales numbers from January 2014 to September 2014.  To do this we need to set the following:
Starting at: 2014-01-01
Ending at: 2014-09-30
Click OK.

STEP 5: In the VALUES area put in the Sales field.  This will show the total of the Sales for each Month:

We can see that everything that does not fall in between January and September is Grouped into its own bucket.

STEP 6: Now we have our sales numbers grouped by Month & Years, notice that we can improve the formatting.
Click the Sum of SALES and select Value Field Settings

STEP 7: Select Number Format

STEP 8: Select Currency. Click OK.

You now have your total sales for each monthly period based on your custom dates! Very Easy!

Group By Time With Excel Pivot Tables

Let’s put yourself in my place for a second.
It was a dark, rainy night in Dublin city and I was working overtime…again!
I downloaded a report with lots of transnational data.  Time of Sale in one column and Sales in another, among other columns.
I wanted to get the Sales numbers by the hour, so that I could understand which part of the day had the best sales and which is the slowest time of the day.
Grouping these would take a ton of effort!
Thankfully there is the Pivot Table way, which is quick and reduces the risks of making any errors….and it makes updating the report easily with any new additional data!
In the example below I show you how to get the Sales Grouped by Time:

STEP 1: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet

STEP 2: In the ROWS section put in the Time of Sale field.
Notice Excel will automatically Group the Time of Sale field in Excel 2016 (a new feature):

STEP 3: Right click on any Row item in your Pivot Table and select Group

STEP 4: It was able to determine our time range (minimum time and maximum time).
Make sure only Hours is selected (blue highlight).
This will group our times by the Hours only.  Click OK.

STEP 5: In the VALUES area put in the Sales field. This will get the total of the Sales for each hourly range you have defined:

Now we have our sales numbers grouped by the hour, notice that we can improve the formatting:

STEP 6: Click the Sum of SALES and select Value Field Settings

STEP 7: Select Number Format

STEP 8: Select Currency. Click OK.

You now have your total sales for each hourly period!

Extra Tip: You can Right Click on any Sales value in the Pivot Table and select Sort > Largest to Smallest.  This will show you the part of the day that has the most sales at the top!

Errors When Grouping By Dates With Excel Pivot Tables

In our Group By Date post, we grouped our sales numbers by weeks.
However there are times when we try to group by Dates and we get an error message!  That is frustrating!
Instead of checking the dates one by one to find out where the error occurred, I will show you a cool way to get to those problematic dates & fix them in one go!
We already have the pivot table ready for grouping by Dates:

In the example below I show you how to get the Errors when Grouping By Dates:

 STEP 1: Right click on any row in your Pivot Table and select Group so we can select our Group type that we want:

However we notice that we have an error!

STEP 2: To check where our error occurred, go to the data table and highlight the column that contains our dates.

STEP 3: Go to Home > Find & Select > Go To Special:

Make sure the Constants, Text and Errors are selected.  This will select all our invalid dates (Errors) and text data (Text).

STEP 4: Excel has now selected the incorrect dates.  To make each incorrect cell easier to view go to Home > Fill Color

The invalid dates are now highlighted:

STEP 5: Manually replace the incorrect dates with the correct dates:

STEP 6: We need to Refresh our pivot table to load our new correct dates but first we need to “uncheck” the ORDER DATE field.

Right click on the Pivot Table and click Refresh:

“Check” the ORDER DATE Field:

STEP 7: Right click on the Pivot Table and click Group:

The Grouping Options are now displayed!  Your data is now clean!