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:

DOWNLOAD EXCEL WORKBOOK

 

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

group-by-time-01

 

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):

group-by-time-02

 

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

group-by-time-03

 

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.

group-by-time-04

 

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:

group-by-time-05

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

group-by-time-05b

 

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

group-by-time-06

 

STEP 7: Select Number Format

group-by-time-07

 

STEP 8: Select Currency. Click OK.

group-by-time-08

You now have your total sales for each hourly period!

group-by-time-09

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!

group-by-time

HELPFUL RESOURCE:

PIVOT BANNER

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Frequency Distribution with Excel Pivot Tables With Excel Pivot Tables you can do a lot of stuff with your data! But did you know that you can even create a Frequency Distribution Table? Let's have some fun below! I'll show you how easy it is to create your own Frequency Distribution Chart! We will create a chart based ...
Move an Excel Pivot Table If you have your Excel Pivot Table ready, but it's not in the best location, how do you move this onto another section of your Excel worksheet? Well, you can simply select Move PivotTable and you can move your Excel Pivot Table very quickly! For our example, let's move this...
Group Sales by Weeks With Excel Pivot Tables I am sure that your boss has asked you to come up with Total Sales numbers per week at least once in your current role!  If not, he will....very very soon! Now your data has only sales for each individual day, which makes you scramble to come up with a solution.   How will you...
Group By Half Years With Excel Pivot Tables   In our previous post, we were able to Group by Month using Pivot Tables.  Let us take it up another notch, and group it by Half Years! In the example below I show you how to get the Sales Grouped by Half Years: January to June and July to December   DOWNL...