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, Salesperson, Product Category, Product and Sales Amount respectively in Columns A, B, C, D, E, F & G.
1. Using Check Boxes to Filter Date Range in Excel Pivot Table
In this method, I’ll show you how to filter the 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 PivotTable 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 PivotTable 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 the 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 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 Select Multiple Items then press OK.
- First, uncheck all boxes. 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.
2. Using Excel Pivot Table to Filter Date with Specific Range
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 as Method 1.
- Now drag the Delivery Date field to Column. If we want to see its relationship with Salesperson & Sales Amount drag both to Row & Values.
- Following above we will have a Pivot Table.
- Now, to filter with a range of dates 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 is between, 01-01-2022 & 28-02-2011.
- Now, our Pivot Table will show data only with the filtered range of dates.
3. Using Date Filters Within Excel Pivot Table
In this method, I will show you how to filter dates 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 the region-wise Sales Amount for a specific time only select the Row Labels Drop-Down.
- Then select the Date Filters.
- Select any desired Dynamic Date.
- Here I have selected This Month.
- So, it will show me the Sales Amount for This Month.
- After removing gridlines & selecting all borders for our data cells, we will get our desired Pivot Table.
Read More: Excel Pivot Table Date Filter Not Working
4. Filtering Date Range in Excel Pivot Table with Slicers
Now, I will demonstrate to you how to filter date range using Slicers.
To create a 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 Values 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 show 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.
5. Using Pivot Table to Filter Date Range with Timelines in Excel
In this method, we will see how to filter date ranges with Timelines. To create a Pivot Table, check out Method 1.
Steps:
- First, I have created a Pivot Table using Delivery Date in Column Headings, Region as Row Headings & Salesperson as Value.
- The Values area inputs everything as a numeric value. So, it counted each Salesperson to be One.
- Now follow Analyze >> Filters >> Timeline.
- Unlike Slicer, using Timeline you can filter 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: How to Create a Timeline in Excel to Filter Pivot Table
Download Practice Workbook
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.
