Excel pivot filters:Modifying and filtering PivotCharts

Excel pivot filters:Modifying and filtering PivotCharts

Excel pivot filtesr:YOUR OBJECTIVE

After creating a PivotChart, you can modify it in a number of ways. No less important is the fact that you can filter the data in your PivotChart. These capabilities enhance your ability to graphically present important information in a forceful, yet easy-to-understand fashion.

This learning module “Excel Pivot filters ” shows you how to modify a PivotChart and filter the data that it depicts.

Excel pivot filters:HOW DOES IT WORK?

A PivotChart does not stand alone; it only exists in conjunction with a pivot table. Therefore you can modify a PivotChart by modifying various properties of its associated pivot table. If you select such a pivot table, its PivotTable Field List task pane will appear. Then you can use your mouse to drag-and-drop field labels into different areas, perhaps putting one back in the field list, perhaps bringing another into play. You can do this until you are satisfied that the information you are presenting is depicted effectively. Now observe the changes made in the following example:

Excel pivot filters:There are two basic ways to filter a PivotChart. The first way is to apply a filter directly to the pivot table. Excel automatically changes the associated PivotChart in a corresponding fashion.

The other way is to filter the PivotChart using its field buttons. As you would expect, Excel will automatically change the pivot table correspondingly.

Commands and functions on the PivotChart Tools context tabs provide further options, in particular, those for modifying the PivotChart’s layout, format and design. The most commonly used options are described below:

Excel pivot filters: PivotChart Tools –  Design tab

Type group: Change to another standard chart type; or save a chart that you individually modified as a template, so that you can use it with other charts in the future.

Data group: Select the data you want to include in the chart, or swap the data used on the x-axis with that used on the y-axis.

Chart Layouts group: Here you can quickly change the chart’s overall layout. The layouts offered here allow you to specify where to place the legend, for example, or whether to give your PivotChart a title.

Chart Styles group: You can use the styles in this group to change your chart’s overall visual appearance. In just one step, you can apply a well-balanced selection of color and effects to your PivotChart.

Location group: Use theMove Chart button in this group to change the location of your PivotChart in your workbook.

Excel pivot filters:PivotChart Tools –  Layout tab

Current Selection group: Here you can format individual chart elements to suit your own needs. Afterwards, if you don’t care for the changes you made, you can use the Reset button to revert to the standard style you were using before making such changes.

Insert group: Here you can insert pictures, shapes and text boxes into your chart.

Labels group: Here you can apply chart titles, lable the axes, position the legend, data labels or the data table – and individually format these elements as well.

Axes group: Specify the order in which the axes appear, suppress them, format them individually, etc. Additionally, you can use the Gridlines button to enable, disable or format gridlines.

Background group: Modify the plot area here: this is available when you work with a two-dimensional chart. If you use a 3-D chart, you can modify the chart wall and chart floor, or individually format chart elements, or rotate a chart’s elements in 3-D space.

Analysis group: Here, if you are using a two-dimensional chart style, you can add analyzing elements such as trend lines and error bars to your chart.

Properties group: Here you can optionally rename your chart.

Excel pivot filters:PivotChart Tools: Format tab

  • Current Selection group: Here you can format individual chart elements, however you like; or click Reset to revert to the standard style.
  • Shape Styles group: From the selection catalog, you can choose pre-defined shapes, lines or fill styles for a selected chart shape or line; or use the buttons to individually fill a selected shape, format an outline, or apply a visual effect to the selected shape.
  • WordArt Styles group: Here you can add visual style to selected text in your chart; or individually apply – fill colors, outline colors and styles, or visual effects – to selected chart text.
  • Arrange group: Rearrange – visability, order and/or appearance – of your chart’s elements here.
  • Size group: Specify your chart’s exact width and height.

Excel pivot filters:PivotChart Tools: Analyze tab

Active Field group: Use the commands in this group to expand or collapse groupings in PivotChart areas.

Data group: If the data in your source table has changed, use the Refresh button in this group to refresh the PivotChart. Using the Clear button, you can either delete the entire chart or just clear any filters you may have applied.

Use the Insert Slicer button to selectively insert slicers; these allow you to selectively filter PivotChart data with the contents of individual fields.

Show/Hide group: Hide or unhide the PivotTable Field List and control the appearance of the field buttons in the PivotChart.