Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

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

by
19th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

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:

ROW = ACC, CUSTNAME
DATA = NET

Remove any subtotals from ACC

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

Second, group by Date:

The first line of the pivot table before you should read 'A001 Anglo American 10620'

Right click anywhere within the pivot table. The pivot table menu appears.

Select: Pivot Table Wizard - Layout - the COLUMN-ROW-DATA box appears

Drag and drop the DATE field into the COLUMN area. OK Finish.

The pivot table is recalculated. Across the top there is now a series of dates.

The dates need to be grouped by month. To do this:

Right click on the grey DATE field button in cell C3. The pivot table menu re-appears.

Select: Group & Show Detail - Group The 'Grouping' Dialogue Box appears

Months is highlighted as the default. Scroll down to the bottom of the list under Quarters and highlight Years as well. OK.

The pivot table now displays the transactions grouped by month ' Apr, May, Jun.

Now rename and save the pivot table as follows:

At the bottom of the screen, the pivot table exists in a new worksheet called Sheet1.

Double left click onto Sheet1. It is highlighted. Type in the new name Pivot. Enter

Now save the Pivot Practice workbook on your own machine. Future articles in this series will use the 'Pivot' worksheet as their starting point.

Related material in ExcelZone
For more than four years, David Carter and AccountingWEB members have built up a massive stockpile of material on Excel and pivot tables. To delve more deeply into the subject, see:

  • David Carter's Five Minute Pivot Table Tips - index
  • Want to learn about Excel pivot tables? Start here
  • The Excel Compendium - Pivot Tables
  • 100 Best Time-Saving Ways to Use Microsoft Office by Simon Hurst

    Subscribe to the ExcelZone newswire
    To keep up with David Carter's Five Minute tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

  • Tags:

    Replies (5)

    Please login or register to join the discussion.

    avatar
    By David Carter
    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)
    avatar
    By carl.nottssport
    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

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

    Month Order
    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.

    Thanks (0)
    avatar
    By David Carter
    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)
    avatar
    By gradow
    16th Jun 2005 17:09

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

    Thanks (0)