0

I have a chart and its source data is read from a range which contains dates. When I plot it out into a chart, the x-axis includes dates that are not included in the range.

How do I avoid this?

For example, these are the dates:

28/04/15
27/04/15
24/04/15
23/04/15
22/04/15
21/04/15
20/04/15
17/04/15
16/04/15
15/04/15
14/04/15
13/04/15
10/04/15
09/04/15
08/04/15
07/04/15
06/04/15
02/04/15
01/04/15
31/03/15

But when the chart is plotted out, it includes dates that are not in the list, such as 05/04/15.

4
  • Are the dates you don't want included located in front and behind the inclusive dates of your data or are they interspersed within your data set?
    – OSol1tair3
    Apr 29, 2015 at 14:03
  • they are interpersed with the data set. it is as if the data flows from 31/03/15 to 28/04/15
    – lakshmen
    Apr 29, 2015 at 14:48
  • A few questions: 1) What kind of chart? 2) Is a data point for 05/04/15 showing up on the chart, or is it just that there is a label for that date included on the axis? 3) How is the chart being created? I see you used the [vba] tag, but it's not clear from your question how that is relevant.
    – Excellll
    Apr 29, 2015 at 17:43
  • 1) it is a line graph. 2) there is no data point for 05/04/15 but it is appearing in the x-axis hence the column is blank. There is a label for that date included on the axis. 3) the chart is already created and the source data for the chart is created using vba.
    – lakshmen
    Apr 30, 2015 at 6:28

1 Answer 1

0

Excel has recognized your x-axis data as dates and is trying to "help" you by including all dates in your range (2015-03-31 to 2015-04-28) on your axis-regardless of whether you have data or not. This isn't necessarily a bad thing-typically data that is being charted according to date relies upon the regular progression of data points from day to day (or whatever your interval may be).

If you want to use your dates as simple categorical data (meaning that skipping dates doesn't matter to you, even if you keep the same data order), you'll need to "trick" Excel into thinking they're not dates. The simplest method is to create a "helper" column with the values 1...n for all of your data points. Then, you can use any number of tricks to label the axis values for your non-sequential date names.

1
  • 1
    Alternatively, select the X axis, and under Axis Type, change Automatic to Text Axis. Apr 8, 2022 at 0:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .