Go Back

Learn How to Deal with Dates in a Pivot Table

We can customize our pivot table dates to be displayed in different ways. In simple steps, we will explore how to sort, filter, group and format our pivot table dates easily.

Figure 1 – Example of how to deal with pivot dates

Setting up the Data

  • We will set up our Excel data to contain an array of columns and rows
  • Our Dates will be listed in Column A
  • Column B will contain our products
  • Sales will be held in Column C

Figure 2 – Setting up the Data

  • Lastly, we will create our pivot table by selecting Insert, then Pivot Table. After that, we will assign Date and Products to the Rows label as well as the Sales to the Values section

Figure 3 – Pivot Table Fields

Figure 4 – Setting up the Pivot table

How To Group Pivot Table Dates

  • We can group our pivot table date by month, day, quarter, week, and year
  • We will right-click on any date and select Group
  • In the Group dialog, we will find different options. For example, to group by day, we will select Day, enter the Starting and Ending date, and then, click OK.  We can also ungroup data by right-clicking on any date and select ungroup

Figure 5 – How to group pivot table date

How To Sort Pivot Table Dates

We can equally sort our dates from the most recent sales to the oldest and vice versa. To do this, we will right-click on any of the dates, select “sort”, and lastly, click “Newest to Oldest.”

Figure 6 – How to sort pivot table date

Insert a Timeline

  • We can insert a pivot table timeline for filtering our pivot table dates.
  • We will click on the pivot table, select the Analyze tab in the Pivot Table Tools, and click Insert Timeline

Figure 7- Insert Timeline

  • We will select Date and drag the scroll bar to choose the specific period we want

Figure 8 – Insert a timeline

Change Date Formatting In Pivot Table

To change the pivot table date format:

  • We will Ungroup Date
  • We will right-click on any cell in the date field of the pivot table
  • We will select Field Settings, Number format

Figure 9- Field Settings Dialog box

  • We will change to the Date formatting in the format cells window and press OK

Figure 10- Format Cells Dialog box

Figure 11 – How to change pivot table formatting

Insert a Filter For a Specific Date Range

  • We will click the dropdown arrow on the Rows Label heading
  • We will select Field name, then Date filters, and click Between

Figure 12- Using filters

  • In the Between dialog box, we will type the desired start and end dates

Figure 13 – Date filters

Figure 14 – How to insert date filters into the pivot table

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar