1

I am making a scatterplot with the following data:

Date Value1 Value2
3/19/2024 5.31 45
4/15/2024 4.91 44
4/29/2024 4.93 44
5/13/2024 4.97 45
5/27/2024 4.71 42
6/9/2024 4.45 40

I created a scatter plot and the gridlines are regularly spaced, so the date labels don't align with the data points:

enter image description here

I can remove the axis labels and label the points:

enter image description here

But what I really want is plot with x-axis labels that correspond to the date for each point, and ideally a gridline as well. I want to preserve spacing that is proportional to the actual date; I don't want equally spaced gridlines that are just labeled with the correct date.

Is this possible?

Improve this question

1 Answer 1

Reset to default
0

The short answer is No, in Excel chart gridlines always follow either the major or minor units established by the Axis.

But, with Excel, there's usually a work-around. To accomplish what you're looking for, you'll need to add a helper column and create an additional series to replace Excel's X/horizontal axis.

  1. Add an additional helper column for the Axis and populate it with all 0's (or the minimum value you anticipate, you could also use a formula to return the minimum value of your data series) The value must match, or exceed, the minimum Y/Vertical axis value otherwise you won't see the labels when you're done.
    helper column

  2. Add this column to your chart as a new series.
    added series

  3. Add Error Bars to your Axis helper column. Change the value to the maximum value you anticipate (or again, you could use a formula to dynamically change to the maximum in your data). Note that when you add error bars, Excel automatically adds both X and Y bars. For this application, just select the X bars and delete them.
    added error bars

  4. Format to taste. Remove the lines/labels from the original Excel axis. Add data labels and format lines for the Axis series and Error bars.
    final

Improve this answer
3
  • I tried your solution and it mostly works. I get small marks around the points used to generate the error bars. They are not point markers, they're something associated with the error bars. You can see the effect here . The error bars are set to solid line, no end cap, direction plus-only, custom amount, flat cap, round join, no arrow heads
    – Llaves
    Commented Jun 13 at 19:52
  • Those look like X error bars, where your seeing the line and top half of the negative and positive end caps. When you create error bars in Excel, it automatically adds both horizontal and vertical bars (regardless of whether you want them both). You can delete them by either selecting them directly by clicking on them, or choosing them from the drop-down menu in the Format side bar.
    – dav
    Commented Jun 14 at 12:48
  • 1
    That was it. Never occurred to me Excel added X error bars. Deleted them and now I'm good to go.
    – Llaves
    Commented Jun 14 at 22:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .