Solved

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

Posted on 2015-02-10
3
238 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 26

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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

685 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