Graphing With Excel

Broken lines in Excel Spreadsheet Graph

My wife needed to graph some data from a spreadsheet for work. Usually this is no big deal, simply highlight the data you want graphed, click the chart button and away you go. I've used it numerous times and the biggest problem I have is remembering that spreadsheet people call graphs charts.

There was only one problem in this case: not all of the data was contiguous. Matter of fact, the data was pretty sparse. Still, she wanted to see lines between the data points so we can see the trend, if there is one, over time. You should be able to do that, so I volunteered to figure it out.

Took me the better part of two hours to get it all sorted. The biggest problem was the enormous amount of information out there on how to do stuff with Excel. It has become the new generation's Emacs. (For those of you who have not had the pleasure, Emacs is/was a text editor for Unix. It was the be all and end all. You could rebuild the entire universe from inside Emacs. It was crazy. I tried it once and quickly moved on to something more rational.)

Eventually I came across a web page about making graphs with broken lines, which introduced me to #N/A which is returned by the function NA(). So, for some reason filling all the empty data cells with =NA() caused lines to appear between the very sparse data points. Cool! Victory is ours! The infidels have been defeated! Let us retire and celebrate with blood ale and roast beast!

Update February 2017 replaced missing graph.