BENEFITS OF PIVOT TABLES IN EXCEL HOW TO
BENEFITS OF PIVOT TABLES IN EXCEL UPDATE

Switch to classic mode by placing the pointer on the PivotTable, selecting PivotTable Analyze > Options, selecting the Display tab, and then selecting Classic PivotTable layout.ĭrag a row or column field to a different area. The following illustration shows how to move a column field to the row labels area. Right-click a column field, and then click Move to Rows. Right-click a row field, point to Move, and then click Move To Columns. This operation is also called 'pivoting' a row or column.

When you move a column to a row or a row to a column, you are transposing the vertical or horizontal orientation of the field. You might want to move a column field to the row labels area or a row field to the column labels area to optimize the layout and readability of the PivotTable. Move a column to the row labels area or a row to the column labels area

BENEFITS OF PIVOT TABLES IN EXCEL UPDATE

To keep the current PivotTable column width, clear the Autofit column widths on update check box. To automatically fit the PivotTable columns to the size of the widest text or number value, select the Autofit column widths on update check box. In the PivotTable Options dialog box, on the Layout & Format tab, under Format, do one of the following: On the Analyze or Options tab, in the PivotTable group, click Options. This displays the PivotTable Tools tab on the ribbon. The following illustration shows how to move a row item by dragging. When the pointer becomes a four-headed pointer, drag the item to a new position. Select the row or column label item that you want to move, and then point to the bottom border of the cell. In the PivotTable, right-click the row or column label or the item in a label, point to Move, and then use one of the commands on the Move menu to move the item to another location. To display subtotals below the subtotaled rows, clear the Display subtotals at the top of each group check box. To display subtotals above the subtotaled rows, select the Display subtotals at the top of each group check box. On the Layout & Print tab, under Layout, click Show item labels in outline form, and then do one of the following: Note: If None is selected, subtotals are turned off. You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas. For more information about each setting, click the Help button at the top of the dialog box. Move the field to the Column Labels area.ĭisplay the Field Settings or Value Field Settings dialog boxes. Move the field to the Report Filter area. Move the field to the beginning of the area. Move the field down position in the area. Move the field up one position in the area. To rearrange fields, click the field name in one of the areas, and then select one of the following commands: Use to filter the entire report based on the selected item in the report filter. Use to display fields in the legend of the chart. A column lower in position is nested within another column immediately above it. Use to display fields as columns at the top of the report. Use to display fields as an axis in the chart. A row lower in position is nested within another row immediately above it. Use to display fields as rows on the side of the report. You can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:

You cannot add the same field more than once in a PivotTable that is based on an OLAP data source. If you try to add the same field more than once - for example to the Row Labels and the Column Labels areas in the layout section - the field is automatically removed from the original area and put in the new area.Īnother way to add the same field to the Values area is by using a formula (also called a calculated column) that uses that same field in the formula. You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. However, you can’t move the Values Column label to the Report Filters area. You can even move the Values Column label to the Column Labels area or Row Labels areas. You can use this field to move the field positions up and down within the Values area. When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area.

Benefits of pivot tables in excel