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.
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
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.
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.
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
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.
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
There are various tools you can use to format your chart. Just spend some time creating your own.
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.
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!