How to Automatically Filter the Past 30 Days in an Excel Pivot Table

Here's an example of a Pivot Table, and I want to automatically filter the past 30 days whenever I update the raw data and refresh it.

So here's the sample raw data of stores sales for the above Pivot Table from May 2024 until June 2024.

To make it filter the past 30 days data in the Pivot Table, this is how I do it:

At the raw data:

Add an additional column to check on whether the date is within the past 30days period.

The Formula goes like this:

=AND([@Date]>=(TODAY()-30),[@Date]<TODAY())

*Note that "[@Date]" is my date column header, remember to replace it with your date column header.

The formula would output a TRUE, FALSE column like below. If the date is within the past 30 days period, then it will be TRUE and vice versa.

At the Pivot Table, add a slicer or filter for the "Past 30 days" column.

Filter the "Past 30 days" to be TRUE, then it will only filter the past 30 days every time the raw data is updated and refresh!

Of course, this method is not restricted to past 30 days period, you can always customise the solution to your own situation.

Also, I have attached the sample raw data and Pivot Table for your reference, in case you want to see how I did it.

Finally, hope this simple article can be helpful to you and have fun with the data you're working on~

Recent Posts