Here are have the chocolate sales data we looked at earlier, and a basic pivot table and chart set up on a separate sheet. When you create a pivot chart, you’ll see something called “field buttons” sitting on top of the chart area. In the upper left, you’ll see buttons for value fields, which correspond to values being plotted in the chart. You’ll also see a button to filter axis or category values. If you have more than one data series - here I’ll add the Category field - you’ll have a button to filter the chart data series. Notice all filters affect both the pivot chart and pivot table. Field buttons can be toggled on and off from the Analyze tab of the PivotChart Tools menu. Many people like to turn them these off to keep the charts uncluttered. With buttons disabled, you can still control a chart by working directly with the pivot table. When adding value fields to a pivot table, you’ll want to use value field settings to set number formats when possible. For example, notice this chart is using the general number format. That’s because I haven’t set a format in the pivot table. To set number formatting, right-click and select Value field settings, then click Number Format. Now both the pivot table and the pivot chart pick up the currency format, and this formatting will stick even if the chart changes. If you want to change the chart type, follow the standard process with charts. Just right click and select Change Chart type. To filter data in the chart, when field buttons are hidden, use the filter menus in the pivot table normally. You can apply a report filter to filter all data that flows through the pivot table. This acts like a global filter. To sort a row or column by value, you can right-click in the value field, and sort normally. You can also right-click in the label field and use More Sort Options. The pivot table filter will display a small arrow icon when a field has been sorted. Keep in mind you can also drag fields around for a custom sort order. Another way to manually sort is to begin typing labels where you want them. The The pivot table will recognize the label, and then rearrange itself automatically.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.