Creating a Third Axis In Excel

Authors Note 2021-04-17:   I made some improvements to the third axis tool that is mentioned in this post and uploaded it to the page associated with it on the Commissioning Resources web site.  Mostly, I improved the instructions to make it more user friendly.  But it also now includes a more practical example in the form of a chiller kW per ton profile.

One of the challenges that came up when I was creating the time series graph of a 9,000 ton chiller plant load profile that I show in my previous post was that I wanted to plot data series that had numbers in them with very large differences in the order of magnitude.

In other words, to get something visually meaningful¹, I needed to plot:

• Temperatures that would all fall into the range 0-100°F against
• Tonnages and flow rates that would fall in the 0 – 15,000 gpm/ton range against
• The number of chillers running, which would fall in the 1-10 range

The Issue

As you probably know, Excel lets you add a secondary axis to your charts, but, as far as I know, that is were it stops, at least in terms of being able to do it with the chart design tools.  Prior to the insight that lead to the technique I will show in this post, they way I dealt with the need to plot more than two data series with wildly different orders of magnitude was to scale one or more of them so they would be visually meaningful on one of the two axis I had available, and then include the scaling factor in the name of the series.

For instance, to plot the number of chillers running on the same axis as temperature, I might have multiplied the number of chillers running by 10 and then plotted it as Number of Chillers Running x 10.  That worked, but it was kind of confusing in a way. Having a third axis to dedicate to a third order of magnitude range (or a 4th or 5th or 6th if you needed them) makes it easier for me (and I think others) to intuitively read the chart.

The “Trick”

My trick for adding an additional axis (or more) to an Excel chart it is create a data series that I plot vertically against the X axis which is scaled to reflect the range I need so that it spans the entire height of the chart.

Then, I scale my data so it is visually meaningful on one of the two real axes that are available.  But providing the additional axis, tied to the data series via its name,  a person using the chart can read the scaled data against the extra axis.

That is the trick in a “nutshell”.  But I thought it might be useful to walk you through the steps in the process.   Moving forward, I will discuss this in the context of having three data series, each of which will be associated with a different axis.  But:

• Multiple data series with similar ranges and magnitudes can share an axis, including the third axis we will discuss creating, and
• If you had four (or five or six, etc.) radically different data sets, you can use the third axis technique I will discuss to create additional supplementary axes.

The Data Set

To demonstrate the concept, I created a little data set with three very different orders of magnitude associated with the three data series.

Incidentally, if you want to “play along”, I put the basic data set along with  my example spreadsheet in a zip file that you can download from the Excel Third Axis tool page on our Commissioning Resources web site.

Overview of the Problem

If you plot Value 3 (orange) in the data set above by itself against an appropriately scaled axis, it has an obvious wave form associated with it.

But, if you plot the Value 3 (orange) data set against an axis ranged appropriately for Value 1 (red), along with Value 2 (green) plotted against a secondary axis that is appropriately scaled for the Value 2 range, Value 3 it looks like a flat line.

Obviously, if you tried plotting Value 3 against the Value 2 axis, it would be an even flatter line given the magnitude of the range on the green axis relative to both the Value 1 axis and the Value 3 data set range.

Adding a third axis dedicated to Value 3 solves the problem as can be seen below.  Note that I plotted the value three axis and the associated line in blue and the actual data behind the line as orange markers on the blue line.

As you can see, you can now read the values for the points in the Value 3 data series directly off of the blue axis.  For instance, as illustrated above, Value 3 = .0075 when X = 7.

Some times, I want to make the series as visually large as possible compared to the other series, which is what I did for the previous image.  In other words, I wanted to provide the broadest visualization of the data relative to the other series that the dimensions of my chart would allow.

But, other times, I might want to scale things so that the data series are visually meaningful, but also so they do not lie on top of each other, like this.

That means that the decisions you will make to set up the third axis are a function of how you want to display your data.  You can make a case for either way depending on the scenario.

For this example, I will set the axis up to plot the curves so they are not on top of each other, as illustrated above.

Step 1 – Decide Which Data Series will use the “For Real” Excel Axes and Which will use the Third Axis

In the bigger picture, the first step in my process is to pick a scaling factor for your axes.  But to do this, there are actually a number of things you need to think about.

1. Which data sets will use the “for real” Excel axes and which data sets will use the third  axis you are going to create?
2. How should the two Excel axes be scaled?
3. How should your third axis be scaled?

The third axis is actually a fake axis in the context of plotting data.  In other words, while it will allow you to read an associated data series as if it was plotted against it, you are actually plotting a scaled version of your third axis data set against one of the other two  axes provided by Excel.  That means that when you are thinking about the different scaling factors, you kind of have to think about them interactively since the third axis will be influenced by the scaling factor associated with what ever axis you use to actually plot the data.

