Excel Graph: Show Only Dates With Data

Yes. To make an Excel graph show only the dates represented by its data, set its horizontal category axis to Text axis rather than Date axis. If your chart source also contains rows with blank values, filter those rows out or build the chart from a filtered range; changing the axis alone will not remove categories already included in the source.

Why a chart can show dates with no data

Excel charts can interpret date labels in two different ways. A date axis treats dates as points on a continuous calendar timeline. If the source contains January 1 and January 4, Excel can show January 2 and January 3 between them even though the source has no rows for those dates. That spacing is useful when the distance between dates matters, but it can look like the graph is displaying dates without measurements.

A text axis treats each date in the source as a separate category. It plots the listed categories in order without inserting calendar days that are missing from the source. For example, a list containing January 1, January 4, and January 9 will show those three labels next to one another as categories, rather than spacing them according to the number of days between them.

There is an important distinction: a text axis does not remove dates that are already present in the chart’s source range. If the source has a date in every row but some corresponding values are blank, those dates can still appear as categories. To remove them too, filter the source or create a range containing only rows with data.

Change the horizontal axis to Text axis

For a line or column chart with dates along the horizontal axis, use these steps in Excel for Microsoft 365 on Windows:

Select the chart, then click or right-click the horizontal date axis.
Choose Format Axis.
In the Format Axis pane, open Axis Options.
Under Axis Type, select Text axis.

The chart should now show the category labels supplied by the source without inserting intervening calendar dates. If you want Excel to decide automatically, choose Automatically select based on data. To restore calendar spacing, select Date axis. The available axis choices depend on the chart type and the axis you selected; an XY (Scatter) chart uses a value axis rather than a category axis, so it does not use the same Text axis setting.

On Excel for Mac, select the axis and open Format Axis to find the axis-type options; the pane’s appearance can differ from Windows. Excel for the web has a more limited set of chart-formatting controls, so if the needed axis-type choice is not available, open the workbook in the desktop app.

Remove blank-data dates from the chart source

If dates with blank measurements are listed in the source, remove those rows from the chart’s plotted categories. One straightforward option is to filter the worksheet table or range so that rows without a measurement are hidden. Excel charts normally omit data in filtered-out rows. If the chart still includes hidden rows, select the chart and go to Chart Design > Select Data > Hidden and Empty Cells, then make sure Show data in hidden rows and columns is not selected.

Another option is the chart’s own filter. Select the chart and use the Chart Filters button beside it, then clear unwanted categories and choose Apply. This works well when you want to hide a few specific dates, but it is less convenient when the unwanted dates change frequently or when the rule is “show every row with a measurement.” In that case, filtering the source data or using a helper range is easier to maintain.

Remember that filtering out a row affects every series in the chart for that category. If one date has a blank value in one series but a valid value in another, removing the whole date may discard useful information. Decide whether a date counts as “having data” when any series has a value, or only when a particular series does, before filtering.

Build a helper range that keeps nonblank values

For a single measurement series, a dynamic helper range can return only rows whose value is not blank. This is useful when the original data must remain intact. The example assumes dates are in A2:A7 and measurements are in B2:B7, with a truly blank cell for a missing measurement. Enter the formula in D2:

=FILTER(A2:B7,B2:B7<>"")

If the six measurements are 18, blank, 23, blank, 0, and 16, the result contains four date-value rows: 18, 23, 0, and 16, alongside their original dates. The zero remains because zero is a value, not a blank. Select the returned range to create the chart, or use it as the chart’s data source. FILTER is available in Microsoft 365 and Excel 2021 or later.

If your source uses formulas that return an empty string, such as a formula whose result is "", the comparison in this example excludes those results as well. If you want to exclude zeros too, use a condition that tests for nonzero values instead; do not do that when zero is a meaningful measurement. For multiple series, define the rule carefully: a date may need to remain if any series has a value, even when another series is empty.

Understand blanks, zeros, and missing dates

