Rearranging fields

There are also some menu options that provide alternative ways to make layout changes.

Dragging and Dropping

Drag and drop the field header to a new location, either in the same area or a different area.

• Click and hold the header you want to move.
• Nudge your mouse to see if a translucent copy of the header is visible.                                                                                                                   
  

• Move it to your desired location.
  
  
  
  
  
  

  Arrows will indicate the locations where you can drop the header.
   

Using the right-click menu

Right-click on the field header, select Order and choose where you want to move the item (to the beginning of the list, one space to the left or right, or to the end of the list).                                                                                                                                                    

To hide a field completely, use the “Field List” as described in the next section.

Using the field list

The Field List offers a compact way to move and reorder fields. If you wish to make a lot of changes, the field list can be more efficient than dragging and dropping from the report-area.

The field list is the only way to hide a field completely, and the only way to bring a hidden field back into the report.

• Right-click on any field and select Show Field List.                                                                                                                                                                              
  

  The sections in this form - Row, Column, Data, and Filter areas, plus the Hidden Fields box - correspond to the pivot-report areas discussed in the Introduction. See the Introduction for more information.

• Drag and drop the fields in the Field List dialog box as you wish. You can drag a field between sections, or you can drag within the same section.

Resizing rows and columns

Column widths and row heights can be changed using the mouse. When the mouse cursor is located in a position that allows resizing, the cursor changes to a double-headed arrow.

In the pictures below, the cursor size is exaggerated to make it easier to see.

Double-clicking

Double-clicking To automatically adjust a column’s width based on its contents, double-click the right edge of the column-header. To adjust a row’s height, double-click its bottom edge.

Dragging

• For a column, click and hold on the right edge of the header. For a row, click and hold on the bottom edge.                               
  

• Drag it to your desired width / height and release the mouse.                                                                                                            
  
  
  

Using the Ribbon

Use the Best Fit buttons in the Pivot Report Ribbon to automatically size columns (column-area and data-area values) or rows (row-area values) to the minimum width required to completely display their contents.                                                                            

Note: The Best Fit buttons use a method that’s very fast even with lots of data, and they consider data that may not be currently visible in your results because it won’t fit on your screen. Occasionally this may result in a value still being truncated, and you may have to manually adjust the width by dragging. We think this is an acceptable trade-off for responding quickly.

Note: In the Column Area, only the widths corresponding to the innermost columns can be dragged. (That is, columns that sit immediately on top of the data-area.) Outer column headers cannot be dragged.

Expanding and collapsing columns and rows

If your pivot-report has more than one row-area field and/or more than one column-area field, all of the outer values will have little expand/collapse triangles on them. This lets you easily decide what level of detail you want to see.

In the example shown there are two column-area fields: the year and the quarter; and three row-area fields: Company, VendorID, Class. You can see that the outermost (one) column-area field values have triangles, and that the outer two row-area field values have triangles.

To expand or collapse a single item, either:                                                                                                                                                      

• Click its expand / collapse triangle; (highlighted in the above screenshot);                                                                                     – or –
  
• Right-click the field value, and select an Expand or Collapse items as appropriate – you can expand/collapse just the item you right-clicked, or you can expand/collapse all the values in the field. The choices you see may depend on the current state of your report.

• Right-click one of the field values and choose Expand All or Collapse All.

Controlling the visibility of field headers

Using the Header buttons on the Pivot Report ribbon, you can show or hide various field-name headers (that is, the icons containing the field names).

The screenshot below displays all the field-name headers. The top section is the filter area; below it on the left is the data area with a single field (Balance), and on the right is the column area with two fields (Yrqtr and Year).                                                            

The screenshot below hides all headers.                                                                                                                                                          

You cannot hide row-field headers.

Changing where totals appear

Use the Summary Options section in the Pivot Report’s Ribbon to choose where you want row-area and column-area totals to be displayed.

Column Totals Location

• Far displays grand totals on the right                                                                                                                                                     
  

• Near displays them on the left                                                                                                                                                                
  

Row Totals Location

• Far displays totals at the bottom of the row                                                                                                                                          
  

• Near displays totals at the top of the row                                                                                                                                              
  

• Tree displays the totals in a tree format.