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.

excel pivot table filter date range


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.

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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

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.

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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

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

excel pivot table filter date range

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.

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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

  • 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


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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

  • 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.

excel pivot table filter date range

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.

Steps:

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

excel pivot table filter date range

  • 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)


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.


Related Articles

Asif Khan Pranto

Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo