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

Question

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?

Answer 1

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.

Answer 2

Another options is to use scatter graph with line.

1. Organize Your Data: Ensure your data is organized in two columns, one for Year and one for Quantity.
2. Insert a Scatter Plot:

Highlight the data range. Go to the Insert tab on the ribbon. In the Charts group, select Scatter, and choose Scatter with Straight Lines. Adjust Axis Options:

3. Click on the x-axis to select it. Right-click and choose Format Axis. In the Axis Options pane, ensure the Axis Type is set to Text axis. This will treat the x-axis labels as text, spacing them evenly regardless of their numerical value.
4. Adjust Major Unit:

Still in the Format Axis pane, adjust the Major unit under Axis Options to control the interval between ticks.