One of the quirks of pivot tables is that they may hold on to items that have been previously removed from the source data, even after refreshing the data. You may see these deleted “ghost” items when filtering a pivot table. In the example shown, the source data originally contained three colors: red, blue, and green. At some point after the pivot table was created, the color Green disappeared from the source data. However, “Green” still appears when filtering by color in the pivot table.

Remove deleted items from a pivot table

In Excel 2019, and Excel 365 , you can remove deleted items by changing a pivot table setting:

  1. Right-click the pivot, select PivotTable Options
  2. Switch to the Data tab
  3. Under “Retain items deleted from the data source”, select None:
  4. Click OK to exit
  5. Refresh the data
  6. Check the filter drop-down:

Set option for all new pivot tables

To change this setting for all new pivot tables:

  1. File > Options
  2. Data > Data options > Edit Default Layout
  3. Pivot Table Options button > Data
  4. Set options as above.

Older Excel versions

Older versions of Excel don’t have the setting shown above. To remove deleted items, you’ll need to use a macro. Debra Dalgleish has a detailed explanation here .

Pivot tables have a built-in feature to calculate running totals. In the example shown, a pivot table is used group data by month and show both the monthly total and running total over a 6-month period.

Fields

The source data contains three fields: Date , Sales , and Color . Only two fields are used to create the pivot table: Date and Sales .

Field list for running total pivot table - 1

The Date field has been added as a Row field, then grouped by Months:

Date grouping for running total pivot table - 2

The Sales field has been added twice as a Value field. The first instance is a simple sum, and has been renamed “Total”:

Total field settings - 3

The second instance is renamed “Running” and set to calculate a running total based on the Date field:

Running total field settings - 4

Helper column alternative

This example uses automatic date grouping. As an alternative, you can add a helper column to the source data, and use a formula to extract the month name . Then add the Month field to the pivot table directly.

Steps to make this pivot table

  1. Create a pivot table
  2. Add Date field to Rows area, group by Months
  3. Add Sales field Values area Rename to “Total” Summarize by Sum
  4. Add Sales field Values area Rename to “Running” Show value as running total Set base field to Date