Chart & Visualization Issues

Fix Missing Date Axis Bounds and Units in an Excel PivotChart

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

The user needs a PivotChart to display dates chronologically, include dates without data, and provide date axis bounds and units.

Fix Missing Date Axis Bounds and Units in an Excel PivotChart
Product
Microsoft Excel PivotChart
Device & OS
not provided
Scenario
Charting date-based PivotTable data, including periods with no recorded values.
Observed behavior
Dates are not displayed chronologically, dates without data are omitted, and Bounds and Units are missing from the axis settings. The behavior continues after copying the content to a new workbook.
Before you start

Check that the horizontal axis uses the intended date field and that source dates are real Excel dates rather than text. Identify the date range and interval you want to display, including dates with no data.

Solution 1Recommended

Sort and configure the PivotTable date field

Use the PivotTable settings to improve date order and show available items without data.

PivotCharts have axis limitations, so Date axis, Bounds, and Units controls may not be available even when the source dates are valid. Showing items with no data also depends on which items are available in the PivotTable data and cache.

1
Check the source dates

In the source data, select the date column and use Home > Number Format to inspect its values. Convert dates stored as text to real dates, then right-click the PivotTable and select Refresh.

2
Sort the dates

In the PivotTable, right-click a date label and choose Sort > Sort Oldest to Newest.

3
Group the date field

Right-click a date in the PivotTable, choose Group, and select the intervals you need, such as Days, Months, Quarters, or Years.

4
Show available empty items

Right-click a date field label, choose Field Settings, and on the Layout & Print tab select Show items with no data. Check the PivotTable filters if expected periods still do not appear.

5
Inspect the axis options

Right-click the PivotChart's horizontal axis and choose Format Axis. Under Axis Options, select Date axis if it is available, then check for Bounds and Units.

Sort and configure the PivotTable date field
Dates may still be missing: Show items with no data cannot guarantee a complete calendar if dates are absent from the available PivotTable items. Use a complete date list and a regular chart when every date must appear.
Free Microsoft Office alternative

Create Date-Based Charts in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files, work with date-based data, and create charts in WPS Spreadsheets for an easier transition. It does not change the axis controls in an Excel PivotChart.

Create charts from a worksheet range containing dates and valuesOpen and edit compatible Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface when moving from ExcelWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why are Bounds and Units missing from my PivotChart axis?

PivotCharts have axis limitations, and the horizontal axis may not offer a Date axis or its Bounds and Units controls. Right-click the axis and choose Format Axis to check; if the controls are unavailable, create a regular chart from a separate date and value range.

How do I put PivotChart dates in chronological order?

Make sure the source values are real Excel dates, refresh the PivotTable, then right-click a date label in the PivotTable and choose Sort > Sort Oldest to Newest.

How can I show dates with no data?

In the PivotTable, open the date field's Field Settings and select Show items with no data on the Layout & Print tab. If dates are not available in the PivotTable data or cache, create a complete date list and use it as the source for a regular chart.

Will copying the PivotChart to a new workbook restore the missing controls?

Not necessarily. Copying does not remove PivotChart axis limitations. If the issue appears specific to one workbook, check its source dates and PivotTable cache, or recreate the PivotTable and chart.