Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2015-02-10
3
Medium Priority
?
265 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 Covney
3 Comments
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40601842
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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
 
LVL 3

Author Comment

by:Ed Covney
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Are you looking to start a business? Do you own and operate a small company? If so, here are some courses you need to take before you hire a full-time IT staff.
Though there are a few manual ways to import PST files to Office 365 , third-party PST to Office 365 import tools are preferred over them due to various reasons.  Consequently, many tools or services are available for the same. Here, we pick the to…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

595 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