Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-10
3
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

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!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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