Customize a pivot table

Product: MyOffice Professional, MyOffice Standard, MyOffice Education

Application: Spreadsheet Desktop version

By default, a pivot table does not contain data because its structure is not defined. To define the structure, use the Constructor tab in the pivot table settings pane.

The Constructor tab includes the following sections:

•Available Fields: This section includes the list of all selected columns of the initial table (the fields). To set the structure of the pivot table, add the fields from the Available Fields section to the Rows, Columns, Values, and Filters sections.

•Rows: Add the fields which will become the headings of the pivot table rows to this section.

•Columns: Add the fields which will become the headings of the pivot table columns to this section.

•Values: Add the fields whose values will be used for calculations to this section.

•Filters: If necessary, add the fields that will be used to filter data in the pivot table to this section. Filters allow you to set the level of detail of the data and change the way the table looks.

Add fields

Fields from the Available Fields section can be added to the Rows and Values sections manually or automatically. If fields are added automatically, they are allocated between the Rows and Values areas according to the data type:

•If a field contains text values, it will be moved to the Rows section.

•If a field contains numeric values, it will be moved to the Values section.

To automatically distribute fields between the Rows and Values sections, follow these steps for each of these fields:

1.In the Available Fields section, hover the cursor over the title of the field which needs to be moved to the Rows or Values sections.

2.Click side_panel_pivot_table_add Add.

To add fields to the Rows and Values sections manually, drag and drop them into the appropriate sections while holding down the left mouse button, or do the following for each field:

1.In the Available Fields section, hover the cursor over the field which needs to be moved to the Rows or Values sections.

2.Click side_panel_pivot_table_more More.

3.In the menu that appears, select the section in which you want to add the field.

Calculated fields created in a third-party application can only be moved to the Values section.

Adding fields from the Available Fields section to other sections is done manually, similar to adding fields to the Rows and Values sections.

Moving a field from one section to another

Moving fields from one section to another is done manually, similar to adding fields from the Available Fields section to the other section in the Constructor.

Change the field order in a section

In any section other than the Available Fields section, you can change the order of the fields in the list. The position of the field in the list determines the position of the corresponding field in the pivot table.

To change the position of a field relative to other fields in the list, drag it to the desired location in the list while holding down the left mouse button, or do the following:

1.Hover the mouse cursor on the field and click side_panel_pivot_table_more More.

2.In the drop-down menu, select the desired command:

•Move to Beginning: Move the field to the beginning of the list.

•Move Up: Swap the selected field and the previous field.

•Move Down: Swap the selected filed and the next field.

•Move to End: Move the field to the end of the list.

The drop-down menu commands depend on the position of the selected field in the list.

Rename a field

You can rename any field in the pivot table if needed.

You may not use formulas or functions as names, or repeat existing field names in the pivot table.

To rename a field, follow the steps below:

1.Hover the mouse cursor on the field and click side_panel_pivot_table_more More.

2.From the drop-down menu, select Rename.

3.In the Rename dialog box, specify the name of the field and click OK.

The name of the field in the Constructor tab will change. When you hover the mouse cursor over a field, a tooltip with its current and original name will be displayed.

Edit the function in the Values section

The function for data calculation for a field added to the Values section is selected automatically.

•If a field contains numeric data, the Sum function is selected.

•If a field contains text data, the Count function is selected.

To use another function for the data in this field, do the following:

1.In the Values section, expand the drop-down list of functions available for this field.

2.Select the desired function from the drop-down list.

For calculated fields created in a third-party application, the Sum function is automatically selected. No other data calculation function is available.

Delete a pivot table field

To delete a field from any section of the pivot table, do one of the following:

•Hover the mouse cursor over the title of this field and click side_panel_pivot_table_delete Remove.

•Hold down the left mouse button and drag the field from the current area to the Available Fields section.

To delete a field from all areas of the pivot table to which it is added, do the following:

1.In the Available Fields section, hover the cursor over the title of the field.

2.Click side_panel_pivot_table_remove Remove from Pivot Table.

Was this helpful?

Yes