Excel Pivot Chart Date Format: Fix the Axis Labels

To change date labels on an Excel PivotChart, select the chart’s date axis, right-click it, choose Format Axis, and set a date format under Number. If that formatting is unavailable or resets after a refresh, format the date field in the PivotTable instead: right-click a date item, choose Field Settings, then Number Format.

Change the axis format directly

These steps apply to Excel for Microsoft 365 on Windows. They work when the chart’s horizontal axis contains actual Excel dates, rather than text that merely looks like dates.

Click the PivotChart, then click one of the date labels along its horizontal axis. Check that the axis labels—not the chart title, plot area, or a single data point—are selected.
Right-click the selected axis and choose Format Axis. Excel opens the Format Axis pane.
Expand Number in the pane. Choose Date to use a built-in date display, or Custom to enter a format such as mmm yyyy for labels like Jan 2026.
If the pane shows Linked to source, decide whether the chart should follow the number format used by the PivotTable. Clear it for a chart-only format when that option is available; leave it selected when the source field’s format should control the labels.

The exact formats offered depend on the dates and regional settings in the workbook. Common choices include m/d/yy for compact numeric dates, d-mmm for labels such as 2-Nov, and mmm yyyy for monthly summaries. If Excel does not accept a format or the labels do not change, use the PivotTable field method below; PivotCharts can take their display format from the underlying field.

Set the date format in the PivotTable field

For many PivotCharts, this is the more reliable fix because the chart is connected to a PivotTable. The date field’s number format can determine how its items appear in both the PivotTable and chart, and setting it on the field is less likely to be lost than formatting individual labels.

Click a date belonging to the relevant row or column field in the PivotTable connected to the chart. If you are unsure which field supplies the axis, click the chart and inspect the Axis (Categories) area in the PivotChart Fields pane.
Right-click the date item in the PivotTable and choose Field Settings.
In the Field Settings dialog, choose Number Format. Select a date category or choose Custom, then select or enter the display you want.
Choose OK to close the number-format dialog, then choose OK again to close Field Settings. Check the chart labels and refresh the PivotTable if needed.

Do not confuse a row or column field with a value field. If the dates are being summarized as values—which is unusual for a date axis—the menu may offer Value Field Settings instead. A useful date axis normally comes from a date field placed in Rows in the PivotTable, or in Axis (Categories) in the PivotChart Fields pane.

Formatting changes how a date is displayed; it does not change the underlying date or the chart’s data. For example, a date may display as Nov 2026 while remaining a complete date value used by the PivotTable.

Choose a useful date display

Pick a format that communicates the level of detail in the chart. A daily chart may need day and month labels, while a chart with one point per month is usually clearer with month and year. Avoid showing a full date on every label when the chart has many points; long labels can overlap or become difficult to scan.

dd-mmm-yyyy displays a full date, such as 02-Nov-2026.
d-mmm displays a shorter date, such as 2-Nov, and works best when the year is clear elsewhere.
mmm yyyy displays a month and year, such as Nov 2026.
yyyy displays only the year, which can suit annual summaries.

Excel format codes are not the same as typed label text. For instance, mmm means an abbreviated month name, while mm means a two-digit month number. A custom date format also does not turn text into a date. If the source column contains entries imported as text, convert or correct the source data before expecting date-specific formatting or grouping to work consistently.

Group dates when the chart needs months or years

Number formatting changes the appearance of labels, but grouping changes which date categories the PivotTable and PivotChart display. If the chart currently has a separate point for every day and you want monthly totals, changing the format to mmm yyyy will not combine those daily points. Group the date field instead.

In the PivotTable, right-click a date and choose Group, or select a date and use PivotTable Analyze > Group Field. In the Grouping dialog, choose an interval such as Months, Quarters, or Years, then confirm. The available choices depend on the field and its data. The connected PivotChart follows the resulting categories.

When grouping by months across multiple years, include Years as well as Months. Otherwise, January from different years can appear under the same January category, which may combine or obscure the time periods you meant to compare. A chart with year and month categories makes the chronology clearer.

Grouping usually requires a consistent date field. Blank cells, errors, or values stored as text can prevent Excel from grouping dates. Correct the source column, refresh the PivotTable, and try again. If the field is already grouped and you need individual dates, right-click a grouped label and choose Ungroup before changing the layout.