A blank cell, a zero, and a missing date are three different things. A blank cell means there is no plotted value in that cell, although the date label may remain if the row is still part of the chart source. A zero is an actual numeric value and is ordinarily plotted at zero. A missing date means there is no category row for that date at all. The axis type determines whether Excel fills calendar space between existing dates; source filtering determines whether blank-data rows remain as categories.

For a chart with a date axis, Excel may display calendar intervals across gaps even when no source rows exist for those days. For a chart with a text axis, Excel shows the date categories present in the source, including categories whose values are blank. These behaviors are not contradictory: one is about how Excel lays out the axis, while the other is about which source categories the chart receives.

Charts also have settings for how to display empty cells. Select the chart and choose Chart Design > Select Data > Hidden and Empty Cells. Depending on the chart and data, you can choose to show empty cells as gaps, zeros, or connected data points. Those choices affect the plotted line or column values; they do not automatically delete the date labels from the category axis.

Choose the right chart and axis for the question

Use a text axis when every listed observation should get equal visual spacing—for example, a report comparing selected business days, inspection dates, or irregularly timed events. Use a date axis when elapsed time matters, such as a daily temperature series or a sales trend where a week-long gap should look wider than a one-day gap.

An XY (Scatter) chart is useful when the horizontal position must reflect numeric intervals precisely, such as measurements taken at irregular times. Excel stores genuine dates as serial numbers, so dates can be used as numeric horizontal values in a scatter chart. That chart will position observations according to elapsed time; it will not behave like a compact list of categories. If the goal is simply to display a selected set of dates with equal spacing, a line chart with a Text axis is usually the more suitable choice.

Also confirm that the cells contain real Excel dates rather than text that only looks like a date. Real dates are stored as numbers and can be formatted to display in different date styles. Text dates may sort or plot unexpectedly, especially when their format is interpreted differently by regional settings. If labels appear out of chronological order, check the source order and the underlying cell values before changing chart settings.

A practical way to diagnose the chart

When the graph contains unexpected dates, check the axis and the source separately. First, click one of the unexpected labels and inspect the axis type. If it is a Date axis and the extra labels are days between source dates, changing to Text axis should compact the timeline. If the unexpected label corresponds to an actual row in the source, inspect that row’s measurement and decide whether the row should remain.

Extra calendar days between observations: try Text axis if equal spacing is appropriate.
A date row with an empty measurement: filter the row or exclude it from a helper range.
A zero that should not appear: decide whether zero is a real value or a code for missing data, then adjust the source rule accordingly.
A line that bridges a blank: review the empty-cell display setting; a connected line does not mean the blank date was removed.
Dates in an unexpected order: check whether the source values are real dates and sort the source range chronologically.

Keep the chart source and the intended meaning of the graph aligned. A compact axis can make irregular observations easier to read, but it can also hide long gaps in time. Conversely, a calendar axis accurately communicates time intervals but may show dates with no observations. Neither setting is universally correct; the right choice depends on whether the reader should compare categories or elapsed time.

Frequently Asked Questions

How do I stop Excel from filling in missing dates on a graph?
For a category chart, select the horizontal axis, open Format Axis, and choose Text axis. This stops Excel from inserting calendar intervals between the date categories supplied by the source.

Will Text axis hide dates with blank cells?
No. Text axis changes spacing; it does not remove source rows. Filter out rows with blank values, use the chart filter, or build the chart from a helper range that excludes those rows.

Why does my chart still show a date when its value is zero?
Because zero is numeric data, not a blank. If zero represents a genuine measurement, it should generally remain. If your worksheet uses zero to mean “no observation,” exclude those rows deliberately when preparing the chart data.

Can I show only dates that have data without deleting rows?
Yes. Filter the worksheet data, use the chart’s category filter, or create a separate helper range. A helper range lets the original records stay untouched while the chart uses a selected subset.

Should I use a Date axis or a Text axis?
Choose Date axis when spacing should reflect elapsed calendar time. Choose Text axis when each supplied date should be one equally spaced category and dates absent from the source should not appear between 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.