Use pivot chart to create a dynamic chart

Uploaded time: 2021-09-10 Difficulty: Primary

Use pivot chart to create a dynamic chart

Use pivot chart to create a dynamic chart

Graphic skills

A pivot chart is a graphical representation of a pivot table, which can clearly and intuitively visualize the data. Suppose we now want to create a pivot chart of the total sales of each branch in the fourth quarter. How can we do that?

Take this table as an example. Select any cell in the data area, click the Insert tab, then the PivotChart button. Now select New Worksheet in the popup dialog box, and click OK. After that, we can create an empty PivotTable and an empty PivotChart.


In WPS Spreadsheet, if we use data to create a PivotChart, we will also create a PivotTable. By creating a PivotChart in the PivotTable, we can display the PivotChart directly.

Select the PivotChart, and then we can see the PivotChart pane on the right is divided into two sections, namely Field List and PivotChart 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, LEGEND (SERIES), AXIS (CATEGORY), VALUES, FILTERS. The VALUES list box will generally be placed with fields containing numeric values, and the LEGEND (SERIES), AXIS (CATEGORY), and FILTERS will generally be placed with non- Numerical field.


Now we want to create a data pivot chart of each branch in the fourth quarter. There are three parameters here, namely the fourth quarter, branches and total sales.

1. First, we drag the Total sales field containing the value to the VALUES list box.

2. Then drag the second field Purchase date to the AXIS (CATEGORY) in the list box. AXIS (CATEGORY) is equivalent to the X-axis in the table.

3. Finally, drag the third field Branch to the LEGEND (SERIES) list box. LEGEND (SERIES) is equivalent to the bar in the figure.


After creating the pivot chart, we now only want to check the sales data in the fourth quarter and don’t need the sales on other dates.

1. Click the inverted triangle button next to the Purchase date of the pivot table and select Date Filter.

2. Then select All Date.

3. Finally, select the Fourth Quarter command.

We can also set it in the pivot chart. After setting, the pivot chart will show the bar graph of the total sales of each branch in the fourth quarter.


If we want to know more about the salesperson's sales performance in the fourth quarter, we can drag the Salesperson's name field to the FILTERS list box. Then the Salesperson's name field button will appear at the top left of the pivot chart. For instance, if we want to view the sales performance of salesperson Andrew, just click the field button to select Andrew, then the total sales of Andrew in the fourth quarter will be displayed. This is the usage of FILTERS.


We can also set the layout of the PivotChart.

1. Move the PivotChart to a suitable place and drag the mouse to enlarge it.

2. Then we can see a Chart Elements button on the right side of the PivotChart.

3. Click the button, select the Date Labels option in the popup menu, now the total sales will be displayed above the bar.

4. Then select the Chart Title option to add a title to the pivot chart. Here, we change the title to Total sales of all branches in the fourth quarter.


If you think that the field buttons will affect the appearance of the PivotChart, right-click any field button and select the Hide all Field Buttons on Chart option in the popup shortcut menu. After that, we can see the field buttons in the PivotChart are hidden. If we want to redisplay the field buttons, click any field in the PivotChart Areas on the right and select the Show all Field Buttons on Chart option in the popup menu.


Another powerful function of the PivotChart is that it can be automatically updated as the data source changes. Here is sheet3. Now change the value 385 on October 3 to 1, then we went back to the previous worksheet. In this worksheet, click the refresh button in the Options tab, then we will find that the bar of the second branch on October 3 will change with the change of the data source.


That's the basic introduction on the usage of pivot chart. Did you get it?

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