how to publish a pivot table

Creating a Pivot Table with Grouped Dates

Imagine that you have the output from an accounts system: a table of payments made by your company. For each row of the table in Excel you have:

Creating a Pivot Table with Grouped Dates

Imagine that you have the output from an accounts system: a table of payments made by your company. For each row of the table in Excel you have:

the supplier code
the amount of the purchase
the date the purchase was made

You want to see the trend over time for each supplier, so you create a pivot table showing this data. To do this (at least for Microsoft Excel 2007 and Excel 2010):

Select a single cell in your table.
Click on the INSERT tab in the ribbon, and choose to insert a pivot table.
Choose OK to choose all of the default options (this will create a new worksheet).
Drag the supplier code onto the ROW LABELS section of the pivot table, the purchase date onto the COLUMN LABELS section and the amount of the purchase onto the VALUES section.

You should now have a pivot table with the supplier codes going down the left-hand side, the unique dates going across the top and the total spending for each combination of date and supplier as values in the middle. We now want to group the dates together into months, quarters or years.

To do this, click on the first date in your column headings. Now click on the DATA tab on the ribbon, and choose GROUP. A dialog box should appear, and you should be able to choose to combine dates together into months and/or quarters and/or years. Hey presto! an instant monthly or quarterly or annual reporting facility.

What Can Go Wrong

If you get the message CANNOT GROUP THAT SELECTION when you do the above, the reason is almost certainly that not all of your dates are dates. For example, if you have 9,999 dates and just one cell which isn’t a date, Excel will still refuse to group by your date field.

To solve the CANNOT GROUP THAT SELECTION problem, you need to persuade Excel that every single one of the dates in the date field column really is a date. To do this, first check the format – a left-aligned value can be a giveaway, since Excel will right-align dates by default. Another common problem is that dates imported from a mainframe system can appear with full stops (aka periods to American readers) instead of slashes: 25.12.2099 rather than 25/12/2099.

One way to spot problems like this is to use the ISNUMBER function, which returns either TRUE or FALSE depending on whether Excel recognises a date or not. Since Excel treats dates as numbers behind the scenes, you should look for the FALSE values returned by the ISNUMBER function and correct the corresponding dates.

One final point – Excel can be slow to recognise changes – you might find that it helps to save, close and reopen a spreadsheet before retesting grouping in your pivot table.

Andy Brown is a Microsoft Excel trainer for Wise Owl training. You can see a list of the Excel 2003, Excel 2007 and Excel 2010 training courses that Wise Owl offer at http://www.wiseowl.co.uk/excel/index.htm. We also publish our Excel exercises online, so you can test your knowledge of Excel pivot tables at http://www.wiseowl.co.uk/training/exercise-list/t-906.htm.

Article Source: http://EzineArticles.com/?expert=Andy_J_Brown

Leave a Reply