A Pivot Table is used to summarise, sort, reorganise, group, count, total or average data stored in a table. It allows us to transform columns into rows and rows into columns. It allows grouping by any field (column), and using advanced calculations on them.
Pivot Tables have three different layouts that you can choose from: Compact, Outline, and Tabular Form.
Manually create a PivotTable
Click a cell in the source data or table range. Go to Insert > PivotTable. Excel will display the Create PivotTable dialog with your range or table name selected.
What is a pivot table? A pivot table is a summary of your data, packaged in a chart that lets you report on and explore trends based on your information. Pivot tables are particularly useful if you have long rows or columns that hold values you need to track the sums of and easily compare to one another.
Go to the pivot table editor, and click the Add button next to Rows. Then locate the row you want to show and click on them. Repeat the same process to insert a Column to start seeing your pivot table take shape. You can also select the right Filters and Values to display multiple columns according to your needs.
Add an Additional Row or Column Field
Click any cell in the PivotTable. The PivotTable Fields pane appears. You can also turn on the PivotTable Fields pane by clicking the Field List button on the Analyze tab. Click and drag a field to the Rows or Columns area.
In the PivotTable Fields List, at the top, are the fields we'll use to create the PivotTable. They are the same as the column headings in the source data. At the bottom are the four areas of a PivotTable; the fields can be added to: ROWS, COLUMNS, VALUES, and FILTERS.
You can use a PivotTable to summarize, analyze, explore, and present summary data. PivotCharts complement PivotTables by adding visualizations to the summary data in a PivotTable, and allow you to easily see comparisons, patterns, and trends.
To insert a new pivot table, simply select any cell in your data set and press the shortcut keys ALT + N + V. This will open the Insert PivotTable dialog box, where you can choose where to place the new pivot table.
Add a calculated field
This displays the PivotTable Tools, adding the Analyze and Design tabs. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field. In the Name box, type a name for the field. In the Formula box, enter the formula for the field.
Pivot Table Report Layouts. In Excel, Pivot tables have a defined basic structure, called a Report Layout (Form). In a new installation of Excel, pivot tables are in Compact Layout by default. See how to change to Outline or Tabular layout, and compare the features of each layout type.
Create Second Pivot Table. The easiest way to create a second pivot table, based on the same source data, is to copy and paste the first pivot table. Then, select the cell in row 1, in the column where you want to paste the new pivot table.
An important advantage of a pivot table is that it is easy to use. You can easily summarize data by dragging the columns to different sections of the table. The columns can also be re-arranged with the click of a mouse.
On the Ribbon, under the PivotTable Tools tab, click the Options tab. At the left, click Options, then click Show Report Filter Pages. In the Show Report Filter Pages dialog box, select one of the filters, and click OK. A new worksheet will be added for each* pivot item, named for the pivot item.
On the Data tab, under Tools, click Consolidate. In the Function box, click the function that you want Excel to use to consolidate the data. In each source sheet, select your data, and then click Add.