Unwanted Weekend Dates in Chart

by Allen Wyatt
(last updated November 23, 2019)


Scott has a set of data by date that is used for a graph. The data is entered by date which only reflects weekdays. (The data contains no entries for weekends.) When Scott graphs the data using the dates as the horizontal axis, Excel inserts the weekend dates with no data points. These dates are not in the original data and he cannot seem to get rid of them so that only the data in the table is shown in the chart.

Believe it or not, this behavior is by design. When you convert your data into a chart, Excel tries to make sense of the data it is charting. If it determines that the data in a particular column or row represents dates, it looks at the earliest date in the range and the latest date in the range and then uses that as the range for the axis. That means that it includes data points in the range that may not really exist in the original data—such as the weekends that Scott mentions.

The solution to this is to overcome the automatic parsing done by Excel as it converts the data in your worksheet into a chart. There are a few ways you can do this. First of all, you can convert your original dates into text values. (Excel doesn't recognize text values as dates, and therefore doesn't do the whole earliest-to-latest range thing.) You could do this by actually converting the date column or row into text or by adding a helper column or row that contains the dates as text representations. If you decide to go the helper route, you could use a formula such as the following:


You could, of course, change the formatting pattern for the date to anything you want, as described in other ExcelTips. You would then use the helper column or row as your axis data in the chart. Excel recognizes it as text and includes only the datapoints in the worksheet—no more phantom weekends!

Interestingly enough, you can also fool Excel in its parsing by simply converting a single cell in the date column into a text value. It appears that if the entire row or column doesn't contain dates, Excel no longer applies the date-range parsing when putting together the chart.

If you don't want to modify your original data, you can simply tell Excel to ignore the date parsing. Follow these steps if you are using Excel 2007 or Excel 2010:

  1. Create your chart as you normally would. You should note that the chart includes the undesired phantom dates (weekends).
  2. Right-click the axis that contains the dates. Excel displays a Context menu.
  3. Choose Format Axis from the Context menu. Excel displays the Format Axis dialog box.
  4. At the left side of the dialog box, choose Axis Options. (See Figure 1.)
  5. Figure 1. The Axis Options of the Format Axis dialog box.

  6. In the Axis Type section of the options, choose Text Axis.
  7. Click OK to close the dialog box.

If you are using Excel 2013 or a later version, then the steps are a bit different:

  1. Create your chart as you normally would. You should note that the chart includes the undesired phantom dates (weekends).
  2. Right-click the axis that contains the dates. Excel displays a Context menu.
  3. Choose Format Axis from the Context menu. Excel displays the Format Axis task pane at the right of your work area.
  4. The Axis Options settings should automatically be displayed, but if not click Axis Options and then the Axis Options icon. (See Figure 2.)
  5. Figure 2. The Axis Options on the Format Axis task pane.

  6. In the Axis Type section of the options, choose Text Axis.
  7. Close the task pane, as desired.

At this point, Excel should redraw your chart and treat your dates as text values, even though they really are dates. This means that you won't get any phantom dates in the date range displayed in the chart. In addition, the dates on the axis should be displayed exactly as they appear in the worksheet data. Thus, if you want to change the way the dates are displayed in the chart, you'll need to reformat your source data for the desired appearance.

You should note that your ability to modify the Axis Type setting depends on the type of chart you are creating. If the chart doesn't have an axis (such as a pie chart) or the chart is primarily for plotting numbers (such as with an XY chart), then you may not be able to tell Excel to treat the axis as text. Why? Because doing so makes no sense in the case of those types of charts.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13271) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Editing Reports

The Report Manager allows you to create specialized reports that can be easily printed from your worksheet data. This tip ...

Discover More

Getting Rid of Everything Except Numbers

Got some numbers and letters mixed up in the same cell? You may need to get rid of those letters so you are left with ...

Discover More

Getting Input from a Text File

You can use a macro to read information from a text file. The steps are easy, and then you can use that information in ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Changing Chart Size

Place a chart on a worksheet and you may not be satisfied with its size. Changing the size of a chart is a simple process ...

Discover More

Adjusting the Angle of Axis Labels

When creating a chart, your axis labels may be wider than desired. One way to deal with them is to change the angle at ...

Discover More

Changing Axis Tick Marks

Create a chart in Excel, and you may find that the tick marks shown on the axes in the chart aren't to your liking. It is ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 1?

2020-01-02 03:50:42


Thanks a lot.

2019-02-21 03:52:47


Thanks a lot for this

2017-10-07 02:04:18


How to hide few intervals of Y-axis in excel program?

2017-08-06 23:55:53


thank you very much for the help sir

2017-05-23 02:38:08

Michael (Micky) Avidan

@Mike f,
Try to HIDE(!) those rows containing the Friday before Memorial day and the Memorial day.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2017)

2017-05-22 15:54:09

mike f

While above works in most cases, How can I select which dates to include.
I want to include certain dates based off holidays, not the weekday or weekend.
For example
I might want exclude Friday before Memorial day and Memorial day.

2017-05-16 03:59:33


even before reading your advice I tried same thing. So I tried to modify format axis to text axis and also tried to change data to text.
But immediately as I did this, my chart line disappeared, so I had only empty chart infron of me.
Could you please advice, why this happened?

Thank you.

2017-05-02 18:40:01


My format axis, under axis options does not have Axis Type. The sub headers under axis options are Bounds, Units, Vertical axis crosses, and Drisplay units. Is there something I did wrong?

2017-01-04 08:34:34


After converting dates to text, the graph doesn't keep the proportion between the dates. For example, if the dates are: 01/01/2012, 01/01/2013, 01/01/2017 I want the longer distance between 2013 and 2017 in the graph. If anyone have any idea? Thanks.

2016-06-05 12:54:48

Harrison Delfino

For this, I had been using MarketXLS and it's great.
It has instruction which can help you.
They also have live to support to help you anytime.

2016-05-31 21:41:46


Thank you! it worked right away! :))

2016-04-16 11:33:21


this information is really helpful.

Thanks for taking the time to create this document

2016-04-05 06:05:56


Strewth did I go round the houses on this!

Did eventually figure on setting the dates as text but missed the 'Text Axis' check-box which is much better solution. Anyway - sorted now. Good article.

2016-02-24 10:20:17


Thanks for the tip!

2014-10-05 06:58:47


Great tips! I always like to know more than one way to solve a problem.

2014-10-05 01:03:34

nir liberman

At least for line graph:
Just use XY graph instead of line.

2014-10-04 08:50:48

Greg Kavonius

You can also hide the weekend dates and they will not appear in your chart. I did this on a weekly basis when doing the metrics for the company I work for.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.