Here’s a little Excel charting hack (with a screencast) that will baffle your colleagues, confound your enemies, and provide useful information display. Sometimes you want to graph related series on two axes without confusion about which axis belongs to which series. Something like this:
For details on how this is done, watch the screencast.
The approach comes down to two techniques:
- Extend the ranges of the primary and secondary axes to compress the secondary axis values to the bottom of the chart and the primary axis to the top of the chart.
- Use conditional custom number formats to hide values on the axes. For instance a number format like: [>1]"";0% displays a blank string ("") if the number is greater than 1 and uses the "0%" format otherwise.
Have I mentioned there’s a screencast? Have fun.