Excel Dashboards
Books at Amazon.com 
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 nowempty 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 addin 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.

Peltier Technical Services, Inc.Excel Chart AddIns  Training  Charts and Tutorials  PTS BlogPeltier Technical Services, Inc., Copyright © 2014. All rights reserved. 