3

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?

  • no, unfortunately you can't get unevenly distributed axis labels in any charts in Excel – Máté Juhász Feb 16 '18 at 14:36
  • you could create a 'fake' sub-set of data using LINEST, and have the chart populate of this subset – PeterH Feb 16 '18 at 15:29
5

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

  • Could you please explain how to perform step 4 in excel please? I am trying to create a similar graph and don't know how to remove labels and ticks. – MRT Oct 1 '19 at 10:52
  • Added additional info for step 4. If that answers your question, I'd appreciate an upvote. Good luck. – dav Oct 1 '19 at 13:35
  • Ahh yes that was much more clear. I have upvoted your answer. Thank you for your reply. – MRT Jan 7 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.