Annotating Charts in Excel

When you are presenting data in the form of a graph or chart, it is critical that the people observing the chart understand what the data signifies.  Its easy to loose sight of this if you are the developer of the chart, at least it is for me. 

Contents

The links below will jump you around in the content if there is a topic of interest you want to focus on.

It Makes Sense to Me

Consider, for example, the chart I used to illustrate the previous blog post.  My first draft of the chart looked like this.

image

By the time I had finished the first draft, it was already obvious to me that there were a number of potentially significant energy conservation opportunities that I wanted to target and describe to the facility’s Owner and operating team. 

In a nut shell, the system was taking outdoor air that was almost at the set point it needed, and then heating and cooling it unnecessarily.  And it was doing this on a round the clock basis.

I could see that because I had been working with the data for a while, studying and manipulating it to generate the first draft including applying the colors I typically use for various parameters to the data channels.  So it is almost a self-fulfilling prophesy that I would look at the chart and see exactly what I want my audience to see.  After all, a picture is worth a thousand words, right?

But I’m not the person I have to convince about the problem if I want to get it fixed. I have to convince the Owner and operating team who will be looking at the graph for a few minutes, not a few hours and who may need to understand it with out the benefit of my being there to explain it to them.

Back to Contents

Just Because You Get the Point Doesn’t Mean Your Audience will Get the Point

While upon comprehension, a picture may truly be worth a thousand words, a picture that is not comprehended by the target audience is pretty useless.   In, the case of the example above, having worked with the data for a while, it was clear to me that between 4 pm on December 23rd and 11 am on December 24th, the Roof Top Unit (RTU) I was studying was taking outdoor air that was generally cool enough to meet the desired set point and:

  1. Mixing it with return air, which elevated it above the desired set point, then
  2. Preheating it to an even higher temperature, then
  3. Cooling it to a temperature that was below the mixed air temperature but most of the time, was above the outdoor air temperature, and finally,
  4. On occasion, reheating the air before discharging it from the unit.
  5. All of this time, the system seemed to be running round the clock even though the area served was probably not in use all of those hours.

As I said, that was all pretty clear to me by the time I was done with the chart.  But I suspect, given only the chart and lacking the preceding enumerated points, it might take while for you or others to understand what was going on.  Since it is generally important for my audience to understand the information I am trying to convey, taking a few extra steps to make sure they “get it” can be really important.

Back to Contents

Enhancing the Picture with the Spoken Word

I have found that if I take a moment to think about my charts and illustrations from the perspective of an audience viewing the data for the first time, I often realize that what is obvious to me may not be so obvious to them.  So, if I want them to understand the point I am trying to make, I need to do some things to facilitate their understanding.

One approach that seems to work when you are presenting your graph in person is to use the spoken word to facilitate understanding.   It can be quite valuable to spend a few minutes orienting your audience to the various elements of your chart.  For instance, for the chart we are using as an example, I might say something like:

What we are looking at here is the performance of Ball Room RTU-3 from about 4 pm on December 23, 2014 through about 11 am on December 24, 2014.  Fan amps – the purple line – are plotted against the right axis.  All of the other lines represent temperatures at various points in the AHU, which is what I want to focus our discussion on.

Having oriented the audience, I can then move on and describe the meaning of each line and what is going on.

Back to Contents

Enhancing the Picture with the Written Word

While useful with a live audience, the preceding approach simply will not work if you are not there in person to explain things.  In addition, there is a lot going on in the chart we are discussing.  Meaning that even if I was presenting the chart in person, adding some features to reinforce what I was saying and facilitate retention on the part of my audience would be desirable.

By taking the time to supplement your picture with a few words making key points, you can facilitate the understanding of a live audience and also make the information accessible to others who may be using it without your being present to explain things.  A good starting point might be to include a legend explaining what each line represents.

image

But, its important to recognize that not everyone in your audience is an HVAC engineer.   So making your legend a little more descriptive may be beneficial.

image

It may even be desirable to change the order of items in the legend to match the flow path through the unit.

There may even be some advantages to separating the legend into two legends, one for the left axis and one for the right axis.

image 

Excel is not very well suited for doing this directly.  To create the two legends shown above, I took the following steps. 

First, I turned of the legend feature in Excel.  Then, I opened PowerPoint and and used the standard text box and line shapes to make each individual legend.

image

Once I had the legends created, I made them into a Group by selecting all of the lines and text boxes, hovering over them, right clicking and selecting “Group”. 

image

Then, I copied the Group to the clip board and used “Paste Special” to paste the Group back into the PowerPoint slide as a picture. 

image

Note that I got the little “Paste Options” window by right clicking on the slide after I had copied the Group to the clipboard.  I picked the second icon;  the one that looks like a little mountain with a sun to the right of it. 

The reason for going through this step is that if you copy your legend and then try to paste it into Excel, Excel will not let you paste it in as anything but an Microsoft Office object (at least as far as I can tell) so you get an odd looking image relative to the one you created.

Finally, I copied the picture version of the legend I created in PowerPoint and then pasted it into my Chart in Excel by using the Paste shortcut (Hold down the “Control” key and then hit the “V” key).  Once the legends are pasted into the chart, you can click on them and move them to where you want them, just like any other object you might insert into a chart.

Back to Contents

Color Matters

The color of a background makes a big difference in how lines and text look.   Here is a slide that has identical colors for font and text on a black and white background.

image

I’m still learning about this, but obviously there is a difference.  And my experience has been that it also makes a difference if you are looking at the image in print vs. projected  vs. on a display where the light comes out of the display vs. being reflected from a screen.

To my eye, things look sharper on a black background, so I have bee moving to that for most of my slides and graphs.  But I also know that I need to be careful and make sure things have good contrast.  Ultimately, when we do this, we are trying to convey information and that (in my opinion) trumps matching the color pallet in the client’s logo.

Its also important to remember that some people are color blind.  So doing things to make lines appear visually different in terms of line style can make a difference.  There is a handy web site called Vischeck that lets you see how your images will look to someone who is color blind.

Using something besides color to distinguish lines also becomes important if your charts will be reproduced in black and white. The built-in “tricks” you turn to initially in Excel to distinguish lines other than by color are to either vary the line type …

image

… or turn on the line markers.

image

But for data sets with a lot of data points like we often encounter with when doing data logging and trending, neither effect is very pleasing for a time series graph.  And the differences in the lines may may not even be that distinct due to the way the dash pattern repeats or the markers overlap. 

Back to Contents

Reducing the Number of Data Markers in an Excel Chart

I have found no direct way to tell Excel to only plot every 10th or 50th or 100th marker for a line in a chart.  But I did discover a trick that, for a modest effort, will create that effect.  I have illustrated it below for the green outdoor air temperature line, where I have created a marker for every 100th data point.

image

Step 1 – Create a Second Data Set with Blank Cells Every So Often

The trick is as follows.  For each data series that you would like to add occasional markers to, add a column to the spreadsheet next to the column with the original data.  Then, enter a formula in that column that puts a blank in the cell unless some criteria is met.  Here is what I did for the outdoor air temperature data to pick up every 100th data point.

image

This is the formula that I used in each cell in column “E” as it appears in that cell in row 93.

=IF(MOD(ROW(C93),$E$1)=0,D93,” “)

The formula use the Excel function Modulus (MOD in the formula).  The modulus is the value that remains after you divide one number into another number.   So, if I divide 11 by 10, the modulus is 1.  But if I divide 20 by 10, the modulus is 0 because 20 is evenly divisible by 10.  The modulus for 100 divided by 10 is also 0.

What the formula says is as follows:   If the modulus of the current row number is evenly divisible by 100 (the value in cell $E$1), then put the value in column D of the current row in column E of the current row, otherwise put a blank there

The dollar signs included with the reference to cell E1 make it an absolute reference.  That means that I can copy and paste the formula all the way down the spreadsheet and it will always refer to cell E1 for the divisor in the modulus calculation. 

In contrast, the other cell references are relative references.  For instance, the reference to D93 is actually telling excel to look in the the cell in column D of the current row.  When that part of the formula is pasted down through the worksheet, the cell referenced changes with each row.  For example, here is the formula is it appears in row 100 of the spreadsheet.

=IF(MOD(ROW(C100),$E$1)=0,D100,” “)

Note that the cells C100 and D100 are now referenced instead of cells C93 and D93.  And, notice that cell E1 is still referenced as the place to look for the divisor in the modulus function.

Instead of referencing cell E1, I could have simply entered “100” in its place in the formula.  But pointing to a cell lets me “play” with the spacing between data points to get the look that I want.  For instance, to get twice as many markers, I would just change E1 to 50 instead of 100 (compare this graph to the preceding copy where E1 was set to 100).

image

Step 2 – Plot the Second Data Set Along with the First Data Set

To get the markers to show up on your chart, you simply plot the second data set along with the original data set.   When you do this initially, you will probably get little spikes in stead of markers because Excel may default to having the line for a data set turned on and the markers turned off. 

image

The spikes are there because Excel only plots points where there are numbers.  The blanks are not plotted. 

To get rid of the spikes, you simply use standard Excel formatting techniques to adjust the data series formatting so the line is turned off and the markers are turned on and formatted as you want them to be.

