A PivotTable has a flexible operation interface. We can change the layout of various types of reports only with mouse operation. It can help us better search for information and make decisions.

Take this table as an example. This is the sales data of a certain company over a period of time. If we want to count the total sales of products sold by each branch, we can use the pivot table to count it quickly. This is the completed pivot table.

1. First, select any cell with data in this table.

2. Click the Insert tab in the functional area.

3. Click the PivotTable button. The pop-up Create PivotTable dialog box will automatically select the cell range. We can choose to place the pivot table in a New Worksheet or an Existing Worksheet according to our needs.

4. Here, we select the New worksheet option.

5. Click OK, and a new worksheet will pop up.

In the new worksheet, we can see that the PivotTable pane on the right is divided into two sections, namely Field List and PivotTable Areas. The field displayed in Field List is exactly the title of the first row in the table. PivotTable Areas is divided into four list boxes, ROWS, COLS(columns), VALUES, and FILTERS. We generally put fields containing numeric values in the VALUES list box, and place non-numeric fields in ROWS, COLS, and FILTERS.

We want to count the total sales of products sold by each branch. There are three fields here, namely Branch, Product name, and Total Sales. Branch and Product name are non-numeric fields. Total Sales is a numeric field. First, drag the Product name field into the ROWS list box, then we can see that the names of each sales product are displayed in the pivot table in rows. This is the usage of the ROWS list box.

Then we drag the Total sales field into the VALUES list box. At this point, we can see that the pivot table has automatically calculated the total sales of each product. We can also change the Summarize Values By. Place the cursor on the cell with the value, right-click the cell, and select Value Field Settings in the popup shortcut menu. In the popup dialog box, we can select Count, Average, Max and other value representations. Here, we click Average, then the pivot table will calculate the average total sales of each good. This is the basic usage of the VALUES list box.

If we move the Branch field to the COLS list box, the names of the Branch will be displayed in the pivot table by column. Also, all product fields and all Branch fields will together form a two-dimensional plane. Here, we can see the sales of each product in all branches. Therefore, we can quickly calculate the total sales of each product sold by different branches. In actual work, we can also move the Branch to the ROWS list box, and the Product name field to the COLS list box according to our own needs.

If we want to check the sales performance of sales staff, we can use the Filiter feature to find the needed data in the table. We can drag the Salesperson's name field into the FILTERS list box. Then the drop-down option of Salesperson's name will appear at the top of the pivot table. Click the inverted triangle button of the drop-down option to select the name of the salesperson we want to find. For example, choose Andrew, then we can know Andrew's sales performance. This is how FILTERS is used.

If we want to learn more about the data source in a cell, 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.

PivotTable can create different tables. It is like Lego blocks, and everyone can use Legos to build their favorite Lego model. Similarly, we can drag different fields to the PivotTable Areas to compose different data tables we need. This is the beauty and power of PivotTable.

To be office excel advancers, you could learn how to use WPS Office Spreadsheet online in WPS Academy.

Get Started

How to set the shading colors of the text in WPS Writer

Graphic skills

Trending Tutorials