Chart & Visualization Issues

Fix Excel Chart Dates Changing to Numbers When Columns Are Hidden

Maira Mehtab Sep 21, 2026 869 views

Question details

The user is experiencing an issue where horizontal axis labels on a chart, originally formatted as year-month, incorrectly display as serial numbers or '1900-01' after grouping, hiding, and subsequently unhiding the source data columns.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Hiding and unhiding grouped columns that serve as the data source for a chart.
Observed behavior: Chart dates revert to incorrect numerical values or default '1900-01' dates upon saving, reopening, and ungrouping the file.

Before you start

Before attempting the fixes below, ensure that your source data cells are strictly formatted as 'Date' rather than 'Text', and verify that 'Show data in hidden rows and columns' is enabled in your chart's Select Data settings.

Solution 1Recommended

Recreate the Corrupted Chart

Often, the chart object itself becomes corrupted after repeatedly grouping, hiding, and unhiding columns. Creating a fresh chart can permanently restore proper axis formatting.

If a chart is repeatedly modified through hiding source columns, the underlying XML formatting metadata for the axis can get corrupted. A new chart reads the raw cell formats accurately from scratch.

Delete the old chart

Select the corrupted chart showing the incorrect 1900-01 dates and press the Delete key.

Select the data range

Highlight the original source data, ensuring the dates are properly formatted in the cells.

Insert a new chart

Navigate to the 'Insert' tab on the ribbon, choose your desired chart type, and place it on the worksheet.

Test the new chart

Group and hide the columns, save the workbook, and reopen it to confirm the date formatting remains intact.

Solution 2

Transform Text Dates Using Power Query (Excel 365)

If your dates are actually stored as text strings, Excel might lose the formatting when columns are hidden. Standardizing the dates via Power Query prevents this.

Create and Manage Charts Seamlessly

Create Stable Charts with WPS Spreadsheet

Avoid chart formatting glitches and corruption by using WPS Spreadsheet. It offers highly compatible, stable charting tools that retain your date formatting perfectly, even when source columns are grouped, hidden, or dynamically updated.

1. Open your data: Launch WPS Spreadsheet and open the workbook containing your dates and values.
2. Insert the chart: Highlight the data range, go to the 'Insert' tab, and select 'Chart' to choose a visual layout.
3. Configure hidden cell settings: Right-click the generated chart, select 'Select Data', and click the 'Hidden and Empty Cells' button.
4. Enable hidden data display: Check the box for 'Show data in hidden rows and columns' and click 'OK'. Your chart will now remain perfectly stable when you hide the source columns.

Fully compatible with Microsoft Excel (.xlsx) file formats and chart types.Automatically retains axis formatting when hiding or unhiding data.Lightweight and free alternative for seamless data visualization.Intuitive chart settings with robust options for handling hidden rows and columns.

microsoft office alternative - wps office

Frequently Asked Questions

Why do Excel dates change to 1900-01 on my chart axis?

Excel stores dates as sequential serial numbers starting from January 1, 1900. If the chart axis loses its 'Date' formatting metadata—often due to hiding columns or file corruption—it reverts to reading a zero or blank value as the system's start date, which is January 1900 (1900-01).

How do I force a chart to show data from hidden columns?

Right-click on your chart and click 'Select Data'. In the dialog box, click the 'Hidden and Empty Cells' button located at the bottom left, then check the box labeled 'Show data in hidden rows and columns'.

Can I stop a chart axis from automatically changing its format?

Yes. Double-click the axis to open the Format Axis pane. Scroll down to the 'Number' section, uncheck the 'Linked to source' box, and manually set the Category to 'Date' with your desired format code. This forces the chart to ignore the cell formatting and use your static format instead.

Excel & Data Problems