Dates by Month on X-Axis in Horizontal Stacked Bar Chart

Hi all,

I understand some of the nuances with working with the horizontal bar chart in a Ganntt style format but can't seem to figure out a way to force the X-Axis to behave the way I'd like it to.

In Excel 2007, I am trying to get the X-Axis to show major units of Months. However, in my chart options I can only change the Y-Axis to be Dates. Changing the Scale to something like a 31 day unit gets me close, but still goes wonky after a couple of months as it drifts.

Changing my data layout (from the below) to be a vertical format produces the same problem, just in the other direction.

My data looks as follows:


System Blocker Production Migrataion Retired
Sys1 1/1/2012 780
Sys2 1/1/2012 400 60 90
Sys3 2/1/2013 30 5 1

The Blocker column is formatted as "No Fill" in order to cover a portion of the time-scale, so that tasks don't always start at the beginning of the time scale.

My X-Axis displays as random dates throughout the period and all of my data displays properly in terms of the scale on the grid.

What is NOT working is that the dates shown on the X-Axis really need to be based on a Monthly scale instead of the randomly selected dates Excel is using.

I can't seem to find the right combination of options to make that happen or force a scale on the X-Axis.

I've been through a lot of the X-Axis discussions on this and other sites, but can't find an example of exactly what I am doing here.

I have also tried creating a secondary Axis and the dates don't align properly, even with the same scale applied. And also have tried the copy/paste-special method of adding the explicit dates but that forces the whole chart into a fairly unusable format.

Am open to all suggestions and if you can provide specific details, even better.

Thanks Andy it was the "Plot as Column" part I was stuck on.

Absolutely appreciate your expertise and input!

Ok, on further review, the Dates are displaying by month but are not lining up with the date scale being used by the primary X-Axis, which was one of the problems I had when adding that secondary axis to start with.

If you look at the secondary X-Axis between "Sep '13" and "Oct '13" - the Primary X-Axis marks one of the interim dates as "11/22/2013" which is not really possible.

I changed and reset the scales to be the same and that did the trick. Not sure what went wrong the first time but looks fantastic now. Thanks again!

Hi Bandolier2k, I'm having the same issue you had (with the secondary x-axis). I'm trying to figure out how to "change and reset the scales" as you said you did, but I'm not having much luck. Any hints you can pass my way?

Hi all. Sorry for a reply to an old thread. I have a stacked Bar chart in Excel 2010. I've added a secondary horizontal date range but when I right click and format axis I have no date options like the excellent example I downloaded from this thread. I have my chart in the same excel workbook that the example is opened in on another tab. I can see the date options on the example but not on mine. The charts appear to be the same type.. Any ideas?