Excel 2007 - Custom Y-axis values

9

2

I am making a basic column chart with a few columns. The only catch is instead of showing numbers as the grid marks, I want to show words like "Very Low" and "Medium".

For example, I have

Data1 = 0.5
Data2 = 0.3
Data3 = 0.8

So that would be 3 columns. Then I want Y-axis marks according to the following scale:

0.1 = Very Low
0.25 = Low
0.5 = Medium
0.75 = High
0.9 = Very High

Any way to do this in Excel 2007?

Wesley Tansey

Posted 2011-04-09T23:45:23.040

Reputation: 233

Answers

18

There is no official support for this in Excel; however, there is a hack to make it work using a scatter plot. This method is a bit complicated, but does not require an add-on like the other answer. I figured this out using the info from here, but doing a different method to make it work with a column chart.

Essentially, the way this works is that you create a data set which corresponds to the category labels you want to use. You set the x values to 0, and the y values to the height you want that label to be at. Then, you hide the markers and add data labels to those points. This is relatively straight-forward for a pure scatter plot, but when combined with a column graph, gets very tricky. I finally figured it out after a lot of experimentation. I'll try to give step-by-step instructions here; comment if any of the steps are unclear. Here is what the final graph will look like:

arbitrary y-axis values column graph

Add the following to your worksheet, with the labels for each category, x values of 0 (you will adjust this later), and y values for how high you want the labels to be.

x      y      label
0     0.1     Very Low
0     0.25    Low
0     0.5     Medium
0     0.75    High
0     0.9     Very High

Create a blank scatter plot by going to Insert > Scatter. You will have a blank graph. Click on Select Data in the ribbon. You will get the following dialog:

select data dialog

Now you need to add each of the lines in your x/y/label table as a separate series. Click Add..., then choose the value from the Label column as the series name, the value from the x column for the Series X Values and the value from the y column for the Series Y values.

selecting series

Repeat this for each line. Each line must be its own series that you add by clicking the Add... button.

Once you've done this, your graph should be looking like this:

scatter plot

Now, plot your column graph in a separate graph the way you normally would, by selecting the data, then choosing Insert > 2-D Column Chart.

Select the scatter plot, and copy it by pressing Ctrl+C. Select the column chart, and press Ctrl+V to paste. This will convert the column chart to a scatter chart.

Right-click on the x-axis for the plot, and choose none for axis labels and major tick marks.

Now, under the layout tab on the ribbon, choose Left under Data Labels. Then, for each of the label series, right-click on the marker and choose Format Data Series. Under Marker Options, choose none. Then click on the data label. Check the box to show the data series name, and uncheck the box to show the Y value. Do this for each of the series with your high/medium/low labels.

Once you have completed this step, your graph should look like this:

excel graph

Now to convert it back to a column graph for your primary data. Right-click on the series that was originally your column chart, and choose Change Series Chart Type.

change series chart type

Now select 2D Column from the resulting dialog. Your graph should now look like this. All we have left to do is tidy things up a bit.

partially complete graph

First, remove the legend by clicking it and pressing Del. Next, remove the gridlines by clicking on them and pressing Del. Then, right-click on the x-axis and choose Format Axis. Under Axis Options, set "Vertical axis crosses" to "at category number" and set that number to 1. Close the properties dialog. Now, adjust the x-axis value for the labels in the table you created at the beginning until the labels are next to the axis. 0.5 worked for me. You can adjust the first series' value until it looks good, then adjust the remaining ones by dragging that cell's value down.

Finally, click on the graph area and use the resizing squares to make the dimensions look good. Now, you can add a graph title, axis titles, and whatever other info you want. You can also remove the data labels from the column chart if you would like. Your chart should now look as it did in the first screenshot, with the categories on the y-axis and your column chart displayed:

excel special y graph

nhinkle

Posted 2011-04-09T23:45:23.040

Reputation: 35 057

@nhinkle Thanks! Is there any way to get grid lines corresponding to the low/medium/high labels? Also is there any way to get the columns to be different colors? – Wesley Tansey – 2011-04-12T23:50:39.577

@WesleyTansey, I believe there is probably a way. Before I edit the post to include that info, could you clarify - do you want to change the color of all the columns, or do you want to change each column individually? – nhinkle – 2011-04-13T23:13:27.110

@nhinkle I'd like to change each column individually. – Wesley Tansey – 2011-04-14T04:48:31.933

@WesleyTansey that's not the way column charts are usually formatted, but if you want to, click once in the chart area, then a second time on the individual column. Right-click and choose "Formant Data Point", then you can change the colors. I will update my post with info about adding gridlines in a little while. – nhinkle – 2011-04-14T05:20:20.333

-1 for no hand-drawn circles. – Hello71 – 2011-04-15T00:31:00.383

1

Excel doesn't support it simply. There's a way of fudging it though. It requires a free addon.

Explanation here: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html

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

Rhys Gibson

Posted 2011-04-09T23:45:23.040

Reputation: 4 218