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.
derek says
I always felt you were a little unfair to the Juice Analytics readers for being delighted to discover the technique. I know I was very familiar with the use of REPT(character) to make crude charts, going back to my days using the equivalent command, @REPEAT, in Lotus 123 in the early 90s.
So what was new to me? A tiny little twist with a big impact, the introduction of narrow characters from proportional fonts. Previous explications of the “in-sheet chart” idea, including I believe John Walkenbach’s, had used monospaced fonts with every character no less wide than the letter M or W, which was never all that satisfactory to me. I think I said at the time on the Juice blog that the simple addition of Unicode characters made such a difference to the precision and look of the bars that it was like a whole new thing even to someone like me who had the basic gist fifteen years ago.
Jon Peltier says
Derek –
Unfair to the Juice guys? I don’t think so. I wrote comment #6 below Chris’ first post (Lightweight data exploration in Excel):
Nice demonstration of “less is more” and “keep it simple stupid”. This is one of those elegantly simple approaches that everyone always forgets.
My comments following Chris’ second post were not particularly germaine to the REPT approach, I was mostly showing what could be done with some simple VBA and colored rectangles (and a client without regard to the appropriate use of colors).
The pipe character makes an elegant chart, and in fact any character you like from any font makes a reasonable bar chart. J Walk liked the blocky Wingdings character because it gave the bar a bold appearance. The nice thing with using monospaced fonts is that you can use different characters for different markers (or spaces) and things will readily line up.
alderaic says
I told on DDOE but character g webdings makes for a nice barchart as it is a block with absolutely no space between to blocks, its a bit Off topic here but as it might help some other looking to use those small character charts ;)
Jon Peltier says
I made a little font and character browser a while back and posted it on my web site. It would be nice to help find good characters for the in-cell formula kind of graphic. The character browser is the last entry on this page:
Excel Tips
alderaic says
actually you can just use charmap, and another block without extra spaces is arial (default font and alt + 2588 )
also WTS fonts (for blind people) do have some kind of bar graph possibilities as they have blocks of different heights (one third, two thirds and three thirds) so using vba and colors it might be possible to replace sparklines with simple text.
Best option would still be to build a font dedicated to in cell charts to have something with a very light footprint and minimal knowledge of formulas / vba (vba to add colors)
Jon Peltier says
I like the font browser of mine, because I can view four fonts at once.
There are a couple of commercial sparkline chart programs (Bissantz Sparkline Tools and BonaVista Systems MicroCharts) which essentially use special fonts to draw small charts in the cells. They’re COM add-ins created with .Net, not VBA, but they have special code that changes colors. They use the COM/.Net equivalent of UDFs that return whatever text is needed in the special font to create the chart.
Frans van Zelm says
Dear mr. Peltier,
Not so much a comment (although … visit my site – the Excel part) but a request for help.
For a long time (years!) I try to find an example of a Lotus 1-2-3 DOS @Function that would create a chart. They were great.
Bu the way: in my courses, I always state that ‘impossible’ charts are done on your site. Thanks for the inspiration.
Kind regards, Frans van Zelm