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!




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


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Show & Hide Field List in Excel Pivot Table Have you ever had the scenario where you are working on your Pivot Table and the Field List disappears?Lots of Excel users get annoyed with this but luckily I'm here to help you out 🙂If your Pivot Table Field List disappears, you can easily bring it back as I show you be...
Group By Quarters and Years With Excel Pivot Table... Ever encountered needing a quick report of total sales numbers by each quarter, of each year?I was faced with this same scenario and looking at my data on hand, I only had sales numbers for each individual day.Grouping these would take a ton of effort & complex formul...
Show The Percent of Parent Row Total With Excel Pi... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT ROW TOTAL calculation.This is a new calculation in Excel 2010 and onwards.This option will immediately calculate the percentages f...
Pivot Table Styles The default Pivot Table style in Excel is very bland and boring, stemming from a lack of creativity from the nerds over at Microsoft!You can give some life to your Excel Pivot Table by simply changing the Pivot Table Style!For our example, this is our Pivot Table:...