How to create excel line charts with irregular scales

Question

I am trying to create a line graph, The vertical axis is an irregular scale - 1,1c,1b,1a,2,2c,2b,2a and so on.

Here is the data in chart format

I am having trouble representing this data in the vertical axis.

The chart should look like the below but with the correct scale

Note: I am using Office for Mac 2011

Answer 1

Disclaimer: This was done on Excel 2010 for PC. Everything should work on your Office for Mac version, but I can't test it. Also, this solution requires an additional free software utility-its absolutely worth it.

As you've already discovered, Excel generally doesn't like charting things that don't add-up like integers. If you don't mind a little extra work and trickery, you can create a chart like you've described.

1. You'll need to add a small table that translates your "scale" values into a numeric value. In my example, I have two helper columns Scale (your values) and Value (numeric equivalents). These can be anything you want, as long as they match one-to-one with your chart values.
2. Convert your "scaled" values from your Original Table into a matching Plotted Table of numeric values. I just used a simple =Vlookup to find the equivalents and return them to a second, matching table.
3. Chart the values from the Plotted Table.
4. Fix some formating on the chart-specifically, specifically:
   • make sure that the Vertical Axis Max Value equals your max numeric value (Excel's default is to calc something a little bigger which will throw your charted values off)
   • clear all other formating (e.g. line, ticks, labels, etc...)
5. Add another series, based off of the Values column in your spreadsheet. This should include all of your numeric values, 1-12 in this example. It'll look weird for a minute-don't worry.
6. Change your Value data series chart type to XY (Scatter), then edit the series so that the X Value is the Dummy Column and the Y Value is the Value Column.
7. Download Rob Bovey's XY Chart Labeler (XY Chart Labeler). He does have a Mac Version.
8. Use the XY Chart Labeler to select your Scale column as your Value series labels, set to the left of the series points.
9. Clean up any other items to your satisfaction.