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

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

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

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

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

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

Fig. 6

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.