Annotated Graphs

Update 3 December 2007: Please note that this technique for annotating Excel graphs and including semi-transparent overlays to highlight sections of the graph apply to any Excel graph. The example below is of a server performance graph, but it could be applied to other types of Excel graphs. Please feel free to comment below if you have any other suggestions/ideas/questions. Over the past few months we've been graphing a performance metric of the World Bank's web pages, and we wanted to form richer graphs than what we could get out of Excel. In particular, we wanted the following:

• of course, the connected datapoints of the performance metric for every day (also showing days when the metric was not available)
• clearly indicate weekends
• indicate months
• insert annotations, like "major database upgrade" or "increased crawler traffic" so that we could look back over time to understand spikes (and gulleys) in the graph. We found that looking back it was sometimes difficult to remember why a particular spike happenned, so the annotations are important. Here's a partial example (but with no gaps in the data and not leaving blank room in the graph for future data) with some fake data I put together:

Perhaps there's a way of coaxing Excel to do that, but we found another approach that appears to be working using Photoshop or Photoshop Elements and Excel:

• Pick a time range including some time in the future (we decided to go to the end of this year). This will allow you to use the annotations on the graph, and continue adding to them (without having to move all the annotations).
• Generate your main graph in Excel (over the time range above), and then right click on the graph to set the background to not fill with any color (or select white). Copy this into Photoshop.
• Using the same range in Excel as the above, create a column for weekend. Set the value to 1 for a weekend day, and 0 for a weekday (just look at a calendar to set the first week, then you can cut in paste weeks for the range). Create a graph based on this (as previous step, plus turn off all gridlines), and copy this in as another layer in Photoshop. Set the layer opaxcity so you can "see through" to the main graph.
• Use the same technique to create a month chart, and copy that in as another layer in Photoshop and set the transparency.
• Resize/move the various layers to get them all lined up on the horizontal scale, and resize the month and day charts so that they take up the whole vertical space the main graph takes. You'll probably want to turn on/off visibility of layers to make this easier.
• Also delete sections of the overlapping graphs (use only the graph labels from the main graph). You can do this using the marquee tool on a layer, and then hitting the delete button.
• Using normal Photoshop drawing, annotate the graph.

For your reference, here are the files I used to generate the graph above: Photoshop PSD file (650KB) Excel file (26KB) You can make even slicker graphs using Adobe Illustrator (which has a graphing utility), but Illustrator is an expensive piece of software that many IT shops probably wouldn't be willing to buy for engineering graphs.