Customizing Fields, Rows, and Columns

Customizing Fields, Rows, and Columns

Rearranging fields

In most cases you can rearrange fields by dragging and dropping. You can change the field order, exchange row-area and column-area fields, anmove fields among different areas. 
For example, you could drag a field from the filter area into the row-area, changing your report layout to provide a more detailed breakdown.  Or you could drag a field out of the column-area to the filter-area, removing it from the report layout.  (If you have filtered that field, the filter remains in effect.)

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 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.


  • 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.
To expand or collapse multiple columns or rows:
  • 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.                                                                                                                                                

    • Related Articles

    • Calculated Fields

      Calculated fields (also called "unbound columns" or "unbound fields") are report fields that you create. A calculated field is not part of the report’s original data source but is derived from the data based on an expression (a formula) that you ...
    • Calculated Fields

      Calculated fields (also called "unbound columns" or "unbound fields") are the report fields that you create. A calculated field is not part of the report’s original data source, but can be derived from the data based on an expression (a formula) that ...
    • Working with Columns

      To show or hide columns: Right click on any column header and select   Show Column Chooser.                                                                                                                                                                ...
    • Right-Click Menus

      Right-click a filter-area icon Hide this Field - Removes the field. (To get it back, see the Field List below.) Order - Moves the field to a different location in the same area: Move to Beginning - Moves the field to the beginning of the list. Move ...
    • Sorting and Grouping

      Sorting Pivot reports sort based on the arrangement of your row-area and column-area fields. To change the order in which columns are sorted, simply rearrange your report. For more information on how to do this, see the topic “Rearranging fields” ...