Resources for getting better with pivot tables?

[acknak]

I can manage (very) simple tables, but beyond that, I just don't get it. Can anyone recommend an approach for getting up to speed here?

For example, I have a table of (date, amount) where the dates span multiple years. I would like a summary of total amount for each month and each quarter. Do I have to add a column for the month/quarter, or is this a pivot option I'm missing?

An output table like this would be ideal:

month 2002 2003 2004 ...
Jan    123  245  201
Feb    443  351  239
...

Is that possible just from Data > Pivot Table > ...

[Villeroy]

First you create a pivot with date (every occurring day) and sum(amount). If there are many dates, you should put them in a row field. A column field may become wider than the sheet. Calculated fields (sum/min/max/etc) go to the data field.
The resulting pivot shows sums for each day.

In a second step you click a single date cell in the pivot and call menu:Data>Grop&Outline>Group...
If the type detection works, you can select years and months.
This grouping splits up the date field into two.

Now you can drag one of them from row field position (vertical categories) to column fields (horizontal categories). Row fields and column fields are different layouts for the same data. The calclulated values in the data field(s) for the row fields and column fields remain the same when you switch the orientation of row and column fields.

[MrProgrammer]

I would like a summary of total amount for each month and each quarter.

You can group by month and year, then in the displayed table drag the year to the column fields. Or you can group by quarter and year. When I group by month, quarter, and year, I don't get separate totals for the quarters, but it is simple to change the grouping between month/year and quarter/year. You can also create two pivot tables, one for each format. The additional sheet in my attachment was created with Data → Data Pilot → Start → Current selection → More → Results to → - new sheet -. Putting the pivot table in a separate sheet avoids three problems:
• If it's below the data, it gets buried far below
• if it's above the data, refreshes of the pivot table could overlay the data
• If it's to the left or right of the data, data filtering can hide the pivot table.

call menu:Data>Grop&Outline>Group...

Or press F12.

I find it difficult to describe what can be accomplished by dragging categories in the displayed pivot table, but the process is fairly intuitive once you learn it. I believe some table layouts are not possible to create in the pivot table dialog and must be done by dragging fields after creation.

It's clumsy, but it is possible to get totals by month and quarter in the same table. Group by month, quarter, and year, move year to the column fields, move Quarter to the right of Date, then create the quarterly totals with Data → Subtotals → Group by → Quarter → Subtotals by → {all but date/Quarter}. Calc will get the formatting wrong for the subtotals but that can be fixed by reapplying style DataPilot Value. However, the subtotals are lost with Data → Data Pilot → Refresh so this isn't a good idea unless the data is static.

[acknak]

Ok, that's fantastic!

I never realized you can adjust/re-group the table after it's created.

Thanks so much; I'll have to play with this a bit.