Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Vertical Category Axis.

How do you arrange your chart so the categories are displayed along the Y axis? The method involves adding a dummy series along the Y axis, applying data labels to its points for category labels, and making the original Y axis disappear.

Start with your data arranged like this:

     Category Name     X Value     Y Value   Dummy X
       0 to 5 mph        15          0.5        0
      5 to 10 mph        14          1.5        0
     10 to 15 mph        12          2.5        0
     15 to 20 mph         9          3.5        0
     20 to 25 mph         4          4.5        0

Plot the values in 'X Value' and 'Y Value' in an XY Scatter chart. Scale the Y axis from 0 to 5, by steps of 1. See Chart 1.

Add a series using 'Dummy X' (the zeros) as X and 'Y Value' for Y. The easiest way is to select the first series, and copy the series formula from the formula bar. This formula looks like this:

=SERIES(Sheet1!$C$1,Sheet1!$B$2:$B$6,Sheet1!$C$2:$C$6,1)

Select the chart's plot area, click in the now-empty formula bar, paste in the copied formula, and change $B in this formula to $D:

=SERIES(Sheet1!$C$1,Sheet1!$D$2:$D$6,Sheet1!$C$2:$C$6,1)

Excel adds this new series along the Y axis. This will serve as our dummy Y category axis. See Chart 2.

Shrink the chart's plot area widthwise, so there is a wider margin at the left side for category labels (you can adjust this later if need be). See Chart 3.

Now you need Rob Bovey's XY Chart Labeler, a free and absolutely must have add-in available from http://www.appspro.com. It's compatible with every version of Excel since 97 (and I think there's also an earlier version, but even I use 97). Use the Labeler to add the 'Category Names' as the data labels for this dummy series you've just added, aligned to the left of the points. See Chart 4.

Now format the original Y axis so the tick labels are not shown, and do what you want with the tick marks. Also format the dummy series either to disappear ('none' for line and marker format), or use crosses as markers to simulate tick marks. Delete the extra legend entry (single click on it twice so only the one entry is highlighted, then press Delete), and you're done. See Chart 5.

Y_CategoryAxis.zip is a zipped Excel file with an easy demonstration of these steps.

Dot Plots

I've recently added a new page, Dot Plots, which has a different approach to creating a vertical category axis. It still needs a dummy series, but labeling is easier, because the dummy series is a bar chart series, and it comes with a vertical category axis. We use this axis and hide the XY chart's vertical value axis. This might make the Dot Plot approach preferable to this one.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile