1-866-245-5224 sales@keystonelearning.com

Excel 2007: Modifying a PivotTable

Excel 2007: Modifying a PivotTable

When we talk about modifying a PivotTable, there’s actually a whole host of things that we can do to change an existing PivotTable. In this lesson, we’re just going to look at a few of those options to give you an idea of the flexibility and power of this feature.

Sorting Data in Column or Row Labels

     
  1. Select a column or row field in a PivotTable report
     
  2.  
  3. Click a column or row label
     
  4.  
  5. Choose Sort Z to Z or Sort Z to A
     

Formatting Cells

     
  1. Select the cells you want to format
     
  2.  
  3. Right-click and choose Format Cells… from the shortcut menu
     
  4.  
  5. Set the formatting in the Format Cells Dialog Box
     
  6.  
  7. Click OK
     

Rearranging PivotTable Fields

To rearrange fields, click the field name in one of the areas, and then select one of the following commands from the Field List Task Pane. 

               

This Command…

Does This…

Move Up

Moves the field up one position

Move Down

Moves the field down one position

Move to Beginning

Moves the field to the beginning of the area

Move to End

Moves the field to the end of the area

Move to Report Filter

Moves the field to the Report Filter area

Move to Row Labels

Moves the field to the Row Labels area

Move to Column Labels

Moves the field to the Column Labels area

Removing a Field from a PivotTable

     
  1. Open the Field List Task Pane
     
  2.  
  3. Drag the field name you want to remove outside the PivotTable Field List Task Pane
     

Adding a Calculated Field

     
  1. Click inside the PivotTable
     
  2.  
  3. Choose the Formulas drop-down arrow in the Tools group on the PivotTable Tools Options Contextual tab
     
  4.  
  5. Choose Calculated Field
     
  6.  
  7. In the Name box, type a name for the field
     
  8.  
  9. In the Formula box, enter the formula for the field
     
  10.  
  11. Click Add
     

Deleting a Calculated Field 

     
  1. Right-click the Calculated Field
     
  2.  
  3. Choose Remove from the shortcut menu