WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free Download

Windows • MacOS • Linux • iOS • Android

banner

Learn PivotTable from scratch

Uploaded time: September 10, 2021 Difficulty Beginner

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.

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 Inserttab in the functional area, and click the PivotTablebutton. In the pop-up Create PivotTabledialog, the whole raw data table is selected as source range by default. You can also select other data sources manually.

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

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.

__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 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.

__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.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.