Use text as horizontal labels in Excel scatter plot

12,304

You need a helper series. Consider the following screenshot:

enter image description here

The blue dots are plotted from the data in the table on the left. The orange dots are plotted from the helper table. Each orange dot then has a data label assigned. Edit each data label individually, type a = character and click the cell that has the corresponding text.

This process can be automated with the free XY Chart Labeler add-in. Excel 2013 and newer has the option to include "Value from cells" in the data label dialog.

Format the data labels to your preferences and hide the original x axis labels.

Share:
12,304
Jamgreen
Author by

Jamgreen

Updated on December 02, 2022

Comments

  • Jamgreen
    Jamgreen 11 months

    I am trying to make a scatter plot in Excel with text as horizontal axis labels.

    I have the data

    enter image description here

    and I want a chart like this

    enter image description here

    The way I produced this chart was to have a scatter plot with numeric X values and numeric Y values, so instead of Poland I have X=1 and Y=10, Y=11, Y=12, and Y=9. For Germany I chose X=2 and Y=8 and Y=7. I just added some text boxes with the names to hide the numeric X values.

    enter image description here

    But I want this to be dynamic, so I tried to set the X value labels here:

    enter image description here

    but I am not able to change the horizontal values.

    How can I solve this problem?

    • Tom Sharpe
      Tom Sharpe over 6 years
      If you only have two or three countries to display, you can use x-values of (say) -1,0 and +1 and a custom format of (say) "Germany";"Poland";"Other" for the x-axis. I haven't offered this as an answer because I don't know a way of extending it to more than 3 x-values. If it is of interest, I can give more details.
    • Jamgreen
      Jamgreen over 6 years
      Unfortunately, I need it for a lot of countries, so I had hoped for a solution where I can add a range of country names to be used at certain X values
    • Tom Sharpe
      Tom Sharpe over 6 years
      I believe you can use custom formatting on the data and then choose 'link to data' for the axis. Will try and work out something more general.
    • Tom Sharpe
      Tom Sharpe over 6 years
      I don't think it's going to work - you can link to manual formats (at least in my version of Excel 2010) but not to conditional formats.