Pivot charts let you rapidly analyze large amounts of unsummarized data in different ways. Unlike normal charts, Pivot charts can be used to plot data with hundreds or thousands of rows. For example, on this worksheet, I have order data from a wholesale chocolate company over a period of 2 years, in almost 2000 rows of data. To introduce the idea of a pivot chart, I’ll first create a normal chart. Let’s say I want to summarize sales by state. On the second sheet here, I’ve built a small summary table that uses the SUMIFS function to get total sales for each state. You can see the formula sums sales, using values from column B as criteria. Now if I insert a column chart using this data, the data used to plot the chart comes directly from the cells selected as source data. [Insert chart] If I temporarily change a value in the data range, the chart updates immediately. Now let me create a pivot chart that shows the same summary. I’ll start by placing the cursor anywhere in the data, then click Recommended Charts. The brings up the familiar window with chart previews. But notice that most options now show a small icon in the upper right. This icon represents a pivot chart. We have options for sales by state, quantity by state, quantity by city, and so on. When I select sales by state, Excel creates a pivot table and inserts a chart in one step. Notice this chart is the same as the one I created manually with the SUMIFS function. But this time I didn’t use any formulas. All the calculations are handled by the pivot table. The key thing to understand when you create a Pivot chart, is the data used to plot the chart comes from a pivot table. Initially, this may seem complex to you, but it provides a lot of really significant advantages, since Pivot Tables are flexible and powerful reporting engines. For example, I can easily sort the states by sales. Then I can apply a filter to show only the top 5 states by sales. With a little more work, I can add the date field, and break out sales by year as well. All of this is done by manipulating the pivot chart or pivot table, which both stay in sync with each other. There’s no need to enter or update formulas.
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.
