Learn PivotTable from scratch

Uploaded time: September 10, 2021 Difficulty Beginner

A free Office suite fully compatible with Microsoft Office

Free Download

Learn PivotTable from scratch

Learn PivotTable from scratch

PivotTable enables us to create various tables by changing its fields on each area as a kind of interactive table. With PivotTable, we can analyze and process complex data tables quickly.

For example, we can analyze the total sales of each branch,the total sales of each item sold in each branch,the total sales of each item sold in each branch in each sales quarter.



The fields are shown in the Field Listcorrespond to the table headers on the first row of the raw data sheet, and PivotTable Areascontains four areas, namely, FILTERS,COLS,ROWS,and VALUES.


We generally place the fields containing numeric values into the VALUESarea and place non-numeric fields into the ROWS, COLSandFILTERSareas.


In this case, we need three fields: Branch, Item, and Sales. Branchand Itemare non-numeric fields, and Salesis a numeric field.


First, drag Branchonto the COLSarea, and we can see the branches are displayed in columns. Next, drag Itemonto the ROWSarea, and the items are displayed in rows. Lastly, drag Salesonto the VALUESarea, and the total sales of each item sold in each branch are calculated automatically.

Is there a difference between placing a field in the ROWSarea and the COLSarea?

Let's swap the positions of these two fields and see what happens. Now we drag Itemonto the COLSarea and Branchonto the ROWSarea. Then they respectively appear on the corresponding areas in the PivotTable.We can easily find that only the layout of this table is changed but the calculated data remains the same.Therefore, we can choose to place a field in theROWSarea or COLSarea according to individual needs.

In addition to data summation, what other statistical functions do PivotTable have?

Right click a cell with numeric values, and click Summarize Values Byin the shortcut menu. We can select Count, Average, Maxand so on. When we click Max,the PivotTable will work out the Maximum value of the total sales of each item sold in each branch. 


In addition, in the shortcut menu, we can click Show Values Asto set the way values are shown, orclick % of Grand Totalto view the proportion of the total sales of each item sold in each branch.

To view the total sales in different quarters, we can use the FILTERSfunction. For example, we drag Sales quarteron the FILTERSarea. Then a Sales quarterdrop-down button appears on the top of this PivotTable. Click the drop-down button and choose a quarter to view the corresponding sales.

If we want to learn more about the data source, we can double-click the cell with the left mouse button. Then all the data related to the cell will appear in a new worksheet.

In conclusion, the PivotTable in WPS Office meets diverse and detailed needs of analyzing and processing table data, helping us save more time at work.