Excel: making a chart with non-evenly spaced years on the x-axis

chartsmicrosoft excel

I have data in Excel that looks like this:

Year            1857    1869    1880    1890...
Population      1970    2328    2556    3171...

As you can see, the years aren't equally spaced, and I want that my chart take that into account (years on x-axis, population on y-axis).
Line chart didn't work for me (because years get evenly spaced on the x-axis), so I tried with the XY Scatter plot with smooth lines and markers.

That chart does well with the space between years (there is more place between markers if there is a bigger gap between years) but it makes the x-axis look like

 1840 1860 1880 1900...

but I want it to look like

 1857 1869 1880 1890...

I can adjust the gap between years from 20 to a smaller/bigger value, but I can't get the exact values.

Is there a compromise between these two charts?

Best Answer

You can't specify specific dates in any Excel axis, at best you can define the start/end/interval of the axis markers. But, you can create a custom axis to represent the values you want shown.

  1. Ideally, convert your data into an Excel Data Table (not required, but will generally make life easier for you).
  2. Add a "helper" column (or row, if your data stays in your original format) named horizontal_labels with all 0's.
  3. Add another data series horizontal_labels to your chart with the following values:
    X values = Year
    Y values = horizontal_labels
  4. Format the default horizontal axis to remove labels and ticks.
    • Click on the axis to select it.
    • Right-click on the axis and select Format Axis from the context menu.
    • In the Format Axis menu, choose Axis Options (icon looks like a 3 series column chart).
    • Expand Tick Marks and set Major type and Minor Type both to None.
    • Expand Labels and set Label Position to None.
    • Each change should take effect immediately, so you can see the changes step-by-step.
  5. Add Data Labels for your horizontal_labels data series, and format them to show the X value, below the marker.
  6. Format to taste.
  7. Bonus, if you want to add custom vertical gridlines to match your dates, just use Y Error bars for your horizontal_labels data series, formatted to taste.

enter image description here

Related Question