Show
Close Pivot tables allow you to group and arrange data of large data sets to get summarized information. In the Spreadsheet Editor you can reorganize data in many different ways to display only the necessary information and focus on important aspects. Create a new pivot tableTo create a pivot table,
An empty pivot table will be inserted in the selected location. The Pivot table settings tab on the right sidebar will be opened. You can hide or display this tab by clicking the icon. Select fields to displayThe Select Fields section contains the fields named according to the column headers in your source data set. Each field contains values from the corresponding column of the source table. The following four sections are available below: Filters, Columns, Rows, and Values. Check the fields you want to display in the pivot table. When you check a field, it will be added to one of the available sections on the right sidebar depending on the data type and will be displayed in the pivot table. Fields containing text values will be added to the Rows section; fields containing numeric values will be added to the Values section. You can simply drag fields to the necessary section as well as drag the fields between sections to quickly reorganize your pivot table. To remove a field from the current section, drag it out of this section. In order to add a field to the necessary section, it's also possible to click the black arrow to the right of a field in the Select Fields section and choose the necessary option from the menu: Add to Filters, Add to Rows, Add to Columns, Add to Values. Below you can see some examples of using the Filters, Columns, Rows, and Values sections.
Rearrange fields and adjust their propertiesOnce the fields are added to the necessary sections, you can manage them to change the layout and format of the pivot table. Click the black arrow to the right of a field within the Filters, Columns, Rows, or Values sections to access the field context menu. It allows you to:
The Filters, Columns, and Rows field settings look similarly: The Layout tab contains the following options:
The Subtotals tab allows you to choose Functions for Subtotals. Check the necessary functions in the list: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp. Values field settings
Group and ungroup dataData in pivot tables can be grouped according to custom requirements. Grouping is available for dates and basic numbers. Grouping datesTo group dates, create a pivot table incorporating a set of needed dates. Right click any cell in a pivot table with a date, choose the Group option in the pop-up menu, and set the needed parameters in the opened window.
Grouping numbersTo group numbers, create a pivot table incorporating a set of needed numbers. Right click any cell in a pivot table with a number, choose the Group option in the pop-up menu, and set the needed parameters in the opened window.
Ungrouping dataTo ungroup previously grouped data,
Change the appearance of pivot tablesYou can use options available on the top toolbar to adjust the way your pivot table is displayed. These options are applied to the entire pivot table. Select at least one cell within the pivot table with the mouse to activate the editing tools on the top toolbar.
The Select button allows you to select the entire pivot table. If you change the data in your source data set, select the pivot table and click the Refresh button to update the pivot table. Change the style of pivot tablesYou can change the appearance of pivot tables in a spreadsheet using the style editing tools available on the top toolbar. Select at least one cell within the pivot table with the mouse to activate the editing tools on the top toolbar. The rows and columns options allow you to emphasize certain rows/columns applying specific formatting to them, or highlight different rows/columns with different background colors to clearly distinguish them. The following options are available:
The template list allows you to choose one of the predefined pivot table styles. Each template combines certain formatting parameters, such as a background color, border style, row/column banding, etc. Depending on the options checked for rows and columns, the templates set will be displayed differently. For example, if you've checked the Row Headers and Banded Columns options, the displayed templates list will include only templates with the row headers highlighted and banded columns enabled. Filter, sort and add slicers in pivot tablesYou can filter pivot tables by labels or values and use the additional sort parameters. FilteringClick the drop-down arrow in the Row Labels or Column Labels of the pivot table. The Filter option list will open: Adjust the filter parameters. You can proceed in one of the following ways: select the data to display or filter the data by certain criteria.
The Filter button will appear in the Row Labels or Column Labels of the pivot table. It means that the filter is applied. SortingYou can sort your pivot table data using the sort options. Click the drop-down arrow in the Row Labels or Column Labels of the pivot table and then select Sort Lowest to Highest or Sort Highest to Lowest option from the submenu. The More Sort Options option allows you to open the Sort window where you can select the necessary sorting order - Ascending or Descending - and then select a certain field you want to sort. Adding slicersYou can add slicers to filter data easier by displaying only what is needed. To learn more about slicers, please read the guide on creating slicers. Adjust pivot table advanced settingsTo change the advanced settings of the pivot table, use the Show advanced settings link on the right sidebar. The 'Pivot Table - Advanced Settings' window will open: The Name and Layout tab allows you to change the pivot table common properties.
The Data Source tab allows you to change the data you wish to use to create the pivot table. Check the selected Data Range and modify it, if necessary. To do that, click the icon. In the Select Data Range window, enter the necessary data range in the following format: Sheet1!$A$1:$E$10. You can also select the necessary cell range in the sheet using the mouse. When ready, click OK. The Alternative Text tab allows specifying the Title and the Description which will be read to people with vision or cognitive impairments to help them better understand what information the pivot table contains. Delete a pivot tableTo delete a pivot table,
Return to previous page What is a fields list on a pivot table?The PivotTable Field List pane allows end-users to organize the structure of a pivot table and populate it with data.
Which pivot table area includes the detailed data that makes up the pivot table?On the right side of the worksheet, a PivotTable Fields task pane is open. In it are four areas (Filters, Columns, Rows, and Values) where various field names can be placed to create a PivotTable. The task pane also includes a checklist area of the fields from which to choose from the data.
Where does Excel list the fields available for a PivotTable?The Field List should appear when you click anywhere in the PivotTable. If you click inside the PivotTable but don't see the Field List, open it by clicking anywhere in the PivotTable. Then, show the PivotTable Tools on the ribbon and click Analyze> Field List.
Is a graphical data representation of data displayed in a pivot table?PivotCharts provide graphical representations of the data in their associated PivotTables. PivotCharts are also interactive. When you create a PivotChart, the PivotChart Filter Pane appears.
|