none
Text labels on Scatter plot X axis

    Question

  • Is it possible to put text labels on the X axis of a scatter plot?  Well, actually, I'm trying to use a bubble plot but the same issue applies.  No matter how hard I try I cannot get the labels to come out as text labels.  The labels are numeric but they should not be treated as numbers.

    I have data that looks something like this:

    ColA ColB ColC Diff B&C
    55555 50000 54000 4000
    55566 25000 26000 1000
    55544 10000 12000 2000
    55588 30000 36000 6000

    And I want to display ColB and ColC as scatter points (each a different series) for each of ColA with the bubble size being regulated by the Diff B&C column.  Does this make sense?  Is this possible?


    Click Start, click Run..., type "cmd" and hit enter, type "work /now"

    Friday, March 25, 2011 9:55 PM

Answers

All replies

  • You will have to use a dummy series and data labels to mimic a category x axis.

    http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

     

    To link the data series labels to cells use this free addin

    http://www.appspro.com/Utilities/ChartLabeler.htm


    Cheers,

    Andy
    www.andypope.info

    Tuesday, March 29, 2011 9:21 AM
  • Hi,

     

    Thank you for using Microsoft Office for IT Professionals Forums

     

    I’m sorry but I don’t quite get what you mean.

    Is the illustration below shows what you want to do?

     

     

    Neither of those quite does it.

    If you put Col A, B, & C into a column chart with ColA as the x-axis values and ColB & C as y-axis values (with B & C being different series) you will see that the x-axis labels match exactly the values in ColA, even the order is the same.  This is the behavior I am trying to mimic in the bubble plot.

     

    You will have to use a dummy series and data labels to mimic a category x axis.

    http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

     

    To link the data series labels to cells use this free addin

    http://www.appspro.com/Utilities/ChartLabeler.htm

    This comes really close to what I want!  Unfortunately, it requires an addin to work.  I don't really want to have to use addins.



    Click Start, click Run..., type "cmd" and hit enter, type "work /now"
    Wednesday, March 30, 2011 2:00 PM
  • Hi

    Given the functionality of Excel only.

    Firstly, the order will always be in ascending order unless you have used the options to display the axis in reverse order, given the type of chart you are using. So the exact order of Column A cannot be matched.

    Secondly the values in Col A are not evenly distributed. So it is not possible to have just the values from this column as axis labels, given the chart type you are using.  The best I managed with your data was to set the axis minimum to 55533 and set the major tick interval to 11.  You will always need additional points as the tick marks in this case form the centers of the bubbles.

    You can suppress unwanted axis labels using a drawing shape formated appripriately and positioned over an unwanted label. But this obviously cannot be done automatically/easily with changing data.

    Sorry but this is all I can offer.


    G North MMI
    Wednesday, March 30, 2011 3:04 PM
  • The addin is only required to easy the task of linking data labels to cells. Note that only you need the addin, once the connection is made the addin is not required by you or anybody else using the workbook.

    You can do the linking manually. The instructions are for chart title but the techinque works for all chart text items.

    http://www.andypope.info/tips/tip001.htm


    Cheers,

    Andy
    www.andypope.info

    Saturday, April 02, 2011 10:06 AM
  • Using a line graph rather than a scatter works
    Friday, December 02, 2011 6:10 AM
  • You can actually use line graph and get the text names in x axis. Then, select each line on the graph, right click and go to format data series. Then select line color --> no line to remove lines. After removing all lines, you should end up like a scatter graph. 

    Hope it helps,

    Gurkan

    Thursday, January 05, 2012 11:51 PM
  • For a very good example of the power of scatter plot check out http://www.launchexcel.com/timelines-in-excel-bonus by Victor Chan

    Each point has a custom label. Click on a single point label -  you can add a cell reference e.g. "='Sheet1'$A$1" I assume text as well.

    He uses both X and Y error Bars (X error bars are a specified cell range)

    Regards,

    Erik

    • Proposed as answer by CubedFluidity Thursday, July 12, 2012 11:21 AM
    Thursday, July 12, 2012 11:21 AM