Pivot Table Issues

Hide Empty Periods in an Excel Timeline Slicer

Muhammad TalhaMuhammad Talha Nov 13, 2026 869 views

Question details

The user wants to hide date periods with no PivotTable data from an Excel timeline slicer.

Hide Empty Periods in an Excel Timeline Slicer
Product
Microsoft Excel
Device & OS
not provided
Scenario
Filtering a PivotTable by day, month, quarter, or year with a timeline slicer.
Observed behavior
The timeline displays periods that contain no PivotTable data, unlike a standard slicer that can hide items with no data.
Before you start

Check that your PivotTable has a date field and that you can edit its source data if you want to create a period field. Menu names and slicer options can vary by Excel version and data source.

Solution 1Recommended

Use a standard slicer with a period field

Use this workaround when you can replace the timeline with a list of periods represented in the source data.

Excel timeline slicers do not have a built-in setting to hide all empty periods. A standard slicer can filter a period field instead, but it does not provide the same continuous timeline interface.

1
Add a period column

In the PivotTable source data, add a column named Month. For an Excel table with a Date column, enter =IF([@Date]="","",TEXT([@Date],"yyyy-mm")) to create a month label for each populated date.

2
Refresh the PivotTable

Click the PivotTable and select PivotTable Analyze > Refresh so that the new Month field becomes available.

3
Insert a standard slicer

With the PivotTable selected, choose PivotTable Analyze > Insert Slicer, select Month, and click OK.

4
Check slicer settings

Right-click the new slicer and choose Slicer Settings. If available, select Hide items with no data, then click OK. Use the slicer buttons to filter the PivotTable.

Use a standard slicer with a period field
Availability varies: The Hide items with no data option and its behavior depend on the PivotTable data source and Excel version. The new field represents months present in the source data, rather than a continuous range of calendar months.
Free Microsoft Office alternative

Work with PivotTable spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easier to move existing workbooks. It does not change Excel's timeline slicer behavior.

Open and edit Microsoft Excel XLSX and XLS filesWork with PivotTable data in WPS SpreadsheetsUse a familiar interface for a smoother transition from Microsoft OfficeAccess a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can I hide empty dates directly in an Excel timeline slicer?

No. Excel timeline slicers do not have a built-in setting to hide all periods with no PivotTable data. A standard slicer on a period field is an alternative.

How do I add a slicer for months instead of a timeline?

Add a month field to the source data, refresh the PivotTable through PivotTable Analyze > Refresh, and choose PivotTable Analyze > Insert Slicer. Select the month field and click OK.

Where is the option to hide items with no data in a standard slicer?

Right-click the standard slicer and select Slicer Settings. If your Excel version and data source support it, select Hide items with no data and click OK.

Will a standard slicer work exactly like a timeline?

No. A standard slicer displays selectable items such as month labels, while a timeline provides a continuous date-range interface with day, month, quarter, and year views.