Why can t I group my month fields into quarters?

Why can't I group my month fields into quarters?

Problem

Instead of having one field that contains months, you have several fields that represent each month. Your pivot table looks similar to the one shown in Figure A.1.

Figure A.1. Instead of one field that contains months, each month is its own field.

The issue is that there is no way for you to group these months into quarters because pivot tables can only group the data items within a field; they cannot group fields together. Because each month is separated into its own field, you will not be able to group them in their current state.

Solution

The source of this problem is the table structure of the data source. The table shown in Figure A.2 has a flawed structure. A general rule when working with pivot tables is that none of the column names in your data source should double as data items that will be used as filters or query criterion (for example, names of months, dates, years, names of locations, names of employees).

Figure A.2. The table structure of the data source is flawed. Most of the column names in this table double as criterion used to identify the month.

As you can see in Figure A.3, the solution to this problem is to change the structure of your data source to contain three columns: Market, Month, and Revenue.

Figure A.3. Creating separate columns for market and revenue will allow you to use month names as data items in your pivot table, instead of fields.

Luckily, there is an easy method to alter the table structure from Figure A.2 to the one shown in Figure A.3. Strangely though, this method involves the creation of a pivot table.

1.	Place your cursor anywhere inside your table and then go up to the application menu and select Data, PivotTable and PivotChart Report.
2.	When the PivotTable Wizard activates, select Multiple Consolidation Ranges and then click Next.
3.	Select I Will Create the Page Fields and then click Next.
4.	The dialog box that pops up will ask you for the range of your data source. Enter the range in the Range input box and click Finish.
5.	At this point, you will have created a pivot table that doesn't make much sense. In the pivot table you just created, double-click on the intersection of the grand totals, as shown in Figure A.4. Figure A.4. Double-click the intersection of grand totals to get your final table.

The final result, shown in here in Figure A.5, is a brand-new table that is structured exactly the way you need it in order to group months. Keep in mind that you will have to do some slight formatting of this table before using it in a pivot table (that is, you will need to change the date format and rename column headers).

Figure A.5. After you format and rename the columns in your final table, you can create a new pivot table from it. This table structure will allow you to group your months by quarter.