I typically start by looking at the maximum and minimum values for all of the data series and seeing if  any two of them have a numerical range that is similar in terms of the values but different in terms of order of magnitude.  For example a data set with a range of  0 to 2 is numerically similar to a data set with a range of 0 t0 2,000, but different by a factor of 1,000.   Neither of those data sets is similar to a data set with a range of –0.175 t0 .25 numerically or in terms of order of magnitude.

When I looked at the example data set in this manner …

… it struck me that the Value 1 and Value 3 data sets met this criteria.  So, I decided that I would use the “for real” Excel axes for Value 1 and Value 2 and create my third axis for the Value 3 data set, which I would plot as scaled data against the Value 1 axis.

Step 2 – Select Scaling Factors for the “For Real” Excel Axes

Now that you have settled on which data will use the built in Excel axes, you need to come up with a scale for those axes.  What you pick will depend on how you want to present the data, (overlapping or not) as I discussed previously.

Since, for this example, I targeted non-over lapping data series, I needed to select scaling factors that would do two things.

1. Allow the data, when plotted to be visually meaningful.
2. Create a “window for the third axis data series to reside in so that it would not overlap the other data series.

This is somewhat arbitrary and I usually make the decision by starting my chart, adding the a data series for each of the Excel axes and then playing with things.   Here is what I ended up with, which basically created a “window” for my third axis data series between the Value 1 and Value 2 data.

I could have also set it up so that the “window was above the Value 1 data series or below the value 2 data series;  like I said, it is kind of arbitrary.

One other point I should make before moving on to the next step is with regard to the large gap between the end of the data on the right side of the chart and the right (green) secondary axis.   That gap is not there due to my bifocals creating a distortion such that it looks like there is no gap there to me.

Rather, I created it intentionally because that is the space where we will place our third axis when we get to that step.  I could have put it on the left side of the chart, made it narrower, made it wider, etc.  Those are all arbitrary decisions.  But my point is that before the we finish, we will need a space for the third axis, so I went ahead and created it at this point in the process.

Step 3 – Select a Scaling Factor for the Third Axis

At this point in my process, my focus is on selecting a scaling factor for the third axis that will make it visually meaningful when I plot it.   In some instances, such a scaling factor will also place that data on top of the other data on that axis if they are numerically very similar.  That was the case for this data set as you can see from this table comparing the scaled Value 3 data with the Value 1 data and the associated graphs.

That would not necessarily be true if the Value 1 and Value 3 data was numerically similarish instead of numerically similar as can be seen from this table and its associated chart.

When I first started messing around with this, after going through an infinite number of variations between charts similar to the first two in this section and still ending up with the lines on top of each other, I realized what I needed was a scaling factor along with an offset.

Kind of a “Well Duh” moment, but as Jay Santos often says;

Engineers are empirical learners

In any case, by applying that principle,  I was able to achieve the following “look” using a scaling factor of 100 with an offset of –2.

Step 4 – Create the Third Axis Line

At this point, if you are “playing along”, you would not have a chart like the ones I have been using to illustrate the impact of various scaling factors and offsets because we have not discussed how to create that axis yet.  But you probably have a table that looks something like this …

… and maybe even a chart that looks something like this if you have plotted the data.

Our next step will be to start to build are third axis.

At a fundamental level, the third axis is just a plot of a data set where:

• The X values are all the same and correspond to the value on the X axis where we want the third axis to appear, and
• The Y values are selected so the data points are evenly space on the line and fall on the chart’s major grid lines.

For me, the easiest way to do that was to build a little tool that would create the table I needed for plotting the line based on the properties I wanted the axis to have, which are:

• The minimum value on the Y axis that corresponds to the minimum value on my third axis, and
• The maximum value on the Y axis that corresponds to the maximum value on my third axis, and
• The number of even increments I want to have on my third axis, and
• The X value associated with the location of the third axis.

Here is what my little tool looks like.

Here is the same thing with the formulas made visible.

Note that in my tool, if I change the Value 3 Axis Number of Major Increments (cell F27), I manually need to insert some rows in the dark red area and copy and re-paste the formulas in cells I31 and J31 into the same columns in the rows below.   I suspect you could solve that to happen automatically with some VBA code if you wanted to.

But having set up my table, I now can create my 3rd axis line by plotting the Third Axis X and Third Axis Y Values (Columns I and J, rows 30 – 38) on my chart against the primary Y axis (the red axis on the left).

Step 5 – Adding the Tick Marks

Next, we need to add the “tick” marks (the short lines next to the axis that fall on the grid line and reference the number associated with that point).  We do that by adding a marker to the line we plotted for the X axis.

