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

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.

Continued...

» Register now

The full article is available to registered AccountingWEB 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.

Comments

Sage dates aren't dates!

David Carter | | Permalink

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!

Grouping in Pivot Tables

carl.nottssport | | Permalink

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

Month Order

gradow | | Permalink

Hi
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.

no problem

David Carter | | Permalink

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

Pivot

gradow | | Permalink

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