Excel Chart: Remove Dates With No Data From the Axis

To remove dates with no data from an Excel chart, select the horizontal axis, open Format Axis, and choose Text axis under Axis Options. A text axis shows only the date categories in your source data; a date axis lays out calendar time and can therefore show dates for which you have no data.

Why Excel shows dates that are not in your data

Excel can interpret a chart’s horizontal axis in two different ways. A date axis treats dates as points along a continuous calendar timeline. If your data contains January 1 and January 5, Excel may show January 2, 3, and 4 between them even if the source table has no rows for those days. Those dates are axis labels, not added data records.

A text axis treats each date label as a category. It displays the categories supplied by the chart source and does not fill the calendar gaps between them. This is usually the right choice when you want to compare only the dates that have observations.

For example, suppose a table has entries for January 1, January 3, and January 6. A date axis can show the intervening days as part of the timeline. A text axis shows only January 1, January 3, and January 6. The plotted points represent the same records, but the spacing changes: a text axis gives each category equal space, while a date axis reflects the actual number of days between records.

Switch the horizontal axis to a text axis

In Excel for Microsoft 365 on Windows, change the setting on the chart itself:

Select the chart, then click the horizontal date axis to select its labels.
Right-click the axis and choose Format Axis. You can also use the Format tab’s current selection controls to open the formatting pane.
In the Format Axis pane, open Axis Options.
Under Axis Type, select Text axis.

The chart should now show only the date categories represented in its source range. If you do not see the Axis Type choices, check that you selected the horizontal axis and that the chart type supports category axes. The exact options vary by chart type; a scatter chart, for example, uses a numeric value axis rather than a text category axis.

On Excel for Mac, select the axis and open Format Axis from its contextual menu or the chart-formatting controls, then look under Axis Options for the axis type. The pane layout can differ slightly by Excel release, but the setting has the same purpose. Excel for the web may offer fewer formatting controls; if the axis-type choice is unavailable, open the workbook in the desktop app.

When a text axis is not the right fix

A text axis removes calendar labels between recorded dates, but it also gives every category equal spacing. That can make a chart misleading if the amount of time between observations matters. If one point is one day after the previous point and the next is a month later, a text axis makes both gaps look the same.

Keep a date axis when the chart is meant to communicate elapsed time, such as daily temperatures, stock observations, or service demand over a period. In that case, the empty dates are part of the timeline, even though they do not have measurements. If you need the horizontal distance between dates to be proportional to elapsed time but want to plot only observed dates, an XY (scatter) chart can be a better fit when the dates are valid Excel date values and the charted relationship suits a scatter plot. A scatter chart has a value axis, not a text axis, so it does not offer the same category-axis switch.

Also distinguish between a date that is absent from the source and a date that is present in the source but has a blank measurement. Changing to a text axis will not remove a date category that is already in the chart’s source range. You must filter that record out or adjust the source range if the category itself should disappear.

Filter out rows with no measurements

If the source includes a row for every calendar day, with blank values on days without data, changing the axis alone will not remove those dates. Filter the source to show only records with measurements. For a normal range, turn on filters from Data > Filter. For an Excel table, use the filter arrow in the measurement column and exclude blanks. The chart will usually omit rows hidden by a filter.

If filtered rows remain visible in the chart, select the chart and open Chart Design > Select Data. In the dialog, choose Hidden and Empty Cells, then make sure Show data in hidden rows and columns is not selected. The wording and dialog layout can vary slightly between releases. This setting also affects rows hidden manually, so consider whether the chart should include manually hidden records before changing it.

You can also use the chart’s filter button, when available, to hide particular date categories or series without changing the worksheet’s displayed rows. Select the chart and click the funnel-shaped Chart Filters button beside it. This is useful for a quick presentation change, while filtering the source table is often easier to audit and reuse.

Check what counts as a blank

A cell that looks empty is not always empty to Excel. It may contain a formula that returns an empty string, a space, or a value hidden by number formatting. A filter for blanks may behave differently depending on what the cells actually contain, and a chart may treat a formula result differently from a genuinely empty cell.

To inspect a questionable value, click the cell and look at the formula bar. If it contains a formula or a space, the cell is not truly empty. For a chart of a line or similar series, select the chart and use Chart Design > Select Data > Hidden and Empty Cells to choose how genuinely empty cells are displayed. The available choices typically include gaps, zero, or connecting data points with a line; the useful choice depends on what the chart is intended to show.

Do not confuse a missing date category with an empty measurement. A gap setting controls how an empty plotted value appears, but it does not necessarily remove the date label from the axis. To remove the label, exclude that category from the chart source or filter the row.

Choose the fix that matches the chart

Use a text axis when you have a list of observed dates and want only those dates shown as categories.
Keep a date axis when the continuous calendar scale is important and gaps in observation should remain visible.
Filter source rows when the table includes dates that have no measurements and those date categories should be excluded.
Use a scatter chart when proportional spacing between irregular date values matters and the data is appropriate for an XY chart.
Adjust empty-cell display when dates should remain on the axis but missing measurements should appear as gaps rather than misleading zeros or connected lines.

One useful way to decide is to ask whether the distance between dates carries meaning. If it does, retain a time-based axis. If the dates are simply labels for separate observations, a text axis is often clearer. For instance, a monthly survey conducted on different days may be better represented as evenly spaced survey categories, while a sensor series with irregular readings usually needs to preserve the time intervals.

Common problems and quick checks

If changing the axis type appears to do nothing, check the chart’s source range and chart type. A chart might include extra rows beyond the visible data, or the dates might be text rather than Excel date values. A true Excel date is stored as a number and displayed using a date format. Text that merely looks like a date can behave as a category label and may not support the same date-axis controls.

Review the source range through Chart Design > Select Data. Confirm that it includes only the intended date and value columns, and check whether blank rows or old records are included. If a chart is based on an Excel table, new table rows may be added to its source automatically; that is helpful for expanding data, but a row with a date and no value can still create a category.

Also check whether the date axis is showing minor or major tick marks that make it seem as though extra records exist. An axis label is not proof that Excel plotted a value for that date. Compare the plotted markers with the source table, and use the text-axis setting only if removing those calendar labels is appropriate.

Frequently Asked Questions

How do I remove weekends from an Excel date chart?
Use a text axis if you want only the dates present in the source to appear, or filter weekend rows out of the chart source. If you keep a date axis, weekends may remain on the calendar scale even when there are no weekend records.

Why does my chart show dates with no rows?
The horizontal axis is probably a date axis. It fills the time span between the earliest and latest dates, so intermediate calendar dates can appear as labels even though they are not separate records in the worksheet.

Will changing to a text axis remove blank date rows?
No. It removes intervening calendar dates that are not source categories. A row that already contains a date remains a category unless you filter it out or remove it from the chart’s source range.

Can I hide missing dates without changing the worksheet?
Yes. If the chart has a Chart Filters button, use it to exclude categories from the chart display. You can also create a separate chart source containing only the records you want to display.

Should I use a text axis or a date axis?
Choose a text axis when each observed date is a category and equal spacing is acceptable. Choose a date axis when the chart must show a continuous timeline or communicate the actual time between observations.

Keep the chart honest and readable

Removing dates with no data is mainly an axis and source-data decision, not a way to delete records from the worksheet. Start by switching the horizontal axis to Text axis if the goal is simply to suppress calendar dates between observations. If dates with blank values are themselves in the source, filter those rows or refine the chart range. Preserve a date axis when missing periods are meaningful, and label the chart clearly so readers can tell whether the spacing represents calendar time or a sequence of observed categories.

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.