Timelines were added in Excel 2013.Ĭlick inside the PivotTable, click the Analyze tab and click the Insert Timeline icon. It allows you to filter by a field and report on it. The Timeline filter overcomes this problem. A PivotTable won’t let you filter by a field and report on that field. We need to filter by date and display the last 12 months. We want to do a 12-month chart, but there are 13 months displayed because the dataset has 13 months’ worth of data. There are a number of issues we need to address with the chart. Note that the PivotTable has subtotals and a grand total, but the line chart ignores them. They don’t have the same ribbon tabs and there are limited options to change them. PivotCharts are different to normal Excel charts. Click the Switch Row/Column button and click OK.
If the chart doesn’t look like the one in Figure 2, then right-click the PivotChart and choose Select Data. Hold the Ctrl key down.Ĭlick inside the report, click the Analyze ribbon tab, then click the PivotChart icon and choose the first line chart and click OK. Right-click one of the dates and choose Group. If you are using an older version of Excel, you will need to group the Dates field by month and year. If you are using the latest version of Excel, it will automatically group the dates, and you may need to remove the Quarters field from the rows section. Click OK and use the mouse to drag and drop the field names from the top section of the PivotTable fields dialog to the four bottom sections as shown in Figure 2. Click in the table in Figure 1, click the Insert tab and click the PivotTable icon (far left). Once we have created the first PivotTable and PivotChart, we can copy the sheet and adjust it slightly to create the second and third charts. The layout of all three reports will be the same. We must create three separate PivotTable reports, one each for volume, value and margin. I covered formatted tables in my August 2018 article. This table has been defined using the Format As Table icon on the Home ribbon. The dataset columns are shown in Figure 1. All three charts will have a rolling 12-month view of volume, value and margin. The report will have three separate charts. Let’s assume we have a sales transaction dataset and we want to distribute a file to users and allow them to filter by State and Customer Category. The advantage of using PivotCharts is the ability to use Slicers and Timelines as filter interfaces.
Using a filter on either will affect both. Whatever is displayed on the PivotTable is reflected on the PivotChart. There is a one-for-one relationship between a PivotChart and a PivotTable. You must have a PivotTable to have a PivotChart. A PivotChart is a chart based on a PivotTable.