Excel Third Axis Tool
Frequently, I find myself wanting to compare three or more data sets in an Excel chart that have widely different ranges and orders of magnitude or both. Because (as far as I know) Excel only provides two axes, you can run into an issue in that if you set those axes up to make two of the data series visible, then the third data series is either a flat line or off the scale on either axis.
Before I came up with a way to create a third axis, I would use a scaling factor with the data set I could not fit on another axis and then call out the scaling factor in the data set name. For instance, if I had to divide air flow by 1,000 to get it to show up in a meaningful way on a chart that was also displaying air system temperatures and air system static pressures, I would label the air flow axis as "Air Flow/1,000" or "Air Flow Divided by 1,000".
But at some point, I realized that I could create a special axis by plotting a data series so that it showed up as an appropriately scaled and located vertical line on the chart, just like the "Number of Chillers" axis in the chart above. In reality, the data is still being scaled and plotted against one of the other axes. But the "third axis" gives you a way to read the data directly with out having to do some extra math, so it seems cleaner, at least to me.
This spreadsheet contains the set-up I use to do that. The data in it is a simple example I set up to support the blog post I wrote about how to go about creating a third axis. But you could take this spreadsheet and insert it as a tab in a working spreadsheet that you have and rework some of the formulas and references to point to the data you wanted to plot on your third axis instead of the example data set and it would potentially save you from "reinventing the wheel". I use it that way myself.
Or, you may just want to download it along with a copy of the basic data set so you can follow along and learn the technique by doing it as you read through my Creating a Third Axis in Excel Blog post. The file on the left is the full spreadsheet tool, including the data set which would allow you to just run with it or reverse engineer it. The file to the right is the basic data set, which would let you start from scratch and build the tool as you read through the associated blog post.
Before I came up with a way to create a third axis, I would use a scaling factor with the data set I could not fit on another axis and then call out the scaling factor in the data set name. For instance, if I had to divide air flow by 1,000 to get it to show up in a meaningful way on a chart that was also displaying air system temperatures and air system static pressures, I would label the air flow axis as "Air Flow/1,000" or "Air Flow Divided by 1,000".
But at some point, I realized that I could create a special axis by plotting a data series so that it showed up as an appropriately scaled and located vertical line on the chart, just like the "Number of Chillers" axis in the chart above. In reality, the data is still being scaled and plotted against one of the other axes. But the "third axis" gives you a way to read the data directly with out having to do some extra math, so it seems cleaner, at least to me.
This spreadsheet contains the set-up I use to do that. The data in it is a simple example I set up to support the blog post I wrote about how to go about creating a third axis. But you could take this spreadsheet and insert it as a tab in a working spreadsheet that you have and rework some of the formulas and references to point to the data you wanted to plot on your third axis instead of the example data set and it would potentially save you from "reinventing the wheel". I use it that way myself.
Or, you may just want to download it along with a copy of the basic data set so you can follow along and learn the technique by doing it as you read through my Creating a Third Axis in Excel Blog post. The file on the left is the full spreadsheet tool, including the data set which would allow you to just run with it or reverse engineer it. The file to the right is the basic data set, which would let you start from scratch and build the tool as you read through the associated blog post.
|
| ||||
