*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.*

Markussaid 03/21/14 04:59amSooo, 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.

Eriksaid 10/01/14 07:50amThis article has helped me, so I am answering about my case to clarify why this could be helpful:

I had thousands of articles with IDs. Sometimes, the IDs were shared for few articles in different product categories. In the pivot table I needed to filter all articles that had IDs falling into one category, even if the article was not in the category itself! This article gave me the idea to add a formula as an additional column, instead of filtering hunderds of ID strings manually (I am not using excel to do anything manually, right?).

So the formula had an output “Yes” if ID of an article was common with an article in a given category. Then I created pivot table filtering only articles that had “yes” in the last column…

Otherwise I would probably have to write a piece of code to feed the pivot table a range of IDs that I wanted filtered… As I don’t know how to do it, this was a perfect workaround. Writing a code would be faster, I admit, but not in my case:-)

Pingback: How To Make A Good Pivot Table | HappyForever168

shaunsaid 03/31/16 09:39amugh, use a macro