More specifically, we select the line we just created and the pick the “Format Data Series” option.  I do that by clicking on the line and then right clicking and selecting it from the little window that opens up.  But you can also do it using the “Format” menu associated with the Chart Tools at the top of the page. (Heck, for all I know, there could be a shortcut key that lets you do it with one click in combination holding down Ctrl, Alt, and 8 other keys concurrently with Revolution Number 9 playing in the background.)

But no matter how you get there, once the Format Data Series window opens the data points will be highlighted (the red arrow points to what I mean).  From there, by selecting “Mark Options” (the blue arrow is pointing to it), you can select the marker type you want (where the orange arrow is pointing) and set all of the properties, like the line weight, the color, etc.  For my chart, I selected the marker that is a short, horizontal bar and made it the same color and width as the line I plotted for the axis.

In fact, if you wanted to, once you were at this point, you could click on an individual marker and make it different from all of the rest.  I’ll give an example of why you might want to do that at in the next section.

For the time being, here is my result after adding the markers.

Step 6 – Adding the Axis Tick Mark Labels

Next, we need to put numbers beside the tick marks on the third axis we created.  Excel allows you to put a label with each data point in a data series, and we will use that feature to do it.  You can get to it by hovering over the data series, right clicking, and selecting the “Format Data Labels …” option.

But first, we need to set up the number that will appear in the label.  This is fairly straight forward.

Specifically, if you were not offsetting the data, you would simply scale the Y axis labels using the same scaling factor you developed for plotting the data.  But if you are offsetting the data in addition to scaling it, then you also need adjust for the scaled offset.  I do this by adding a column to the table in my little tool.  Here is what that looks like along with a view of it with the formulas exposed.

Now, the trick becomes getting the third axis labels to show up next to the tick marks on the blue line we created to represent the third axis.   That is where the “Format Data Labels …” option I mentioned above comes in.

If I hover over my blue third axis line, right click, and select that option, I end up with a formatting window on the side of my screen very similar to any of the other formatting windows I get when I have selected an object and picked the formatting option.

Notice how the location where the labels will appear are now highlighted on the graph (the red arrow points to what I mean) and also that when I selected “Label Options” by clicking on the three little green bars (where the orange arrow is pointing), I have a place where I can select where the label contents come from (where the blue arrow is pointing).

By checking the “Value from Cell” check box, I can select my scaled values and have them appear next to the axis.  Note that when you do this, you may need to uncheck the “Y Value” check box, which is the default because, you can use multiple sources for the labels and we probably don’t want to do that in this case.

You can also format things like the font, the font color, the text box fill, etc. to get the look you want, just like you would for any other Excel object you were formatting.  Here is where I ended up after going through those steps.

You may notice that the most negative value on the third axis is hard to read because it lies on the X axis line.   You can fix that by selecting that particular label and adding fill to the text box (at least that is how I solve that problem).

I also inserted a rectangular shape, formatted it to match the chart background, and located and sized it to cover up the 11 and 12 on the X axis since they are superfluous for the purposes of my chart.

And because the values on my axis transition from positive to negative, but for the scaling factor and number of increments I selected, 0 (zero) is not specifically called out, I may want to change the font, tick, and line color for the negative part of the axis to highlight the transition.

Of course, it would also be possible to play with your scaling factors and offsets, and increments until you ended up with a scale that had zero on one of the major grid lines.  But given all the variables in play, which for this example included:

• Separating the data series, and
• Having a set of grid lines that hit even, meaningful divisions on all three axes, and
• Having all of the data be visually meaningful

that can actually be tricky.

Bear in mind that how far you go with that type of stuff is a matter of personal choice. Just because you can do it doesn’t mean you should  do it.  And what makes sense to you may not make sense to others.  For instance, to a color blind person, the little nuance I just illustrated is not very helpful.

Adding a Few Whistles and Bells

Pointer Lines

At this point, we have successfully (I hope) created our third axis.  If you needed even more axes, you could go through these same steps and add them.

But no matter how many axes you add, it is good to take a reading or two using them to make sure you didn’t zig when you should have zagged somewhere in the development process.

To facilitate that, I often add a little feature to my charts that makes it easy to precisely read data from the chart and also to highlight something for someone else viewing the chart.   More specifically, I add little arrows that point to the value on the third axis (or any axis I want) associated with a given value on the Y axis.  You may have already noticed them in some of the charts I was using earlier on in the post to illustrate the effect of different scaling factors.

The lines are generated in a manner very similar to the third axis.  In other words, the vertical line is just a series that is plotted with two points;

1. One point has an X coordinate equal to the X value of interest and a Y value equal to the minimum value on the Y axis that you are plotting the series against.
2. The other point also has an X coordinate equal to the X value of interest but the  Y value is the Y value of the data point you are wanting to read.

