Step Chart using Titles instead of Dates in the Horizontal Axis

[garrett.grillo]

Hi Excel Forum Team,

I have attached a Step Chart model with a horizontal axis date range that works correctly.

What I need is the same Step Chart, but I need to replace the charts horizontal axis dates with the Project Phase titles seen in Column B.

Please let me know if you have any questions or need further clarification.

Thank you in advance for all your help.

Garrett

[MrShorty]

I'm not sure exactly what you are looking for. I am guessing that you want to have the text labels appear along the date axis marking the date that each "phase" starts, or ends, or some date in the middle.

As a first guess at what you want:

1) Format the vertical axis so it has a fixed minimum at 50.
2) Enter 50 into the appropriate cells in column I where you want those markers to appear.
3) Copy/enter/link the project phase text into column J adjacent to the corresponding 50 value
4) Select I4:I36 -> ctrl-c to copy to clipboard -> select chart -> ctrl-v to paste the new series into the chart.
5) Select the new series and format as desired.
6) Download and install Rob Bovey's XY chart labeler add-in (or whatever strategy you like for adding data labels from cells to a chart series, since this functionality is not built into Excel 2010).
7) Add data labels to the chart using column J as the label text.
8) "hide" (format as no tick and no label) the axis, if desired.

Is that close to what you want?

[garrett.grillo]

Hi Mr.Shorty,

Yes, you are correct in that I do want to have the text labels replace the dates along the horizontal chart axis.

I'm just getting out of work, but I try your method to get the Step Chart working.

I'll let you know what I come up with.

Thank you for the detailed directions!

Garrett

[garrett.grillo]

Hi MrShorty,

I seem to be having troubles applying your solution steps.

I just want to be clear, what I'm looking for is to not use the dates in the horizontal axis and instead replace these dates with the 10 project phase listed in the attachment (column B). The dates just came with the Excel template that I downloaded from the internet, so these dates have no relevance to my step chart request.

So, I tried to apply your recommended changes and was unsure what you meant in step 2) "Enter 50 into the appropriate cells in column I where I want those markers to appear". Also in step 4 I wasn't able to complete. I copied the I4:I36 cells and selected the chart then Ctrl-v to paste and nothing happened and that's as far as I got.

Please let me know where I went wrong or maybe you can add further clarification to your explanation.

Thank you again!

Garrett

[MrShorty]

If it helps, I am not sure what you are looking for. It might help if you could better describe (maybe draw) what you are expecting. If this data is not representative of what you really have, then it might help to have a good representative data set.

what I am looking for is to...replace these dates with the 10 project phase listed in the attachment (column B).

Clarification? Completely replace the dates with the text (converting the date axis to a text category axis), or superimpose the text categories on top of the date axis (but the base x axis is still a date axis based on the dates)? This is important because completely replacing the dates with text/categories is going to change this "square wave" step chart into a zig zag sawtooth type look that is typical of generic line charts. Perhaps my first question for you is -- are you needing to keep the "step" chart look, or are you going for a more generic line chart? My assumption was that you wanted to keep the step chart look, and "superimpose" a dummy axis with the text categories over the existing date axis. If that is incorrect, then please explain what you really want to do.

Clarifying step 2): Don't overthing this. I cannot tell from the sample file where they should go. Maybe enter 50 into I20,I22, I24, I25, I26, I28, I30, I32, I34, I36 as evenly spaced guesses to where these should go. 50 corresponds to the current axis minimum value of 50.
Clarify step 4): Because the chart is formatted as "lines with no markers", the data series you added was also probably formatted as line without marker. I expect there were "gaps" in the data, so there were no lines actually shown. So, Excel probably added the second data series, but it was effectively invisible. After pasting the second data series in, go to Chart tools -> Format -> select newly added data series from chart elements drop down -> Format selection -> Add markers to this data series. This should make the data series visible so you can see where it is. (it can be hidden again later if you don't want to see it. At this stage, I find it is helpful to see the data series while putting the chart together).

See if that is helpful.