Learn PivotTable from scratch

Uploaded time: September 10, 2021 Difficulty: Primary

Learn PivotTable from scratch

Learn PivotTable from scratch

Tips: you can turn on the volume by clicking

Graphic skills

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.

__13.gif 

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. 

__4.gif


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

__5.gif


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

__6.gif


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.

__7.gif 

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.

__8.gif

 

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. 

__9.gif


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.

__10.gif

 

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.

__11.gif

 

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.

__12.gif

 

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.