The horizontal line is generated in a similar manner, but the Y values are constant.  I do all of this by building a little table that lets me enter the X and Y values of choice and then sets up the other points in the table to generate the line.

In this particular example, I use VLOOKUP to come up with the Y value associated with an X value.  But, I can also manually enter the point, or I could even develop the equation for the line I am looking at using Excel’s trendline feature and then have it calculate the Y value for the X value I entered.

In terms of checking things, the easiest points to check, of course, are ones that by chance happen to lie on or near a grid line.   You really don’t need my little lines to do that , but for illustration purposes, if I have done things correctly, then the value on the third axis associated with X=5 should be just slightly above the .0025 tick mark/grid line on the third axis …

… which seems to check out in the graph above.  And the values associated with X=3 and X=9 should be just slightly above the –.0100 tick mark/grid line.

So things seem to check out.

Using Pointer Lines to Precisely Read the Data from Your Third Axis Series

Obviously, you could also do what I just suggested by simply inserting shapes on the chart where you wanted them.   But if my make them plotted lines, the  other thing you can do with them is use them as a tool to precisely read the chart.

In other words, by setting up your table so that you can pick an X value and then “play” with the Y value until the to lines meet at exactly the point of interest, you can “read” the Y value because it is the number you have in the cell that you were playing with.

In this example, I have my little table set up to adjust all of the other values based on what I enter in the yellow X cell and the orange Y cell.  The vertical orange line is the series in the red box plotted against the left axis and the horizontal orange line is the series in the blue boxes plotted against the left axis.

To use the tool in this manner, I arbitrarily set X to the value of interest.  In this case, I picked an arbitrary value of  4.86 and entered it in the yellow cell.

I will arrive at the Y value by “tweaking” (which is the technical term for “playing with”) the Y value in the orange cell and zooming in to look closely at where the two orange lines meet on the blue waveform.  I will continue the highly technical process of “tweaking” until the two lines meet exactly in the middle of the blue line.

To start that process for this example, I made an educated guess at about what the Y value  should be when X was 4.86 by considering the fact that the major division for the blue third axis was .0125 and then “eyeballing” (another technical term) that the point where the X axis intercepted the blue wave form was going to fall at least 4/5ths of the way between –0.0100 and +.0025.  I used 4/5ths because .0125 is pretty easy to divide by 5 in your head, even at my age.

So,

• One fifth of .0125 is .0025, meaning
• Four fifths of .0125 would be .01, and
• -0.0100 plus .01 will be about 0.

So I started there.

Obviously, I didn’t even need to zoom in to see that I needed to do some additional “tweaking”.  But I had kind of expected that;  my first estimate was just to get me in the “ball park” of where I needed to be.  As a result, I now knew that the number I was looking for was somewhere between 0.0000 and 0.0025.

Visually, it looked like it would be about half way between the two. So I tried that.

(Since my cell was formatted for 4 decimal places, 0.00125 got rounded to 0.0013)

That looked pretty good, and probably was “good enough for government work”.   But when I zoomed in to see exactly how good it was, I could see that I was just a bit low.

If you look closely, you can see that the two orange lines intersect towards the bottom of the blue line vs. exactly in the middle.  So, I arbitrarily made another “tweak” and adjusted Y to .0015, and as scientific people often say, “Ta-Da”.

How many “tweaks” you make is a function of how anal you are (I can be pretty anal).  My real point is that this approach lets you come up with a pretty exact value by reading your chart.

Obviously, if I knew the equation of the line, I could have just calculated what Y was given a value of X.  But a lot of times, when I am using this approach to read my chart, I am looking at some wild and crazy trend data that I pulled from a logger or control system or utility meter and there is no equation.  In fact, one of the reasons for plotting the data was so I could pick Y values from the data series based on an X value I selected.

Focusing Attention

One last “trick” before I stop.

When you start trying to show a lot of data on one graph, doing things to help people correlate the information can be helpful.  For instance, I color coded my axes to match  my data series in an effort to help a (non-color blind) person’s “mind’s eye” make the connection.

I potentially could further enhance that by restricting the range of the axes as shown below.

For the third, blue axis, I accomplished this by formatting the text in the data labels I wanted to hide so that it was transparent.   For the red and green axis, I did it by inserting a rectangle shape, formatting it to match the chart background, and then sizing and positioning it so that it covered the part of the axis and related tick marks that were outside the range of the data I was presenting on the axis.

                                                        

David Sellers
Senior Engineer – Facility Dynamics Engineering                                                                     Visit Our Commissioning Resources Website at http://www.av8rdas.com/

1.     What I mean by the term “visually meaningful” is that the scale of the axis associated with a data series allows its wave form to be seen.    If the peak to peak value of a wave form is small relative to the axis it is plotted against, it will come out looking like a flat line, even though it actually is not at all flat.