Learn PivotTable from scratch

Uploaded time: September 10, 2021 Difficulty: Beginner

Learn PivotTable from scratch

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.


Take this raw data sheet as an example, which displays the sales of each branch of a brand. Now we can start to analyze its data._.gif


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.


After knowing about the basic functions of PivotTable from these examples, let's learn how to make it. Here are the steps. For example, we will work out the total sales of each item sold in each branch.First, select any cell with data in this table, click the Insert tab in the functional area, and click the PivotTable button. In the pop-up Create PivotTable dialog, the whole raw data table is selected as source range by default. You can also select other data sources manually.__2.gif

Here we select New worksheet and click OK. On the right of the new worksheet, we can see a PivotTable Pane, which consists of two parts, namely, Field List and PivotTable Areas. __14.gif

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


We generally place the fields containing numeric values into the VALUES area and place non-numeric fields into the ROWS, COLS and FILTERS areas.


In this case, we need three fields: Branch, Item, and Sales. Branch and Item are non-numeric fields, and Sales is a numeric field.


First, drag Branch onto the COLS area, and we can see the branches are displayed in columns. Next, drag Item onto the ROWS area, and the items are displayed in rows. Lastly, drag Sales onto the VALUES area, and the total sales of each item sold in each branch are calculated automatically.


Is there a difference between placing a field in the ROWS area and the COLS area?


Let's swap the positions of these two fields and see what happens. Now we drag Item onto the COLS area and Branch onto the ROWS area. 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 the ROWS area or COLS area 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 By in the shortcut menu. We can select Count, Average, Max and 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 As to set the way values are shown, or click % of Grand Total to 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 FILTERS function. For example, we drag Sales quarter on the FILTERS area. Then a Sales quarter drop-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.