A Retrospective on Charting

My daughter’s got a dentist appointment now, so I’ll try to whip off a quick post while sitting in the waiting room.

While looking up a topic on Charley Kyd’s ExcelUser.com site, I stumbled across his historical article The First Dashboard Used in Spreadsheets. This sample dashboard is so old, it predates Excel*. Charley presented it as an example in the first book he ever wrote, Financial Modeling Using Lotus 1-2-3 (Sybex, McGraw-Hill, 1986). The “dashboard” consists of charts created with text, using characters as bars and markers within blocks of text. While this is not the first example of text-based charting, it’s one of the oldest I have encountered.

*Charley wrote the book before Excel came out, but the book was published shortly afterward. Excel came out in 1985 for the Mac, and in 1987 for Windows. I fiddled with MacExcel a bit around 1990, used 1-2-3 for ten minutes in 1992, and I’ve used Excel ever since, starting with Excel 3.0.

The techniques used isn’t really magic, of course. It relies on cell formulas to position text characters in order to represent numbers graphically. John Walkenbach wrote of this technique in Creating a non-graphic chart directly in a range. John’s tip uses the Excel function REPT to repeat characters in the cell. REPT("a",n) returns a string with the character a repeated n times. You can create simple but decent charts using the appropriate combination of character and font (e.g., “n” in the Wingdings font) with the right scaling factors to calculate n.

While writing an article about Dot Plots, my colleague Debra Dalgleish reminded me of this technique. I wrote In Cell Charting with Worksheet Formulas, which shows how to generate dot plots and bar charts, and how to apply custom formats. You could use conditional formatting to change the cell’s font color, for example, or use IF statements to display different “markers” (e.g., +, o, or x).

Some time back, the Juice Analytics blog featured two blog posts that discussed in-cell worksheet function charts, Lightweight data exploration in Excel and More on Excel in-cell graphing. For a few weeks or a couple months, other blogs kept picking up on this technique, and it was the neatest thing since, well, since “real” charts were included in spreadsheet programs. I guess I won’t be surprised in fifteen years when the technique is rediscovered again.

At the time Charley was writing this book, I was writing my doctoral thesis, so obviously Charley’s older than I am. The version of Minitab available at that time ran on mainframes, and printed reports were strictly text-only. Of course, rudimentary charts were included, using combinations of characters to display trends, frequency distributions, and values with confidence intervals.

----+---------+---------+---------+-----

                    (-------*------)

  (------*-------)

----+---------+---------+---------+-----

   9.75     10.00     10.25     10.50

Minitab Text Chart

For my thesis, I was analyzing high temperature mechanical test data using the lab’s fleet of HP-85 desktop computers for data acquisition. We stored data on magnetic tape cartridges, and when we graduated to external 3.5″ floppy drives, we thought we’d struck gold. 400 kb!! My first computerized charting used HP BASIC and HPGL on the HP-85 to drive a two-pen plotter. It was so nice not to draw the charts by hand, we never noticed the tedium of telling the computer how to lay out the axis ticks and labels. Many of my charting habits were formed at that time, and Excel hasn’t broken me of them. To me, an XY series is just a way to get the plotter pen to a certain place on the chart, so I can add my axis ticks or other features.

Small world: Charley also used the HP-85 with plotters to make charts.

Related Posts:

• In-Cell Bullet Charts
• Charting Seasonal Sales Data in Excel using Cycle Plots
• Dashboards
• Quick VBA Routine: XY Chart with Axis Titles
• Dashboarding and Charting Training
• Apply Chart Formatting to Other Charts