Pivot Table Spreadsheet

A Pivot Table in a spreadsheet is used to take information from a larger table of data and present summaries from across the entire range that can provide insightful and meaningful views of the data. Pivot Tables can be used to show various combinations of statistics such as, for example, averages across multiple entry types or categories, total amounts for different labels or types, or summaries of the range of values associated with different rows and / or columns.

Pivot Table Spreadsheet

A spreadsheet consists of data in rows and columns, that can incorporate various formulas to add up totals or perform other calculations such as averages. These formulas can be very complicated and provide many accounting or scientific functions. A spreadsheet is only a flat table of data, and it could have a very large data set that from which it is difficult to extract significant relationships and summaries. A Pivot Table can be used to pose questions of the data, for example asking how many items of the colour red and size ten have been sold in each region, or a summary of all the colours and sizes across the regions.

The Pivot Table can be used to extract all the distinct values in a column or row, for example shirts, trousers, jumpers, socks and hats, would each be a distinct value in a spreadsheet of clothing sales, or colours such as red, blue, yellow, brown, orange and black. A Pivot Table could the be used to extract data from the sales spreadsheet to summarise the number of items sold of each type and colour.

Pivot Table in Excel

To insert and use a Pivot Table in Excel there must first be a sheet of data on which to work and summarise data. The Pivot Table in Excel can be found in a number of places. First in the Data option in the main menu bar, where there is an option for Summarize with PivotTable, which is also duplicated under the Data... Table Tools menu option. There is also the option for PivotTable under the Insert tab which brings up the Create PivotTable wizard. Note that the data series to be worked on needs on have one cell selected and the Pivot Table wizard will automatically select the entire range (this can be altered afterwards). There will also be the option to create a Pivot Table in the existing worksheet or in a new worksheet.

Summarize with PivotTable in Excel

Summarize with PivotTable in Excel

Create PivotTable Wizard in Excel

Once the data range has been verified or changed, and either a new or existing worksheet selected then clicking Ok will open the PivotTable Builder as shown below. This gives options to select which rows and columns provide the labels and which provide the data. The fields can be dragged from one area to the next to change the display and use of the data.

PivotTable Builder

Selecting the i icon next to the field will open the PivotTable Field editor.

PivotTable Field editor

Pivot Table in OpenOffice

In OpenOffice Calc the Pivot Table option can be found in the menu bar under Data... Pivot Table... Create. Note that it will not automatically select the data range, so a selection needs to be made before opening the Pivot Table wizard.

Pivot Table in OpenOffice

Pivot Table in OpenOffice

Pivot Table in Google Sheets

In Google Sheets a Pivot Table can be added by going to the Data menu and selecting Pivot table in the drop down. This will start the Pivot table editor on the right hand side of the screen.

Pivot Table in Google Sheets