Pivot Table - Rolling Up Dates

I am in the process of updating my Pivot Table course for Excel 2010 so I thought I would go through some of the fun things you can do with pivot tables here in my blog.  The new slicer function is extremely cool but let me start with a few basics before I get there. Today, I want to talk about rolling up dates in a pivot table.

You can do it in all the versions - it is just a little bit easier in Excel 2010.

• First, you click in your data and select Insert>Pivot Table
• Then drag the field from the Field List Dialog box to the specified area.
• In my case I dragged Order Date to the ROW, Ship Via to the COLUMN and Order Amount to the VALUES section

to create the pivot table below.

 

 

There are a  lot of orders here so the pivot table is not that useful. It would be useful if I could see orders by month, quarter or by year. In other words, I want to "roll" up the dates and view it by month and by year.

• You can either right-click on the dates

• or in Excel 2010 you can also click on the Group Field icon on the contextual Pivot Table toolbar.

Either of these methods brings up a Grouping date dialog box.

At the top, it displays the newest and the oldest data.

•  In this dialog box, I selected months and then held down the Control key to also select Years.

(It is recommended that you always select years - if you do not and there is more than one year of data, Excel will not differentiate and will instead treat it all as a single year).

Now, the orders in my pivot table display by Year and by month and is much more useful than the first pivot table.








You can hide the individual months by clicking on the button immediately to the left tof the year or by right-clicking and selecting Expand/Collapse.

Also, if you take a look at the pivot table field list, you will see that you have a new field called Years that did not exist before. You can move this field around in the pivot table and you can even use it in a new pivot table that is based upon this one.