Date and Time Series Issues in Excel Charts

Many users think they are getting an axis error when they say that their column or line charts are showing every date between data points, but all they want to show is the dates related to the data.  In your Excel Executive Dashboards you may use date categories in your charts, however, this may have unintended consequences that you didn’t know.  When you use dates as a chart category in Excel, Excel applies the best setting for your data.  If it recognizes text, it will set your axis categories as text, if it recognizes dates, it will set up your chart using the Excel date (time scale) axis – Automatically.

Here is what Microsoft Excel Help states on the subject:

“A date axis displays dates in chronological order at specific intervals or base units, such as the number of days, months, or years, even if the dates on the worksheet are not in sequential order or in the same base units.

By default, Excel determines the base units for the date axis, based on the smallest difference between any two dates in the worksheet data. For example, if you have data for stock prices where the smallest difference between dates is seven days, Excel sets the base unit to days, but you can change the base unit to months or years if you want to see the performance of the stock over a longer period of time.”

‘Automatic’ Problems





This automatic date (time scale) setting may not cause you a problem, but in some cases it may not be what you want to see.  For instance, if your chart series categories are dates but are not in sequential order and you just want to see a column chart with only the dates chosen, however, this is how the Automatic choice of date (time scale) axis will look:

image

Why is it doing this?  As the Excel help text states, it will choose the base units based on the smallest difference between any two dates in the chart data.  In this case, the smallest difference is 5 days, so it will choose Days as the Base Unit.  This chart does a really good job at showing how often the data is occurring but it is difficult to compare the columns because of the extra white space.  This chart has its uses, but not what I was looking to do.

Here is what you get if you change the base units from the automatic choice of days to either months or years:

image

Definitely not what I was wanting.  It is now not showing all of my data points, but instead it is only showing the first data point of the month or year that exists in the data series.

 

Axis Type

Now this is the type of dashboard column chart that you were hoping to achieve in Excel, It is a simple choice away from the automatic choice that Excel makes for you..

image





This shows my data more closely together with the categories of each data point showing the date.  It is not expanded showing every day of the month.  How can you achieve this in your dashboard template?  You need to change your Axis type from Date Axis to Text Axis and here are step-by-step instructions showing you exactly how to do this:

How-To Excel Dashboard Instructions

1) Create your chart in Excel

image





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





2) Right click on the Horizontal Axis and Select Format Axis…

image

3) Now in the “Axis Type” Select “Text Axis” in the middle of the Axis Options sub menu of the Format Axis Dialog box.

image

As soon as you change the type to a text axis, you will see your chart change to what you were looking for even before you choose the close button.

What is it doing?





When you select Text Axis, you will see that Excel is no longer treating the data as a Time Series and because of it, it will use each category and not insert other dates in-between your categories.

So next time you are using Dates as your categories and you expect to only see a few dates, but instead you are seeing a date range on your horizontal (X Axis) axis, be sure to check your Axis Type and change it to Text Axis if that gives you the desired outcome.

I hope this helps you in building your next Excel Dashboard Template.  Please leave me a comment, sign up for my RSS feed, and leave me comments on your problems!

Steve=True





11 COMMENTS

  • At last! one very good blog about Charts. Understanble deep insights.
    Thank you for your commitment to other people.

    I have one doubt: What’s the difference between Major Units and Base Units in a date axis? It seems redundant

    Thanks

    • Thanks for the amazing comment Miguel. Glad you like the website. The major and minor units typically set the vertical gridline spacing. The base unit sets the horizontal category spacing. Hope this helps. Steve=True




  • If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:




  • I just ran across your website and I have been able to provide some improvements in charts I’ve been working on – the information is easily understood and I find this is an excellent source for learning.

    I have been working on a line chart where I want to show a specific event schedule, and when the event actually started. I calculate plus or minus the time – so if an event was to start at 12:00 and it starts at 12:01, (+1) from the start. If it starts early, at say 11;58, I’ll show the event started 2 minutes early, or (-2) from the scheduled start.

    So, now I want to chart this, with ‘0’ being the horizontal axis, and the plus or minus times to show how far early or late the event(s) were from the actual schedule. OK?

    I have been able to create a chart that will plot the plus and minus, however, in the data area, it will not display negative time numbers. Only the positive numbers are shown.

    Do you know of a way I can show the negative numbers on the chart?

    Best Regards,

    Joe

  • Steve,

    That doesn’t get what I’m looking for because I need the negative numbers to show in the data table on the chart. And this will provide a chart, but the negative numbers will not display. I need a chart that shows + or – the number of minutes:seconds from zero. I can create a chart that will do that – but it will not show me any of the negative numbers that fall below the zero line.

    Does that make sense?
    Thanks,
    Joe

    • So if I understand correctly, you are subtracting times and sometimes there are negative times, but they are showing up as a very small number and not in negative minutes? Given that, if A1 = 12 PM and A2 = 11:58 AM then use this formula to get positive and negative minutes
      =IF((A2-A1)<0,-1*MINUTE(ABS(A2-A1)),MINUTE((A2-A1)))

  • The formula for getting the time works fine. I am getting a result of + or – in the results. What happens when I chart the data – I do get a zero horizontal line and a line showing the data plus or minus the zero line. This works fine. For example:
    00:42 (42 seconds late)
    -09:43 (9 minutes 43 seconds early)
    -14:43 (14 minutes 43 seconds early)
    -17:42 (17 minutes 42 seconds early)

    On the chart the positive number will be above the zero line, the negative numbers below the zero line.

    The positive number will display in the data table and date label.
    The negative numbers will not display in the data table and data label.

    I hope this helps.
    thanks,
    Joe




  • If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:




    • Hi Ashfaq, I am not sure the exact issue. Normally i would just add another data point for the new date. If you have done that and are not seeing your new date, then you should check and see if the horizontal axis is set to TEXT or Date. Text will show each date. Date will show dates along a range and your start and end dates may be very far apart and hard to see each individual date along the spectrum.

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here