Solved

Excel 2010 How can I change a chart's y-axis label location?

Posted on 2015-02-10
3
225 Views
Last Modified: 2015-02-11
I have a simple 2D chart with 0.0 to 1.0 labeling up the y-axis and exactly middle aligned to the corresponding chart y-axis tic marks.  I would like to drop the  "0.0" and place the remaining labels aligned to the middle of the intervals between tic marks.

TIA,

Ed
0
Comment
Question by:Ed-SSA
3 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40601842
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 40601885
Hello,

the Y axis is the value axis and the tick marks and labels show the exact position of that numeric value. Placing the label between the tick marks -- why? -- is not possible out of the box.

You can hide the axis labels. Then add XY chart helper series on the secondary axis with data points in the positions where you would want the labels. Then add data labels to that series and point the labels to a range of cells that contain the correct labels.

You can use the XY Chart Labeler add-in for that, or in Excel 2013 this is a tick in the label options box "Value from cells".

The screenshot shows the helper series with the markers of the XY series still visible. The final step is to format these to hide the markers.

chart
cheers, teylyn
0
 

Author Comment

by:Ed-SSA
ID: 40602035
Teylan,

To answer your "why?" question:
I've have a "Bernoulli's Binomial Distributions in Motion" spreadsheet. For small N, a single chart is all that is needed. For N 32 or larger, all the single chart choices are bad. So I use 2 nearly identical charts differing only in scale. The top one in the attached ranges 0.0 -> 1.0 but the bottom 20% is hidden beneath the bottom chart which is scaled 0.0 to 0.2 step 0.02. With the y-axis label removed, the "jump" between charts is about 5 pixels but occurs instantly. Had I retained the y-axis labeling on the bottom chart, its a very distracting 40-50 pixels. Accept Excel's automatic scaling is the worst - you get to see a lot of the "bars" but with no context (rapidly changing scales).

For very large N, 128 or greater, most of the bars almost disappear behind the x-axis line for 0.1 < p , 0.9 so the charts split at 0.1 - I'm trying to automate as much as possible, so manual adjustments - only if absolutely necessary.

In any event, if I can capture what you've done in a macro, that would be ideal. Alternately I've begun playing with a text box with vertical content: (but in need of precise spacing between paragraphs).
1.00
0.90
0.80
0.70
0.60
0.50
0.40
0.30
0.20
0.18
0.16
0.14
0.12
0.10
0.08  
0.06  
0.04  
0.02  
0.00
Bernoulli-N-32.jpg
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now