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 be able to group them together and get the sales report your boss needs?

Formulas?  Maybe…well NO!  Too difficult.

Macros?  Don´t know how to code!

Pivot Tables?  HELL YEAH BABY!

Yes, Pivot Tables are quick and reduce the risks of making any errors….ah yeah, I almost forgot…it is also easy to add new data to your sales report!

In the example below I show you how to get the Sales Grouped by Date:

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-date-01

 

STEP 2: In the ROWS section put in the Order Date field.

Notice that in Excel 2016 it automatically groups dates into Years & Quarters.  We do not need this expansion.

Click the Undo button:

group-by-date-02

 

After clicking Undo, you will only have the Order Date in the Rows area:

group-by-date-03

 

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

group-by-date-04

 

STEP 4: It was able to determine our date range (minimum date and maximum date).

Deselect Months and make sure only Days is selected (depicted by the blue color).

Set the Number of days to 7.

This will group our dates by a weekly range (every 7 days).

Click OK.

group-by-date-05

 

STEP 5: Notice that we have Date Ranges now which are 7 days long each :

group-by-date-06

 

STEP 6: In the VALUES area put in the Sales field.

This will get the total of the Sales for each 7-day date range you have defined:

group-by-date-07

 

You now have your total sales for each 7-day period!  If you like you can insert a Pivot Chart to visually show the data to your miserable boss!

group-by-date-08

group-by-date

HELPFUL RESOURCE:

PIVOT BANNER

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

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 Sa...
Show The Percent of Row Total With Excel Pivot Tab... 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 ROW TOTAL calculation.This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, exp...
Show The Percent of Parent Total With Excel Pivot ...  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 TOTAL calculation.In the example below I show you how to get the Percent of a Product Total i.e The % Sales of each Product ...
Show The Percent of Difference From Previous Years...  I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer.One is using Formulas, but that will take time to set up and you are exposed to errors!The other method is t...