image

This technique works best if you create your own legend.  Other wise, the marker data series shows up along with the line data series in the legend Excel will generate.  Don’t forget to include the marker in your legend when you create it along with the line.

Back to Contents

Adding a Title

Adding a title to my chart provided two more pieces of information.

image

One piece of information is that it identifies the system that the data came from.  That is useful information for a number of reasons.  One is the obvious one; i.e. it identifies the specific piece of equipment that is being studied.  You may even want to add the name of the facility to the title if your presentation includes data from a number of locations and the Owners are agreeable to your naming their site.

In this case, I also used the title to nail down the date associated with the data.   I could have done that by formatting the time axis to include the data as well as the time.  But that seemed to make the axis more congested and harder to read.

image

This is a bit of a personal preference thing;  what really matters is that somewhere on the graph, the person viewing it can understand the complete time frame, including the month and year, not just the hour of the day.

Back to Contents

Replacing the Legend with Annotations for Each Data Series

Another way to handle the need for a legend and identify lines with something besides a color is to provide an annotation for each data series.

image

The only place this approach falls apart is when two lines merge and then separate again, like the chilled water coil and reheat coil lines do at several points.  However, even if this was plotted in black and white, the two different colors would likely show up as slightly different shades of grey, which usually is enough of a distinction for most people to sort things out.

Back to Contents

Replacing the Legend with a Picture

This approach is a bit unusual, but I think it can have an advantage in a situation like the example we are discussing here.  You may recall from the opening discussion that the graph is showing an RTU that is wasting energy by taking air through a series of heat transfer processes where one process ends up working against the process ahead of or subsequent to it. 

It occurred to me that if I could some how convey the various temperature data series as a function of where they were being measured in the unit, then the dysfunction in the system may become more obvious to my audience. 

As a result, it seemed to me  that using a diagram of the unit with the loggers locations shown as a legend and then referencing each data series to the logger that generated it may make my point.  The result is as follows:

image

The image I used was simply a cropped version of the partial system diagram I had created to show the operating team where I wanted them to install the loggers I sent to them.  In my presentation, I included it as a second page to complement the chart.

image

I don’t think this approach is workable in every single situation.  But for this instance, I think it helped me convey the energy waste that was occurring, which was represented by vertical displacements on the graph but in terms of time and process, occurred via a series of heat transfer processes in a horizontal path as the air moved through the system.

Back to Contents

Font Sizes, Real Estate, and Legibility

One of the issues that comes up when you start providing additional features in a chart or slide is that you need space to put them.  One of the things that impacts the space that is occupied by the various annotations is the font size you are using.   As a result, it is tempting to make things smaller and smaller so you can fit more in.

But at some point, that starts to not work out.  Either the actual printed size of the graphic in your report makes it impossible for someone to read it if they don’t have the ability to zoom in on it.  Or, in a large classroom, the people at the rear are straining their eyes trying to follow your illustration.  Either way, you are liable to loose your audience and as a result loose the opportunity to make your point.

Generally, I need my graphics to be legible when I insert them into a slide for a class or into this blog.  And, I want your eye to be led to things, so, for instance, I will make the font for the chart title larger than the font for the axis titles.  And those fonts will be larger than the fonts in the legend and axis labels.

Having done this for a while, I have discovered that if I make chart fonts much smaller than 12 points, they will not be legible in my slides or blog posts. For notes on slides, it seems like fonts need to be at least 18 points to be legible to someone in the back of a large classroom.  So, those metrics become a lower limit for me when I am developing my graphics. 

Another “trick” that seems to help is to make the fill for something like the title of the graph semi-transparent.   In the example, this allowed me to place the title over the fan amps data stream.  So, while the title partially hid the data, you could still see that the flat, steady state condition that clearly shows up to the right of the title persisted through the entire data set. 

That, of course was my point;  the fan is running round the clock serving a load that probably does not require 24/7 service.  Making the title semi-transparent allowed me to place it over the data set but, in my perspective, also allowed my audience to see that the fan operating state did not vary much.

Back to Contents

A Great Resource

In my opinion, one of the true geniuses in terms of conveying information in a clear and concise manner is Edward Tufte.  He has published a number of books on the topic of data visualization which are well worth the investment if you are going to be doing this a lot.  There also is a moderated forum containing a large inventory of discussions on various data visualization topics available on his web site.

David-Signature1_thumb1_thumb

David Sellers
Senior Engineer – Facility Dynamics Engineering
PowerPoint-Generated-White_thumb1_th

Click here for a recent index to previous posts

This entry was posted in Excel Techniques. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s