Best Microsoft Excel Bloggers

Thursday, June 16, 2016

Excel 2016 Charts - X Axis Date Units

Date Units on the X Axis- Excel

I was working on a dashboard in Excel 2016 and I ran into something a bit frustrating. So, I thought I would share it since I am assuming others have run into the problem as well.
Below is a picture of my data and my chart - See anything wrong with it?
OK- now don't get too picky - this is before I formatted or updated anything - so what's wrong with it?

Take a look at my X axis..
The X axis shows 1/1/2016, 2/1/2016 instead of what is actually shown in Column A, which is the last day of each month.  I have used this same data and charted it in other versions and never ran into this particular issue so I was a  bit surprised.
Apparently Excel 2016 and probably Excel 2013's unit default is month.
To fix this problem, right-click on the X axis and on the Format Axis pane, change the Base to Days.

Once you do that, magically the dates in my chart now match the dates in my worksheet.

1 comment:

  1. If the spacing of your data is monthly, Excel tries to be helpful, and uses months as the base unit. If you then use a number format that shows month or month and year but no days, it might look fine. But I almost always want a base unit of days.


Ms. Excel- Resident Excel Geek