molecule

Returning to a Pivot Table for Advanced Operations in Excel 2007:-


Pivot tables are very powerful. A subset of pivot table operations are available in the pivot chart interface. Sometimes, however, you need to return to the pivot table in order to carry out an advanced operation. After you have made the change to the pivot table, you can then click the pivot chart in order to return to the pivot chart interface.

One example of an advanced operation is grouping daily dates up to months and years. Because the underlying data is transactional, it is reported at the daily level. If you build a pivot chart with dates along the Axis Field, your chart will be a confusing mess. In the first Figure (given below), for example, Product is moved to the Legend field, and Date is shown in the Axis field. With five years’ worth of dates, you cannot make out any details.



(It is easy to summarize the daily dates to yearly and monthly dates in a pivot table.)

To group the data in the Date field, you follow these steps:

1. Select any date cell, such as cell A3. Excel puts away the Pivot Chart Tools ribbon tabs and adds the PivotTable Tools ribbon tabs.

2. From the Options ribbon, choose Group Field.

3. In the Grouping dialog, Excel defaults to grouping by months. You need to add Years in the Grouping dialog box. Otherwise, Excel will add January of this year and January of last year into a single value called January. Look into the second Figure given below).



(You choose Months and Years from the Grouping dialog.)

4. Click OK. Excel adds a new virtual field called Years to your field list and adds this field to the Row Labels field. The original field called Date is recast to include months.

5. Click the chart to put away the PivotTable Tools ribbon tabs and bring back the PivotChart Tools ribbon tabs.

After grouping, you can see details by month and year, as shown in the third Figure (given below).



(Excel presents both years and months along the horizontal axis.)

Although the grouping feature initially adds both the year and month information to the same area of the pivot table, you can easily split them apart in order to show year versus year data. In the fourth Figure (given below), the Product field was dragged out of the Legend Fields drop zone, and the Date field was moved to the Legend Fields drop zone. You can now compare monthly sales from one year to the next.



(You can separate the year and date fields in order to show year-over-year comparisons.)