Change the spacing of major gridlines in Excel to custom number and add label

Question

Hi all, I would not consider myself a power user of excel but nor am I a newbie. I have been trying to create a line chart of an elevation profile that includes points at a distance along the profile line and the elevation value at that point. The distances are floating point and automatically generated by a GIS software program. So the first point is at 0 distance, and an elevation of 0.22m. The second point is at 1.58453 distance and elevation 0.08m. It goes on like that with random distances along the line and associated elevations to a distance of about 1,400m.

When I try to create a chart in excel, I am unable to set the major gridlines (and associated labels) for distance at 100, 200, 300, etc. When I try to format the gridlines I simply get width and color options for the line themselves. When I choose to include major gridlines it seems to include hundreds of lines. I can't for the life of me find a resource online that explains how to manually set the major gridline spacing based on the distance along line but at an interval of my choosing.

This seems like such a simple task but I'm stumped. I've tried both Excel desktop 2013 and 2019. I simply don't see any option to change the spacing of the gridlines to a custom value (within the range of the data). Any help is greatly appreciated.

Answer 1

Use a XY-Scatter chart instead and set the Mayor Units in the Axis Options of the Y axis to 100 if necessary.

Andreas.

Answer 2

Hi,

Here is how you control gridlines

1. Choose the Axis (here the Y or vertical axis)
2. In the Format Axis window on the right click Text Options
3. The Minimum and Maximum control the bottom and top of the Axis
4. The Units, Major controls the spacing of the major gridlines (the only ones showing here.

In other words, the Axis Options, not the Gridline Options controls their spacing.