Pivot Tip 2 - How to analyse sales by month. By David Carter

Kashflow logo
Share this content

David Carter's series of 'Five Minute tips' aims to develop your skill at using Excel pivot tables. If you want to test the tip for yourself, he takes you through a worked example on the 'pivot practice' database.

When you create a pivot table report in Excel, you will usually want to summarise the report totals by week or month. Use the Group and Show Details command for this.

Right click the mouse on the 'Date' field button in B3. From the pivot table menu, select 'Group and Show Details', then 'Group'.

Practice Session:

To practise this tip, open up the Excel file Pivot_Practice.xls.

First, create the following pivot table:



Remove any subtotals from ACC

Note: If you are unsure how to do this, the first article in the series...

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.


Please login or register to join the discussion.

31st Oct 2005 19:51

Sage dates aren't dates!
When Sage Line 50 exports dates onto Excel, they actually come over as text. Try sorting by date, and you will see that they don't actually sort into date order.

You first have to turn the dates into proper dates. The easiest way is to highlight the Date column, then Data - Text to Columns - in Step 3 tick the DATE button. Finish.

Excel will now modify the column into real dates.

To check that a date really is a date in Excel, highlight the column, then Format - Cells - Number. If it changes to a number like 39000 or thereabouts, it's a date. If it still looks like a date - it isn't one!

Thanks (0)
31st Oct 2005 15:13

Grouping in Pivot Tables
I've decided to try and learn more about Pivot Tables and am working through David Carter's Tips. With Tip 2 i've got the grouping to work fine with the demo data but when I try the same excercise on my actual data from Sage Line 50, and Group on Date I get the message "cannot group that selection". Any ideas what I am doing wrong?

Thanks (0)
By gradow
02nd Jun 2005 18:12

Month Order
Pretty new to Pivot Tables so excus emy ignorance.
I used the grouping function to total by month but it then runs by calendar year; Jan-Dec. I want it to run by financial year, July to June. Looking ofrward to your answers - great site.

Thanks (0)
04th Jun 2005 22:48

no problem
Hi Graeme
If you take another look at the Grouping box you will see at the top a "Starting at" field and an "Ending at" field.

In "Starting at" type in July 1st 2004 and in "Ending at" type in June 30 2005. Then below that make sure you highlight both "Month" and "Year". This should do it. David

Thanks (0)
By gradow
16th Jun 2005 17:09

Thanks for that - it has worked well and my report is issued!

Thanks (0)