How to create a text based Y axis on excel chart

Refresh

February 2019

Views

23.7k time

1

I wish to customized the Y-axis to text instead on value.

Is there any way I can do it without any plugin?

Here will be my data:

        A             B            C          D
 1 Lower Limit   Upper Limit       #         Data
 2  30S               40S         A1234       30A
 3  30S               40S         A2345       30S    
 4  30S               40S         A1256       30S

I wish my Y axis will be 30S, 30A, 40A, 40S intead of 10,20,30,40
and my X axis will be A1234,A2345,A1256

Is there any possible that I can change the X axis value?

1 answers

4

I started with your data, and I added a small lookup table. I inserted a column "Value" (blue text) in your data range that has the Y value corresponding to the label in your "Data" column. You can use a lookup formula for this, for example, the formula in D2 is:

=INDEX($H$2:$H$5,MATCH(E2,$I$2:$I$5,0))

I made a column chart (don't know what type of chart you want, but this technique is widely applicable) using the yellow shaded range in the first data range (top left chart). I selected and copied the green shaded range in the second data range, selected the chart, and used Paste Special to add the data to the chart, using the settings shown in the dialog overlying the top right chart.

This has added another set of columns (bottom left chart), not so useful. I right-clicked the added series, picked Change Series Chart Type from the pop-up menu, and changed just this series to an XY scatter type (bottom right chart).

First half of protocol

The XY scatter series is on the secondary axis, also not too useful. I formatted the XY series, and changed the axis to Primary (top left chart below). The 0.5 values in column G make the points line up with the vertical axis of the chart.

I added data labels to the XY series, shown in orange to distinguish them from the black axis labels (top right chart below). I formatted the labels so they appear to the left of the points, and so they used the Value from Cells option, based on the labels in I2:I5 (middle left chart below). If you don't have Excel 2013, you don't have this option, but you could

  • Manually type the desired text into the labels.
  • Link each label to the desired cell: select the labels (one click), select the desired label (second click), type = in the formula bar, select the cell, press Enter, repeat for all labels.
  • Use a third-party add-in like Rob Bovey's free Chart Labeler from appspro.com.

I hid the default axis labels. You can select None for axis labels, and then resize the plot area to make room for the custom labels created above. But I used a custom number format for the axis, which consisted of several space characters enclosed in double quotes. See middle right chart below.

Finally I rescaled the vertical axis so its min and max were 0 and 40 and the major spacing was 10, I formatted the XY series to use no markers, and I changed the label font color back to Automatic (bottom chart).

Second half of protocol