Y-axis tick marks

Hi all,

  I'm pretty knew to Excel VBA and charting.  I have a simple histogram
chart on a worksheet and the data ranges are 1 to 100.  So Excel
automatically puts the Y-axis tick marks at 0, 20, 40, 60, 80, 100.  But
I only want three tick marks to show on the Y-axis (0, 50, 100).  How
can I do that?

  Also is there a way to put a horizontal line at the 50 y-axis tick
mark that will show on top of my chart?  My users want to be able to
quickly see the 50 line (i.e. which histogram bars extend above it, and
which ones don't reach it).

    It would be great if I could programmatically do this in VBA code.

Thanks!
Brad

0
12/17/2007 9:17:31 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
644 Views

Similar Articles

[PageSpeed] 2

Hi,

Double click the Y axis and on the Scale tab set the Major Unit value to 50.
If you want code use the macro recorder whilst you do this and then you 
should get something like,

ActiveChart.Axes(xlValue).MajorUnit = 50

As for adding a datum line see here for examples.
http://www.andypope.info/charts/averageline.htm

Cheers
Andy

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Brad Stone" <brushyland@yahoo.com> wrote in message 
news:4766E76B.8550ECEE@yahoo.com...
> Hi all,
>
>  I'm pretty knew to Excel VBA and charting.  I have a simple histogram
> chart on a worksheet and the data ranges are 1 to 100.  So Excel
> automatically puts the Y-axis tick marks at 0, 20, 40, 60, 80, 100.  But
> I only want three tick marks to show on the Y-axis (0, 50, 100).  How
> can I do that?
>
>  Also is there a way to put a horizontal line at the 50 y-axis tick
> mark that will show on top of my chart?  My users want to be able to
> quickly see the 50 line (i.e. which histogram bars extend above it, and
> which ones don't reach it).
>
>    It would be great if I could programmatically do this in VBA code.
>
> Thanks!
> Brad
> 

0
andy9699 (3616)
12/18/2007 9:03:33 AM
Thanks Andy!  I have it working perfectly now thanks to your suggestions.

~Brad


Andy Pope wrote:

> Hi,
>
> Double click the Y axis and on the Scale tab set the Major Unit value to 50.
> If you want code use the macro recorder whilst you do this and then you
> should get something like,
>
> ActiveChart.Axes(xlValue).MajorUnit = 50
>
> As for adding a datum line see here for examples.
> http://www.andypope.info/charts/averageline.htm
>
> Cheers
> Andy
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> "Brad Stone" <brushyland@yahoo.com> wrote in message
> news:4766E76B.8550ECEE@yahoo.com...
> > Hi all,
> >
> >  I'm pretty knew to Excel VBA and charting.  I have a simple histogram
> > chart on a worksheet and the data ranges are 1 to 100.  So Excel
> > automatically puts the Y-axis tick marks at 0, 20, 40, 60, 80, 100.  But
> > I only want three tick marks to show on the Y-axis (0, 50, 100).  How
> > can I do that?
> >
> >  Also is there a way to put a horizontal line at the 50 y-axis tick
> > mark that will show on top of my chart?  My users want to be able to
> > quickly see the 50 line (i.e. which histogram bars extend above it, and
> > which ones don't reach it).
> >
> >    It would be great if I could programmatically do this in VBA code.
> >
> > Thanks!
> > Brad
> >

0
12/19/2007 3:48:30 PM
Reply:

Similar Artilces:

Excel 2003: Value Axis
The value axis seems to have default formatting of "percentage". I made it a number and it's now coming out to 1, 1, 1, 1, 0. My numbers are in the hundreds but how do I format it that way? ...

"check mark"
Is there any way to put a simple check mark next to text or data an Excel spreadsheet? Use one of the windings fonts. -- Regards, Tom Ogilvy Doug Wyatt <wyatts@adelphia.net> wrote in message news:062d01c3d2e0$511abd40$a401280a@phx.gbl... > Is there any way to put a simple check mark next to text > or data an Excel spreadsheet? For me (My PC Config) requires using Font: "Monotype Sorts", then entering either the number 3 for light-shaded chkmark or the number 4 for bold-shaded chkmark - also format cell(s) as Number, General. "Tom Ogilvy" <twogilvy...

how to switch the primary and secondary Y axis to opposite chart s
Using Excel 2007, evertyime I refresh pivot table data, my charts lose almost all of the formatting (ex. axis will disappear and chart type will revert from lines to columns). Most annoying problem I have when this happens is the primary Y axis moves from the left side to the right side of the chart, and when I re-introduce the secondary Y axis, it will appear on the left. How can I easily move the primary Y axis back to the left side of the chart or switch the two axes? I've tried choosing Layout - Axes to hide and re-display the axes but this did not work. Format the primary cate...

Primary/Secondary Axis series count
Hi, I have a VBA function like so: Code: -------------------- Private Sub AddSeries(ByVal strName As String, ByVal rngValues As Range, ByVal rngValuesX As Range, ByVal xlAxis As XlAxisGroup) With Charts("Chart").SeriesCollection.NewSeries .AxisGroup = xlAxis .ChartType = xlLine .Name = strName .Values = rngValues .XValues = rngValuesX End With End Sub -------------------- Sometimes I add a series to the Primary Y axis and other times to the Secondary. How can I determine how many series are on the Primary and how many on the secondary????? Applicat...

Inventory transaction batch marked to post to gl as default
This is an old request/thorn. The inventory transaction batches should default to post to the GL. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=da0b1810-f918-4ebd-a353-7...

X-Y Graph with Months, Not Date on Axis
I have a data set with dates in column A and values in column B. I have this plotted as an X-Y graph. I can change the major units on the X (date) axis, but only by integers. I would like for it to display months, rather than equally spaced dates. The closest I can do, is to use 30 days for the major unit on the X-axis. Is it possible to have each month displayed rather than specific date in 30 day increments? Bill Hi, Try the following approach; I think that it would work. If the lowest date in your data is not on the first of a month, create a dummy date which is the first of the low...

Don't print months on x-axis on pivot chart
My data is by month for 11 years - how can I tell pivot chart not to show the month names on the x-axis? I'd like to show the years with small tick marks for the months. -- Thanks! Cornelia ...

Seperate data series on a similar X-axis
I am using Excel 2000. I need to be able to plot two seperate curves: Engine Power and Drag. The two curves both have speed on the x-axis and power on the y-axis. The problem is that the two sets of data are presented with different values for speed. Speed drag (kph) (watts) 10 37.75 20 269.3 30 888.45 40 2089 50 4064.75 60 7009.5 70 11117.5 80 16581.2 Speed engine power (kph) (watts) 7.49 -36.65 14...

Marking used records
Dear friends, Suppose there are certain records which I don't want to use again accidentally, how can I mark a certain record automatically so that the next time when I see it I should remember that I have used it before? Color marking would be more helpful if you can help on that? Hope you will help. you can add a Yes/No field to your table, where Yes = the record has been used. or, if you'd like to know *when* a record was used, add a Date/Time field *instead of* the Yes/No field - where the date (with or without time) entered is when the record was used. hth "Techman&quo...

x-axis time interval
Trying to plot mortality rates per 5 year blocks, i.e. 1951-1955, 1956-1960. Can't get scatter plot to display those specific intervals. Rather, it simply labels the time points by the number cell in which the interval was typed, i.e. 1, 2. thanks A scatter chart needs numerical values. Try a line chart, which will treat the text "1951-1955" etc as labels. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John" <John@discussions.microsoft.com> wrote in message ...

Additional Y-Axis Area Chart
Anyone know how to add another y-axis to a simple area chart? Thanks a bunch! Jack ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Hi Jack - You need at least two series in the chart to show two sets of axes. Double click on one of the series, click on the Axis tab, and select Secondary. Now format everything so the area series on the secondary axis doesn't obscure the series on the primary. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities....

Clear Message Flag and Mark as Read not working
I've created a series of rules to get rid of the spam I've been getting. I have two rules, one moves certain e-mails to the junk e-mail folder and another that certain e-mails to the deleted folder (to be permanently deleted when I'm sure it works right). I also wanted the rules to clear the message flag and mark the e-mails as read but these don't seem to be working. The e-mails are being moved just fine. Any ideas? Outlook 2002. Thanks You need to clear the flags and mark as read before moving the message - do it using a rule that is ahead of the one that move...

x axis scale problem
I've got two columns of data - one is "years" and goes 1, 2, 4, 5, 10, 50, 100, 250, 500, 1000. When I graph it, I get an "equidistant" x-axis. That is, 1 is as far from 2 ans 500 is from 1000. I would like it to be "to scale". That is, 500 to 1000 takes up half the axis, and 1,2,4, 5, 10, 50 take up only 5% of the axis. How do I do that? Use an XY Scatter chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ rishi wrote: > I've got two columns of data - one...

How do I change the scale of logrithmic curve on the x-axis?
I would like to reduce the x-axis range on a logrithmic curve from 10 to 50, but I am restricted to a scale of 10 to 100? A logarithmic scale by definition goes up in decades. You have to use multiples of 10, by this I mean 0.01, 0.1, 1, 10, 100, 1000 etc. You cannot use anything else -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25803 View this thread: http://www.excelforum.com/showthread.php?threadid=465768 It's true that Excel's chart engine i...

Auto update a date field when a checkbox is ticked
Hi All Does anyone know how I can automatically populate a data/time field when a check box is ticked? This needs to be at record level. thanks Submitted via EggHeadCafe - Software Developer Portal of Choice Get Identity Values of Multi-row insert by using OUTPUT Clause http://www.eggheadcafe.com/tutorials/aspnet/03c8104d-7deb-425e-beb5-dd4f996da99f/get-identity-values-of-mu.aspx If you mean when the check box receives a check (rather than when it is cleared), in the check box After Update event: If Me.CheckBoxName = True Then Me.SomeField = Now() End If If you want th...

How do I get non-sequential dates on X-axis
I am trying to make a chart that tracks progress each week from Monday to Friday. i.e. June 5 to June 9, then June 12 - June 16. This goes on the X-axis. Each time I try and chart the series without the dates it works OK. As soon as I add the date, Excel automatically fills in the weekend days. I have looked at every option that I can think of and prefs but can't get it to just show the dates that I actually want to chart. Can someone tell me how I can force Excel not to show dates for which there is no data and there is no labels etc? Thanks in advance Using Excel 2004 for Mac...

How do I graph in 3-D, X,Y and Z
Is it posible to construct a 3-D graph with three different axes and three diferent data streams in Excel. If not, can anyone suggest any software packages that will allow me to do this! Hi, Excel does not support true 3-d charts. You can fake it with some worksheet formula. http://www.andypope.info/charts/3drotate.htm Fernando Cinquegrani's http://www.prodomosua.it/ppage02.html SigmaPlot http://www.systat.com/products/SigmaPlot/productinfo/?sec=1003 Mathematica http://documents.wolfram.com/v3/index.html Cheers Andy Uncle Albert wrote: > Is it posible to construct a 3-D graph w...

How to set up a "ticked" rows
I am an Excel newbie and want to select say 100 rows at various positions down a 5000 row table then copy these to a new sheet. Rather than copy and paste each one I would like to set up a "tick" cell column in each row so that somehow I can then automatically select all the ticked rows then do a copy and paste. How do I do this? regards, Beemer Insert a column at A and set the font to Marlett. Put an a in each row of A to copy then add this code and run it Sub CopyData() Dim rng As Range Dim lastrow As Long With ActiveSheet lastrow = .Cells(.Rows.Count, &...

Alternate axis at top of chart
i have a number of X-Y scatter plots where I need to have either only an alternate abscissa axis at top of chart or alternate ordinate on right and abscissa on top. I can easily do the alternate ordinate but can't find a way to do the alternate abscissa. How do I do it. the only option I see at moment is to redraw graphs in Visio and then add the axises. I prefer to use Excel XP but can use 07 if need be. Thanks in advance for any suggestions! What you need are secondary axes, and you need multiple series in order to get secondary axes. Right click one of the series, choose Forma...

Category axis label problem at column chart
Dear experts! I have a column chart. Category axis labels are either time ("hh:mm") or empty ("") cells. If first value in the label range is time, e.g. ("13:00"; ""; "14:00"; ""), everything is fine, in the opposite case, e.g. (""; "13:00"; ""; "14:00"), labels are not displayed. It seems that the chart calculate required space for labels according to the first value in the label range. Is there any way to solve this problem, so labels will be displayed regardless of the fact if first...

Bar graph with percentage of total marked inside each bar
I'm trying to create a chart in Excel but I can't seem to figure out how to make the data work for me. I need to create a bar graph with each bar with a "goal" amount, and the percentage of the goal reached within the bar. There are two values where the goal has been exceeded and I wanted to reverse the chart for this - mark the total raised percent and the total of the goal within the bar. It's hard for me to explain without visuals but I seem to be getting somewhat close to this, I just can't quite make it work. Any advice? ...

Two Y axis with different scales for a line chart
I have to plot a line graph with two Y axis. The primary Y axis is normal scale ranging from 10 to 60 with interval of 10. The secondary Y axis needs to correspond to the primary axis but in log scale, i.e. from log10 to log60 with interval of log10. This question actually comes from a request to display the same data in different unit. Primary axis displays the data in Watt, whereas the secondary axis using db, which is log scale. I have tried to create a dummy series, but find it difficult to align the two y axis. Also, excel does not seem to be able to display a maximum of log60. -----...

Bubble Graph where Both Axis are Categories
Hi there, I would like to use a bubble graph where the two axis are categories and not values. Only the size of the bubble will depend on values. Can anyone tell me how this works? Thanks for any hints! ...

numbers on x axis
Hello all, I'm kinda new to this, but I'm learning, so please bear with me. My question is this. I am trying to set up a xy scatter chart to plot points of a test that I take for my job. Only thing is, I would like to change the x axis to read 5, 10, 15, 20, 25, 30, 40, 50, 60. This is the interval at which I take the reading of my test, 5 minutes, 10 minutes, etc. I put the readings in column A1 thru A9. The reading are correct, but the numbers in the x axis read 0, 2, 4, 6, 8, 10. I would like to put A1 on the 5 minute mark, so on and so forth. I am running Microsoft...

Document extra check was printed and mark it void
I'm trying to figure out how to tell GP8 that an extra check was printed but we need it to be void. We printed checks today and accidently printed 1 more than we should have. Since we used that check number, GP will move on to the next one. We want to be able to go into GP and document that we did print that check but it should be void. Does that make sense to anyone? Did that exra check print on a check or on blank paper. Do you want to re-use that check number? I'm not 100% with GP 8, but in 10 you just go to "Void Historical Transactions" and void the check. ...