Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

MS Excel (Minor Gridlines)

  1. Oct 16, 2007 #1
    Hey all,

    I have a quick question about the minor gridlines in MS Excel. I have points that differ by
    .00001 on the graph. However when I type this in, Excel automatically changes it to
    .000036 every time. I wasn't sure if anybody knew if Open Office could solve this problem or if its just a useless battle.

    Thanks in advance,

  2. jcsd
  3. Oct 16, 2007 #2


    User Avatar
    Science Advisor

    Are you using an X-Y line plot or a X-Y scatter plot? Make sure it is a scatter plot. Never use the line plot (I have no idea why they insist on including it as an option).
  4. Oct 16, 2007 #3
    Yes, I am using the X-Y scatter plot.
  5. Oct 17, 2007 #4


    User Avatar
    Science Advisor

    Can you share some of the data so we can replicate the problem? I have never seen Excel do this. That definitely doesn't mean there's no bugs in there.

    I just tried some junk data I made up and had no problem setting the minor plot divisions to .00001.
    Last edited: Oct 17, 2007
  6. Oct 17, 2007 #5
    Ok so this is a projectile motion problem and the y axis represents displacement, "s" and the x axis represents t^2/2 where "t" is time in seconds, and s is in meters.

    s1= -0.385 t1= .27
    s2= -0.352 t2= .26
    s3= -0.318 t3= .24
    s4= -0.286 t4= .23
    s5= -0.257 t5= .22
    s6= -0.240 t6= .21

    I then use an equation in excel for it to caclulate t^2/2 for me automatically. =((B1^2)2)
    I did this for each time. The results i got were this

    t^2/2 1. 0.03645
    t^2/2 2. 0.0338
    t^2/2 3. 0.0288
    t^2/2 4. 0.02645
    t^2/2 5. 0.0242
    t^2/2 6. 0.02205

    Then I used the X-Y Scatter to make my graph which was fine. And then I added a linear trendline which was also fine; but when I tried to set my gridlines at .00001 they didin't work.

    I hope the data helps a little.

  7. Oct 17, 2007 #6


    User Avatar
    Science Advisor

    I see what you mean. However, what I am wondering now is really why you need that kind of resolution in the grid lines. None of the data you provided had a delta of .00001. Granted you may not have given all the data, but do you really need to have minor divisions that small?

    It is definitely a resolution issue. If you narrow the overall range on the axis to a smaller value, you can get the minor divisions to what you are looking for. Perhaps Excel has a built in check to allow only so many minor divisions per overall scale.
  8. Oct 17, 2007 #7
    That was all the data I was given. I wasn't there for the class becasue I was ill but I figured I'd do the graph as an attempt to make up some work. My teacher wants each point on the graph to be crossed by both a minor x and minor y gridline. That's the only reason why I was trying to get them that small.
  9. Oct 17, 2007 #8


    User Avatar
    Science Advisor

    That is a silly request from a teacher. There's no need for that. That is like saying that you want all of the T's in your paper to have little curls at the end. It makes no difference in the data.

    I don't know what to tell you on this one. It looks like a limit in Excel itself. Perhaps someone else here can clue us both in.
  10. Oct 17, 2007 #9


    User Avatar
    Science Advisor
    Homework Helper

    I don't know Excel well enough to try your sample.
    Most microsoft drawing objects (like grid) have a link to screen resolution.
    So for a particular screen resolution there will be a minimum setting for grid lines.
    You could test this idea by choosing a lower screen setting and see if the number gets larger.
  11. Oct 19, 2007 #10
    Im new to physicsforums, thought i could help.
    I tried your data and sure enough i got the same problem, but the value to which excel changed the minor gridlines was different, and a little fiddling around later, i got the reason.
    Its related to the "span" of your graph. Try limiting the values of the minimum and maximum values of x and y on the graph so that the range covered is less,and you'll see that excel rounds it off to a lower value. It seems as if excel changes the value automatically to the minimum it can display (it needs atleast this much separation between the gridlines).

    Hope this helps

Share this great discussion with others via Reddit, Google+, Twitter, or Facebook