01 August 2011

Weekly Dynamic: Filtering a Pivot Table with a Formula - Technology

I was playing with a pivot table built from GP data the other day and I wanted to control the months that would show based on a field. For example, I only wanted to see months 1-7 since we’re in July. Typically you could just filter the pivot table to exclude months 8-12 but then every month you have to change the filter and you have to change it for every pivot table.

It just so happens that I had seen this great CFO.com article on Filtering a Pivot Table with a Formula.

This is basically a hack that compares the data to the control field and then uses that result as a true/false filter. Filter the Pivot Table on True/False and you get formula controlled Pivot Tables.

It’s very slick.