How to Create a Pivot Table to Analyze Data in WPS Spreadsheets

How to Create a Pivot Table to Analyze Data in WPS Spreadsheets

February 25, 2019

Pivot table is a valuable tool for displaying the results of data that has been analyzed and summarized. Do you know how to use WPS Spreadsheets to create a pivot table? In this tutorial, we will guide you through the steps. You may follow along and practice making one yourself.

Below is an example using the sample data for a three-day laptops sales. We will show you how to summarize the numbers and profits of the whole data using the Pivot Table.


data

Step1: Click PivotTable under the Insert tab

In order to create a pivot table, select the target place you want to insert your pivot table and then go to the Insert tab and then click PivotTable.

PivotTable Tab

Step2: Select a Data Range

After clicking PivotTable, a Create PivotTable window will pop up. Then, drag your mouse to select the data range from which you want to make your first pivot table.

Data Range

Step3: Make your First Pivot Report

The WPS Spreadsheets pivot table UI is very intuitive. For starts, let's make a brief introduction: as shown below, the worksheet is divided into three sections:
  • Spreadsheet Body: Display the data and pivot report.
  • Field List: Display all the fields of your selected data.
  • PivotTable Areas: Based on your needs of data analysis, drag the fields in the field list among the areas below.
  • Create PivotTable

    In this analysis, since we need the summary of prices and numbers for each type of laptop. Then, we drag Products to ROWS, and No. of products as well as Profits to VALUES. A summarized pivot table is shown on the left.

    Step4: Generate a Pivot Chart for a Clear Visualization

    A pivot chart is the visual representation of a pivot table in Spreadsheets. Go to Options tab and select PivotChart.

    PivotChart

    Step5: Customize Pivot Chart

    An "Insert Chart" window will pop up. Since there is a large difference of the two scales, it's better to click Combo tab to make customization.

    In this case, select "Clustered Column" for Sum of Profits and "Line" for Sum of No. of products under Chart Type. Check the Secondary Axis for No. of products to create another scale.

    Cutom Combination

    Step6: Format the Pivot Chart

    A Pivot Chart is generated. Since the scale for profits is thousands, it is necessay to format the scale. So, choose Formatting and click AXIS under AXIS OPTIONS tab. Under Number, select Number category and change Decimal places to 0.

    There are various tools you can use to format your chart. Just spend some time creating your own.

    Format

    Beautify and Change the Style of Pivot Chart

    Did you notice there are three buttons in the right side of the chart? Click on the top one to quick lay out your chart. Here, we select the first one to add and edit a title in the chart.

    Title added

    Congratulations! You just finished your first Pivot Table and Chart. Not hard, right? Just play around and practice more to be an expert in WPS Spreadsheets!

    About Author

    Marketing team in the WPS office located in Mountain View