Pivot table structure

Product: MyOffice Professional

Application: Spreadsheet Web version

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

The Constructor tab contains the following areas:

•Available fields: This area contains a list of all selected columns in the source table. To define the structure of the pivot table, add fields from the area Available fields to the areas Rows, Columns, Values and Filters.

•Rows: Add fields to this area, the data from which will be the row headers of the pivot table.

•Columns: Add fields to this area, the data from which will be the column headers of the pivot table.

•Values: Add fields to this area, the values of which will be used for calculations.

•Filters: If necessary, add fields to this area that will be used to filter the data in the pivot table.

Add fields

Fields from the Available fields area can be added to the Rows and Values area manually or automatically. If fields are added automatically, they are distributed between areas Rows and Values by data type:

•If the field contains text values, it is moved to the Rows area.

•If the field contains numeric values, it is moved to the Values area.

To automatically distribute fields between Row and Values areas, perform the following steps sequentially for each of these fields:

1.In the Available fields area, hold your mouse over the name of the field you want to move to the Rows or Values area.

2.Click the knpk_add_list Add button.

To add fields to Rows and Values area manually, drag them to the appropriate areas while holding down the left mouse button, or perform the following steps for each field in turn:

1.In the Available fields area, hold your mouse over the field you want to add to the Rows or Values area.

2.Click the side_panel_pivot_table_more More button.

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

Adding fields from the Available fields area in other areas is done manually, similar to adding fields to areas Rows and Values .

You can sort the fields using the pivot_sort Sort button.

Change the order of fields, rows, and columns

To change the location of a field relative to other fields in the list, drag it to the desired location in the list. To change the row or column layout, follow these steps:

1.Hold your mouse over a row or column, then click the side_panel_pivot_table_more More button.

2.Select the desired command from the drop-down list:

•Move to beginning: Move a row or column to the beginning of the list.

•Move up: Swap the selected and previous row or column.

•Move down: Swap the selected and subsequent row or column.

•Move to end: Move a row or column to the end of the list.

Rename the field

If needed, you can rename any field in the pivot table.

The field name can contain up to 60 characters.

To rename a field, follow these steps:

1.Move your mouse over the field and click the side_panel_pivot_table_more More button.

2.In the drop-down list, select Rename.

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

Select value calculation functions

For a field that is added to the Value area, you can select a function for the main calculation and a function for the additional calculation.

When adding a field to the Value area, the function for the main calculation is selected automatically as follows:

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

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

When adding a field to the Values area, the function for additional calculation is not used by default. The function can be selected manually as described below.

To select a function for the main calculation via the settings pane, follow these steps:

1.Open the Constructor tab of the pivot table settings pane.

2.In the Values area, in the Calculations field, expand the drop-down list of functions corresponding to this field.

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

To select a function for the main calculation via the context menu, follow these steps:

1.Select a cell or range of cells with calculated values in the pivot table.

2.Right-click the context menu and run the Calculations > required function command.

To select a function for additional calculation via the settings pane, follow these steps:

1.Open the Constructor tab of the pivot table settings pane.

2.In the Values area, in the More calculations field, expand the drop-down list of functions corresponding to this field.

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

To select a function for additional calculation via the context menu, follow these steps:

1.Select a cell or range of cells with calculated values in the pivot table.

2.Right-click the context menu and run the More calculations > required function command.

To disable a function that is used for additional calculation, select the option No calculations when selecting a function as described above.

When updating the pivot table, such additional calculations will be reset.

Delete a field from the pivot table

To remove a field from any area of the pivot table, right-click to bring up the context menu, then execute the Delete command.

To remove a field from all pivot table areas where it is added, hold the cursor over the name of the field and click the dec_scale Delete button.

Was this helpful?

Yes