Calling all Chart experts!!

[PCRIDE]

I would consider myself almost an expert with excel charts as far as creating different views by manipulating the graph format to come up with something you just didn't think was possible with Excel Charts. Given that... here is my question or challenge if anyone can help me out.

Let's say you have a basic line graph that shows number of x per week, what I want to be able to do is display the line graph so its perfectly horizonal even considering the values are not the same per week. So obviously if you have a chart that shows 10, 20, 30, 40, 50 each week, the line graph would show it trending upwards. But what if you wanted to make this perfectly horizontal?

Here is what I do, looking for another easier way to do it.

Add Data labels to a basic line graph
Add Line Markers that are circular, set it to No Fill, make a thicker Outline and enlarge the markers so in the end I get big circles with nothing in them
I then add Data Labels, choose Center, now I have a value in the circle
Format the chart so there is NO line in the line graph, now there is no line
My graph still shows the upward trend

I then format the Axis , Maximum category to a negative number much larger than the largest value on the chart, In this case its -600, this forces the graph to the top of the chart area almost making it perfectly horizontal.
Now I change the Major Unit in the axis to again something very large, like 2000.

Then I hide the axis by making the text smaller and changing the font color to match the chart background. Or just remove the chart axis in the format controls in the Ribbon.

Now I have a pretty flat chart but its still not 100%. Its acceptable in my graph, but I was wondering if anyone else does this differently.

Any ideas?


Here is a snapshot of the chart almost finished.

https://skydrive.live.com/redir?resid=87472BF740A1D7CB!331&authkey=!AN2CtHVs7Po72H0

 

[pgc01]

Hi

The way I'd do it is to use the X values but not use the Y values for the points, I'd use for Y a constant value instead, for ex. 1.

This makes the line perfectly horizontal. You'd then do your thing with the markers to make them circles.

To finish, you'd use the real Y values as labels. For that you can do a small vba snippet or use Rob Bovey's XY Chart Labeler: XY Chart Labeler

 

[BAlGaInTl]

I think what you have already figured out is a pretty nifty trick.

I made it a little quicker by doing a "bubble" graph... That made the circles for me.

Then I just set my scale to -60000 to 60000

The larger you set the scale, the more horizontal it will look.

ETA: The above works as well, but It seems a bit more work to use VBA.

Why don't you try increasing the size of your scale?

 

[PCRIDE]

Hi, I can pretty much get it good enough with adjusting the scale even higher. Sometimes the same exact way in one graph, doesn't always work with another for some reason in excel. When combining a stacked Bar chart with 2 series and adding a line graph as a secondary axis; on the bar charts I have to use "Reverse Axis" because I have 6, 5, 4,3, 2, 1 order vs 1, 2, 3, 4, 5 to make it progressive, but adding the line chart doesn't give you the option to reverse, so the data points don't line up.

Oh well I created a work around but again another weird thing with excel!

 

[PCRIDE]

Hi, I can pretty much get it good enough with adjusting the scale even higher. Sometimes the same exact way in one graph, doesn't always work with another for some reason in excel. When combining a stacked Bar chart with 2 series and adding a line graph as a secondary axis; on the bar charts I have to use "Reverse Axis" because I have 6, 5, 4,3, 2, 1 order vs 1, 2, 3, 4, 5 to make it progressive, but adding the line chart doesn't give you the option to reverse, so the data points don't line up.

Oh well I created a work around but again another weird thing with excel!