Figure 819 This doesn’t look like a pivot table, but harnesses the power.

Additional Details: Cell P1 in the report is a date that I type manually each month. Formulas in row 4 use that date to show “Actual” or “Plan” based on the date. =IF(MONTH(DATEVALUE(E3&" 1, 2014"))<=MO NTH($P$1),"Actual","Plan").

MANUALLY RE-SEQUENCE THE ORDER OF DATA IN A PIVOT TABLE

Problem: By default, a pivot table organizes data alphabetically. For the Region field, this means the data is organized with Central first, East second, and West third. My manager wants the regions to appear in the order East, Central, West. After unsuccessfully lobbying to have the Central region renamed Middle,

I need to find a way to have my table sequenced with the East region first.

Figure 820 Central, East, West is the alphabetical sequence.

Strategy: It is amazing that this trick works. Try it: 1. Select cell B4 in the pivot table.

2. In cell B4, type the word East.

Figure 821 Go to the Central cell and type a new heading.

3. When you press Enter, Excel senses what you are trying to do. All the data from the East region moves to Column B. Excel automatically moves the Central region heading and data to column C.

Click on the cell for year 2018 and click the Expand Field icon on the Analyze tab. The years will expand and show quarters.

Figure 830 Expand years to quarters. Click on Qtr3 and expand again to see months.

If you don’t like the Auto-grouping, you can choose any date field and select Group Field to change the settings. Or, immediately after dragging Date to the pivot table, press Ctrl+Z to undo the grouping. Or - to turn it off permanently, there is a registry setting described at http://mrx.cl/stopautogroup.

GROUP DAILY DATES BY MONTH IN A PIVOT TABLE

Problem: My data set has a date on which each item was shipped. When I produce a pivot table with the date field, it provides sales by day. My plant manager loves sales by day, but everyone else in the company would rather see sales by month.

3

Figure 831 Excel reports daily dates.

Strategy: You can group daily dates to show year, quarter, and month. To do so, you build a pivot table with dates in the Row area of the pivot table:

1. Select a cell that contains a date. Click the Group Field icon in the Analyze tab. Excel displays the

Grouping dialog.

2.The Grouping dialog defaults to selecting months. If your data spans more than one year, it is crucial that you also select years. Select Months and Years. If you don’t choose Years, Excel will group January from one year and January from another year into a single value called January.

The Date field is now replaced with Months. There is a field called Years. Years and Months are shown in the pivot ta- ble, although the pivot table is not showing subtotals for each year.

Figure 832 Select Months and Years.

3.To add the subtotals for the years field, select a years field, then choose Field Settings as shown.

Figure 833 500 rows of daily dates are now 24 rows of months.

4. In the Field Setting dialog, change Subtotals from None to Automatic.

Figure 834 Change the Years field to use Automatic Subtotals.

The result is a report with a subtotal for each year.