Excel 2007 - Custom Y-axis values

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?

2 Answers

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:

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:

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.

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:

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:

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`.

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.

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:

• @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? Apr 12 '11 at 23:50
• @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? Apr 13 '11 at 23:13
• @nhinkle I'd like to change each column individually. Apr 14 '11 at 4:48
• @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. Apr 14 '11 at 5:20
• -1 for no hand-drawn circles. Apr 15 '11 at 0:31

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