Fix common axis-format problems

If the labels still look wrong, identify whether the issue is the date value, its display format, or the PivotTable’s category structure. These checks cover the most common causes:

Labels show numbers such as 45200: Excel may be displaying date serial values with a general or numeric format. Apply a date number format to the axis or, preferably for a PivotChart, to the date field in Field Settings.
The custom format has no effect: Confirm that the selected chart element is the category axis and that the source entries are real dates. Text labels cannot be reformatted as dates by applying a number format.
The format changes back after refresh: Set Number Format in Field Settings for the PivotTable date field. A direct chart-axis format may be controlled by, or reset from, the source field. Refresh the chart and check the result before relying on a chart-only change.
Dates appear out of order: Check that the source column contains dates rather than mixed text and date values. Text dates may sort alphabetically or inconsistently instead of chronologically.
Labels are crowded: Group daily records into months or years when that matches the analysis, shorten the format, or make the chart wider. Changing the format cannot make an overcrowded category axis readable by itself.

After correcting source data, refresh the PivotTable from PivotTable Analyze > Refresh, or use Data > Refresh All when you need to refresh workbook connections as well. A refresh updates the PivotChart from its PivotTable; it does not fix inconsistent source values automatically.

PivotChart date axes versus regular chart axes

A standard chart built from a worksheet range can often use a date axis, where dates are treated as points along a continuous time scale. A PivotChart is tied to its PivotTable’s fields and categories, so the available axis behavior depends on the PivotTable structure. In some PivotCharts, the Date axis option may be unavailable or may not behave like it does on a regular chart.

This distinction matters when dates are missing. A continuous date axis on a standard chart can reserve space for time intervals without records; a PivotChart typically presents the categories supplied by its PivotTable. Grouping and field formatting are usually the right tools for a PivotChart. If you need precise control over a continuous timeline, gaps, or unusual date intervals, consider making a regular chart from a separate summary range instead. That chart will not have the same direct PivotTable filtering and refresh behavior.

Changing a PivotChart to a regular chart is not simply a different date format. It changes how the chart is connected to its data, so filters and refreshes may need to be managed differently. Keep the PivotChart if interactive PivotTable filtering is important; use a regular chart when its axis controls are more important than that direct connection.

Windows, Mac, and Excel for the web

The main Windows route is right-click the chart axis > Format Axis, or right-click a PivotTable date > Field Settings > Number Format. On a Mac, select the axis and use its context menu to open Format Axis; a Control-click or two-finger click can open the context menu if a standard right-click is unavailable. PivotTable field dialogs and ribbon labels may differ slightly between desktop releases, but the field-level number-format setting serves the same purpose.

Excel for the web has chart-formatting and PivotTable features, but some controls or dialogs can differ from the Windows desktop application. If you cannot find Field Settings, date grouping, or the number-format control in the browser, open the workbook in desktop Excel and make the change there. The steps above describe Microsoft 365 for Windows, not a guarantee that every control appears identically in every platform or update.

Frequently Asked Questions

Why does my Excel PivotChart show date serial numbers?
Excel stores dates as numbers and applies a number format to display them as dates. Set a date format under Format Axis > Number, or set Number Format in the PivotTable field’s Field Settings dialog. If the source values are text, correct them first.

How do I show months instead of individual dates?
Right-click a date in the PivotTable and choose Group, or use PivotTable Analyze > Group Field. Select Months. If the source covers more than one year, include Years so January from different years is not treated as one category.

Why does my PivotChart date format change back after refreshing?
The chart may be following the PivotTable field’s number format. Set the format through the date field’s Field Settings > Number Format rather than relying only on a chart-axis override, then refresh and verify the result.

Can I use a custom date format such as mmm yyyy?
Yes. In the axis Number settings or the PivotTable field’s Number Format dialog, choose Custom and enter mmm yyyy. This displays a month abbreviation and year without changing the underlying date.

Why can’t I group the dates in my PivotTable?
Check the source date column for blanks, errors, or dates stored as text, and make sure the values are consistent. Correct the source, refresh the PivotTable, and try grouping again.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.