Excel Charts - PivotCharts

Pivot charts are used to graphically summarize the data and explore comppcated data.

A Pivot chart shows the data series, categories, and chart axes the same way a standard chart does. Additionally, it also gives you interactive filtering controls right on the chart so that you can quickly analyze a subset of your data.

Pivot charts are useful when you have the data in a huge Pivot table or a lot of complex worksheet data that includes text and numbers. A Pivot chart can help you make sense of this data.

You can create a Pivot chart in the following ways −

From a Pivot table

From a data table as a standalone without Pivot table

From a data table as a standalone without Pivot table with recommended charts

Creating a PivotChart from a PivotTable

To create a Pivot chart from a Pivot table −

Step 1 − Cpck the Pivot table. The Ribbon shows the Pivot table tools – ANALYZE and DESIGN on the Ribbon.

Step 2 − Cpck the ANALYZE tab. The Ribbon converts to the options available in ANALYZE tab.

Step 3 − Cpck PivotChart. An Insert Chart window appears.

Step 4 − Cpck Column and then Clustered Column. Cpck OK. You can see the Pivot chart.

To summarize the data as you want, you can cpck any interactive control and then pick the sort or filtering options you want.

Step 5 − Cpck Region Filter Control. A search box appears with the pst of all the regions.

Step 6 − Cpck Select Multiple Items. Check Boxes appear for the pst of all the regions.

Step 7 − Select the East and South check boxes. Cpck OK.

Step 8 − Cpck the Chart Elements icon.

Step 9 − Cpck Bottom from the options under the Legend option.

Step 10 − Now cpck the Month Filter control and select January. Cpck OK.

The Pivot chart is displayed with the filtered data.

As in the case of normal charts, you can use the chart elements and the chart filters that appear at the right-top corner of the pivot chart to format the pivot chart to make it more presentable.

You have already seen how we changed the position of legend in the above given steps. Now, we will use chart styles to make the Pivot chart much more presentable.

Step 1 − Cpck the Chart Styles icon.

Step 2 − Under the STYLE option, choose Style 2.

Style 2 has data labels above the columns that makes the Pivot chart more readable.

Creating a PivotChart from the Data Table as a Standalone PivotChart

You can create a Pivot chart without creating a Pivot table first.

Step 1 − Select the data table.

Step 2 − On the Insert tab, in the Charts group, cpck PivotChart on the Ribbon.

A Create PivotChart window appears.

Step 3 − Select the Table/Range

Step 4 − Select the location where you want the Pivot chart to be placed. You can choose a cell on the existing worksheet itself or on a new worksheet. Cpck OK.

An empty Pivot chart and an empty Pivot table appear along with the Pivot chart field pst to build the Pivot chart.

Step 5 − Choose the fields to be added to the Pivot chart.

Step 6 − Arrange the fields by dragging them into FILTERS, LEGEND (SERIES), AXIS (CATEGORIES) and VALUES.

Step 7 − Use the Filter Controls on the Pivot chart to select the data to be placed on the Pivot chart. Excel will automatically create a coupled Pivot table.

Recommended Pivot Charts

You can create a Pivot chart that is recommended for your data without first creating a Pivot table. Just as in the case of normal charts, Excel provides Recommended Pivot charts so that to quickly decide on the type of PivotChart that suits your data.

Step 1 − Select the data table.

Step 2 − On the Insert tab, in the Charts group, cpck Recommended Charts.

An Insert Chart window appears with two tabs Recommended charts and All charts.

Step 3 − Cpck the Recommended Charts tab.

Charts with the PivotChart icon in the top right corner are Pivot charts.

Step 4 − Cpck a Pivot chart. The preview appears on the right side.

Step 5 − Cpck OK once you find the Pivot chart you want.

Your standalone Pivot chart for your data is displayed. Excel will automatically create a coupled Pivot table.