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.
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
In this method, I’ll show you how to Filter Date Range with Filter Checkbox.
- 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.
Read More: How to Filter Date Range in Pivot Table with Excel VBA
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.
- 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.
Read More: How to Filter Date Range in Excel (5 Easy Methods)
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.
- 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.
Read More: VBA to Pivot Table Filter Between Two Dates in Excel
- How to SUMIF between Two Dates and with Another Criteria (7 Ways)
- Calculate Average If within Date Range in Excel (3 Ways)
- How to Do SUMIF Date Range Month in Excel (9 Ways)
- Excel SUMIF with a Date Range in Month & Year (4 Examples)
- How to Filter Last 30 Days of Date in Excel (5 Easy Ways)
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.
- 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.
Read More: Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)
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.
- 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.
Read More: Excel Formula to Add Date Range (11 Quick Methods)
Here I have provided a dataset for you. Make your own Pivot Table with the dataset & apply different Date Filters.
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 SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)
- VLOOKUP Date Range and Return Value in Excel (4 Suitable Methods)
- How to Use IF Formula for Date Range in Excel (6 Methods)
- Filter Dates by Month and Year in Excel (4 Easy Methods)
- How to Use Custom Date Filter in Excel (5 Easy Ways)