Solved

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

Posted on 2015-02-10
3
228 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:
Ingeborg Hawighorst 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to With line 4 39
Copy all Sheet1-Sheets into a newly created workbook using VBA 8 33
Boolean help 6 27
Sum iF  based on a null cell 11 29
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
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 will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

919 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

18 Experts available now in Live!

Get 1:1 Help Now