Step-chart in Excel

[Shurov]

I am trying to create step-chart in Excel using the tutorial (section "Step Chart Approach 2: Line Chart with Repeated Ranges").

However, it works incorrectly. According to instructions (section "How it Works") values from 2 named ranges should be shuffled, while I get consequent values from 2 ranges.

The resulting Series formula seems to be correct:

Please Login or Register  to view this content.

(I am just using namedRange2 instead of namedRangeX)

What am I doing wrong? How to force excel to mix values from 2 ranges?

[MrShorty]

Can you post a sample worksheet? Based on what you have posted, I cannot see what you have done wrong, but I also cannot see what the named ranges refer to, nor can I see what data is in the named ranges.

One key element that I see in Jon's tutorial that makes this work is that the X data in his Dates and DatesX named ranges are actual dates. You use "names" in your named ranges, suggesting to me the possibility that what is in KPInames and KPInames2 are names/text strings rather than dates. If that is true (and I have no way of knowing one way or the other), then I can see this technique failing, because it seems to require a date axis -- not a category axis.

[Shurov]

You use "names" in your named ranges, suggesting to me the possibility that what is in KPInames and KPInames2 are names/text strings rather than dates. If that is true (and I have no way of knowing one way or the other), then I can see this technique failing, because it seems to require a date axis -- not a category axis.

You are right, I have category axes.
I have tried changing categories axes to the dates, it worked. But I need categories...
I have attached my file with 2 approaches - categories and dates. 1st is not working, 2nd is working

So no way leaving categories on my axes?

[MrShorty]

I'm sure it can be done. It isn't the kind of charting I do so I'm not very good at it. My first attempt would be to add a "dummy" series to the existing step chart, then format that to be on the secondary axis system, then hide the axes that are not part of the actual chart I want to display.

1) Add a column of 0's to the existing table (I used column H)
2) Select columns A and H, copy to clipboard.
3) Select chart, paste special, as new series with X values in leftmost column. This should add an additional series to the chart.
4) Select this new series, then format this series to be on the secondary axis system (follow instructions here for adding a secondary vertical and horizontal axis).
5) Then format axes as desired.
a) if you want the category x axis at the bottom, format the secondary vertical axis so the horizontal axis crosses at automatic or a fixed value.
b) other formats as needed to hide/delete/display the axes as you want them to appear.

[Shurov]

thanks! I have forgotten about the trick with dummy series and secondary axis. It did what I needed!