How to Use Pivot Table to Filter Date Range in Excel (5 Ways)

In this article, we will learn about Excel Pivot Table Filter Date Range. Pivot Table is an amazing tool in Excel to summarize our data in a few seconds & in Pivot Table we can Filter Date to see the result for specific Dates or Range of Dates.

Suppose we have a dataset of a company’s sales having Delivery Date, Region, Sales Person, Product Category, Product & Sales Amount respectively in Column A, B, C, D, E, F & G.

Table of Contents hide

Download Practice Workbook

5 Ways to Filter Date Range in Pivot Table in Excel

Method 1. Filter Date Range in Pivot Table with Check Boxes

Method 2. Using Pivot Table to Filter Date with Specific Range in Excel

Method 3. Inserting Pivot Table to Filter Date with Dynamic Range

Method 4. Filter Date Range in Pivot Table with Slicers

Method 5. Employing Pivot Table to Filter Date Range with Timelines in Excel

Practice Worksheet

Conclusion

Download Practice Workbook

Pivot Table Filter Date Range.xlsx

5 Ways to Filter Date Range in Pivot Table in Excel

Method 1. Filter Date Range in Pivot Table with Check Boxes

In this method, I’ll show you how to Filter Date Range with Filter Checkbox.

Steps:

To make a Pivot Table first select any Cell with your data Range. You can’t have any blank Columns or Rows within your dataset.
Then follow Insert tab >> Tables >> Pivot Table.

Upon clicking it create Pivot Table dialogue box will open.
Now your Table or Range will be Automatically selected if you have selected it initially. Otherwise, select it using the select button shown with an arrow in the image below.
Then if you want to work in the Existing Worksheet Check it & with the Location button shown in the image below with an arrow select your desired location for Pivot Table on the Existing Worksheet.
If you want to work on a New Worksheet Check the circle & press OK.

Upon pressing OK a new Worksheet will open & click on any Cell of it.
Then Pivot Table Fields dialogue box will open. It will have all Fields from your dataset Column Heading.
It has four Areas namely Filters, Columns, Rows, Values. You can drag any Field to any of to Areas.

To Filter Date drag Delivery Date to FILTERS.
Suppose we want to find out the relationship between Product Type & Region.
To create the table with the relationship mentioned above drag those two to Column & Row or vice versa.
Then I have put the Sales Amount in the Value Area to triangulate it with Product Type & Region.
Upon clicking those we have found our desired Pivot Table in the Top-Left of the Worksheet.

Now to Filter Date Range click on the Drop-Down menu beside Delivery Date.
Then click on any Date that you want to Filter.
To select Multiple Dates Click on the Select Multiple Items then press OK.

At first, uncheck the All box. Then select your desired Dates.
I have selected 01-Jan to 04-JAN.
Then click OK.

Now your Pivot Table will contain only values from 01-Jan to 04-Jan. You can also select discrete Dates just by clicking on them.

Finally, I have removed Gridlines & selected All Borders for my Pivot Table. Here is our desired output.

Method 2. Using Pivot Table to Filter Date with Specific Range in Excel

In this part, we will learn how to Filter a Range of Date with Column Drop-Down.

Steps:

First, create a Pivot Table with the dataset following the same procedures of Method 1.
Now drag the Delivery Date Field to Column. If we want to see its relationship with Sales Person & Sales Amount drag both to Row & Values.
Following above we will have a Pivot Table.

Now to Filter with a Range of Date Click on the Column Drop-Down beside Column Labels.
Then select Date Filters.
To Filter with a Range of Dates select Between.
You can select any other desired Filters like This Month, Last Week, Last Year, etc which are called Dynamic Dates & I have shown them in a different section.

Upon selecting Between the Date Filter dialogue box will open.
Now select the Range of Dates you want to Filter.
Here I have selected Between 01-01-2022 & 28-02-2011.

Now our Pivot Table will show data only with Filtered Range of Dates.

Method 3. Inserting Pivot Table to Filter Date with Dynamic Range

In this method, I will show you how to Filter Data with a Dynamic Range using Row Drop-Down. To create Pivot Table check out Method 1.

Steps:

Here I have selected Delivery Date in Rows & Region in Column & Sales Amount in Values.
This Pivot Table will show us how much the Sales Amount was in each Region per Delivery Date.

Now to find Region Wise Sales Amount for a specific time only select the Row Labels Drop-Down.
Then select the Date Filters.
Then select any desired Dynamic Date.
Here I have selected This Month.
So It will show me the Sales Amount of This Month.

Now after removing Gridlines & selecting All Borders for our data Cells we will get our desired Pivot Table.

Similar Readings

Method 4. Filter Date Range in Pivot Table with Slicers

Now I will demonstrate to you how to Filter Date Range using Slicers.

To create Pivot Table check out Method 1.

Steps:

Here I have created a Pivot Table having Delivery Date in Column Headings & Product Type & Product in Row Headings.

I have input Sales Amounts in Value Area.
You select your desired Field. You can drag multiple Fields in a single Area to make a more detailed Pivot Table.

To Filter Date with Slicers follow Analyze >> Filter >> Insert Slicer.

Then Insert Slicers dialogue box will up. From there select the Field you want to Filter.
I have selected Delivery Date as I want to Filter with Dates.
Then press OK.

Now the Delivery Date box will open up. You can select any Date from here for Filtering.
To select Multiple Dates select the checkbox at Top-Right then select Multiple Dates.
Here I have selected 01-Jan, 04-Feb & 13-Mar for Filtering my Pivot Table.

Now the Pivot Table will show us the data of the above 3 selected Dates & we will have our desired Pivot Table.

Method 5. Employing Pivot Table to Filter Date Range with Timelines in Excel

In this method, we will see how to Filter Date Range with Timelines. To create Pivot Table check out Method 1.

Steps:

First I have created a Pivot Table using Delivery Date in Column Headings, Region as Row Headings & Sales Person as Value.

The Value Area inputs everything as Numeric Value So it counted each Sales Person to be One.

Now follow Analyze >> Filters >> Timeline.

Unlike Slicer, Using Timeline you can Filters Dates So the only option available here is Delivery Date.
Select it in the box.
Then press OK.

Then moving the Blue bar Left & Right select your desired Timeline.
I have selected FEB & MAR.

Now Excel will show us our desired Pivot Table having Sales only from Feb & Mar Timeline.

Practice Worksheet

Here I have provided a dataset for you. Make your own Pivot Table with the dataset & apply different Date Filters.

Conclusion

I hope you have enjoyed reading the article above. Reading this you have learned about Pivot Table Filter Date Range. It will make your Pivot Table more creative & convenient. I Hope It helps you make your task easier. If you have any questions please feel free to leave a comment.

How to Use Pivot Table to Filter Date Range in Excel (5 Ways)

Download Practice Workbook

5 Ways to Filter Date Range in Pivot Table in Excel

Method 1. Filter Date Range in Pivot Table with Check Boxes

Method 2. Using Pivot Table to Filter Date with Specific Range in Excel

Method 3. Inserting Pivot Table to Filter Date with Dynamic Range

Method 4. Filter Date Range in Pivot Table with Slicers

Method 5. Employing Pivot Table to Filter Date Range with Timelines in Excel

Practice Worksheet

Conclusion

Related Articles

Asif Khan Pranto

How to Hide Filter Arrows from Pivot Table in Excel (3 Easy Ways)

How to Clear Pivot Cache in Excel (3 Effective Ways)

How to Join Pivot Tables in Excel (with Easy Steps)

What Is the Use of Pivot Table in Excel (13 Useful Examples)

Leave a reply Cancel reply