Filter an Excel Pivot Table for Upcoming Dates

In a pivot table, you can add a date field to the Page area, and filter the pivot table, so it shows the results for a specific date.

Sometimes though, you'd like to show dates in a specific range, instead of just a single date. For example, you might like to show orders with ship dates in the future.

• You could manually hide all the past dates in the Ship Date page field, but that might take quite a while if there are lots of dates.
• Another option is to add a field to the source data, and add it to the pivot table, as a filter.

In this hub we'll look at both solutions.

To hide some of the items in a pivot table's page field filter, you can change the field's settings.

1. Double-click on the page field button.
2. In the PivotTable Field dialog box, click on dates in the Hide Items list.
3. Click OK to close the dialog box.
   

For some filtering problems, the best solution is to add a column to the source data.

In this case, we'll add a column named ShipLater, and use a formula to test if the ship date is later than today's date.

1. In the source data table, add a column with the heading ShipLater. In this example, a blank column was inserted to the right of column A.
   
2. In the first data row of the new column, enter a formula that refers to the ShipDate in that row. Our formula is in cell B2, and will check the Ship Date in cell A2, and see if it is greater than today's date.
   
   =A2 > TODAY()
   

1. Select cell B2, that contains the formula.
2. Point to the fill handle, at the bottom right of the cell.
3. When the pointer changes to a small black plus sign, drag down, or double-click, to copy the formula down to the last row of data.
   

Refresh the pivot table, and add the ShipLater field to the page area.

From the ShipLater page field dropdown list, select TRUE.

Remember to refresh the pivot table each day to see the current calculations for the ShipLater field.

To manually refresh the pivot table, right-click on a cell in the pivot table, then click on Refresh Data.

Instead of manually refreshing the pivot table, you can set it to automatically refresh when you open the Excel file.

1. Right-click on a cell in the pivot table
2. Click Table Options
3. In the Data Options section, add a check mark to Refresh on Open
4. Click OK to close the dialog box.
   

For more pivot table tips, please visit my Excel Tips and Tutorials website.