• Technology
  • CFO.com | US

Spreadsheets: Filtering a Pivot Table with a Formula

There is really no "good" way to change the filter with a formula. However, there is a quick fix to remedy the situation.


Editor’s Note

: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Robert L. wins a copy of Excel Gurus Gone Wild for his question, “How can you change a pivot table filter using a formula?”

This is a tough question. Whether you are using the Report Filter or the new Excel 2010 Slicer feature in a pivot table, there is really no good way to change the filter with a formula. Instead, you could add a formula to the original data set and then use that new field in the filter. Unfortunately, you would have to constantly refresh the pivot table in order to get the new filters in the report.

Adding a Formula to the Original Data Set

As an example, Figure 1 shows a data set with a time field. You would like the pivot table to include values where the time is in the past. You could do this by filtering to where C2<=NOW().

Fig. 1

MrExcel July20 Fig1

 

Rather than try to filter the pivot table with a formula, you could add a field to the data set. Use a heading such as “Include.” The formula would be =C2<=NOW(), as illustrated in Figure 2. Then copy the formula down to all rows.

Fig. 2

MrExcel July20 Fig2

 

When you build the pivot table, move the Include field to the Report Filter (or to a slicer if you are in Excel 2010). Choose True from the filter (see Figure 3).

Fig. 3

MrExcel July20 Fig3

 

Refreshing the Formula; Refreshing the Pivot Table

Pivot tables are fast because they copy a snapshot of the values in the data set into a special area of memory called the pivot table cache. If the values in the worksheet change, those changes are not automatically reflected in the pivot table.

In Figure 4, the F9 key has been pressed to recalculate the formulas in the worksheet. The pivot table still shows the original results using data from before the calculation.

Fig. 4

MrExcel July20 Fig4

 

To include the new values in the pivot table, you have to click the Refresh icon in the PivotTable Tools Options tab (see Figure 5). This will reload the current values in the data set into the pivot table cache.

Fig. 5

MrExcel July20 Fig5

 

The pivot table will now reflect the current values (see Figure 6).

Fig. 6

MrExcel July20 Fig6

 

So, while this process allows for the pivot table to refresh based on a formula, you would have to continuously use a combination of F9 to calculate and then Refresh to update the pivot table.

Bill Jelen is the author of 33 books about Microsoft Excel and host of MrExcel.com. For a chance to win a copy of one of his books, post a question at the Community Center to the right. If Bill selects your question as the topic of a future column, we’ll send you a book as a thank-you. Bill’s
 

next MrExcel Webcast

, on
 
writing macros for streamlining budgeting and corporate accounting functions, is Tuesday, August 16, at 2:00 Eastern Time.

One thought on “Spreadsheets: Filtering a Pivot Table with a Formula

  1. Sooo, basically what i should learn here is: dont use formulas to change the pivot tabel, since it is as impracticable as can be? Is there an actual use for this? in what situation would i have no choice but to use formulas to change it? I mean normally i would just use filters and stuff, wouldn`t i. Forgive my questions, im kinda new to excel, and am just trying to align information i found about pivot tables so far (was on http://www.excel-aid.com/excel-pivot-filtersmodifying-and-filtering-pivotcharts.html about pivot charts and how to manipulate them) with the information given here.

Discuss

Your email address will not be published. Required fields are marked *