Day: December 3, 2009

Allow Drag-and-Drop in Excel 2007 Pivot Table

In Excel 2003, you could drag fields from the PivotTable Field List onto the pivot table layout on the worksheet.

In Excel 2007, you can only move the fields to the areas in the PivotTable Field List. In the screen shot below, if you try to drag the Promo field onto the pivot table, the cursor shows an X. The drag-and-drop feature doesn’t work.

PivotDrag01

Change the Pivot Table Settings

If you’d like to drag the pivot fields onto the worksheet layout, you can change a setting in the pivot table options.

  1. Right-click a cell in the pivot table, and click PivotTable Options.
  2. On the Display tab, add a check mark to Classic PivotTable Layout.PivotDrag02
  3. Click OK, to close the PivotTable Options dialog box.

Drag the Pivot Table Fields

After you change the setting, the pivot table on the worksheet shows a blue border around each region.

PivotDrag03

You can now drag the pivot fields from the PivotTable Field List onto the worksheet, or drag the fields to a different part of the pivot table.

  1. Point to a field label in the pivot table layout
  2. When the pointer changes to a four-headed arrow, drag the field label to a different area

PivotDrag04

You can use the same technique to drag fields into the pivot table layout from the PivotTable Field List, or drag field labels out of the pivot table layout.

Pivot Table in Tablular Form

When you change the setting to Classic PivotTable layout, the pivot table’s report layout automatically changes to Tabular form.

You can use Tabular form, or Outline form, with the Classic PivotTable layout. If you change to Compact form, the blue borders are still visible when the pivot table is active. However, you won’t be able to drag-and-drop the fields.

Video: Classic Layout

In this video, you’ll see the manual steps required to format a pivot table in Classic Style, so you can drag and drop the fields, right on the worksheet. There are several steps in the process:

  • change subtotal setting
  • change report layout of the pivot table
  • change pivot table style
  • change the pivot table display options
  • change setting to clear old items in Pivot Table
  • format each value field as number format
  • sort each row field alphabetically.

Save Time with a Macro: Near the end of the video, you’ll see how much quicker it is to run the recorded macro, to format the pivot able in seconds, instead of minutes. To get that macro, go to the Classic Layout Pivot Table Format Macro page, on my Contextures site.

___________________

Change Field Names in Pivot Table Source Data

Occasionally, you might need to change a column heading in the pivot table’s source data. For example, you could change a column heading from Qty to Quantity, to make it easier to understand.

Pivot Table Field Name 01

However, when you refresh the pivot table after making the changes, the renamed field will disappear from the pivot table layout. In our example, the Qty field is removed from the layout. It isn’t automatically replaced by the Quantity field, even though it’s in the same column heading cell in the source data.

Pivot Table Field Name 02

If you change the column headings, you’ll have to add the revised field to the pivot table layout again. If you had number formatting in the old pivot field, you’ll have to reapply the number formatting you had previously applied. 

Change Captions Instead

If possible, instead of changing the column headings in the source data, create custom names for the fields in the pivot table instead.

For example, to create a custom name for the Qty field:

  1. In the pivot table, click on the cell that contains the pivot field name, Qty.
  2. Type the custom name, Quantity.
  3. Press the Enter key, to complete the renaming.

Pivot Table Field Name 03

_____________________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

_________________