Attention to the key "retail rectangles" - visible cells, a printed page, a chart, a slide - is an indispensable strategy to bring your message to market.
Charts have an obvious special place in the design of elegant spreadsheets. If a picture is worth a thousand words, a chart is worth at least that many cells. But like Excel itself, haphazard design (an oxymoron?) can backfire, not only missing the right point but making a wrong one. At best, the impact is more time and effort than necessary spent trying to understand the data.
This post highlights some chart features to look for using the case of a timeline chart. If you have followed this blog you'll recall an earlier timeline project using conditional formatting to simulate a chart. That project produced an elegant result but used an overly complex method resulting in performance issues. This update simplifies the process using a standard bar chart with a few customizations.
Also in a previous post I presented PageMaker (multi-column wrapping), and discussed the importance of "managing the rectangles" through which we consume information. Attention to the key "retail rectangles" - visible cells, a printed page, a chart, a slide - is an indispensable strategy to bring your message to market. It follows, then, that one of the pitfalls to avoid in sheet and chart design is to put too little or too much into one rectangle. With too little, context is lost; with too much, focus is lost.
The first stage of output design is to study the data. What do you see? Are you overwhelmed? What would help you see better? What conclusions can you draw? This is a challenge to find and articulate or envision the essence of the data, without missing or twisting any angles. It's a great time to use quick and dirty pivots and charts, whether you keep them or not. Only from understanding can you "chart" the path to elegant presentation.
This is also an honest check of mental capacity. If you have a reasonably small data set, say US state population or US presidents, you can expect to portray it all in one "rr". On the other hand, if you're starting with 5000 records or a dozen data categories, you'll need to filter or summarize the data into a single portion size, which might entail a pivot or dashboard for point-of-use reduction.
Timeline Chart
Now on to today's feature presentation, a timeline chart using a table copied from the internet of the 46 US presidents, with date columns including birth and death, term start and term end. For output, the horizontal axis should show the year, with names vertically; the bars will be stacked, three different colors, to show each president's lifespan in three parts, before, during and after his term. (Note that in the image above, only the most recent 12 are shown, as an example of filtering for impact.)
Prepare the Data
First, we need to normalize the data. The dates span 1900, so some are interpreted by Excel as dates (>=1900) and some as text (<1900). For my purpose, I don't need actual dates, only years, which are integers and thus unaffected by the arbitrary 1900 cutoff. To extract year from these mixed data, I use YEAR(date), and IFERROR, then VALUE(RIGHT(date,4)).
=IFERROR(YEAR([@[TERM BEGAN]]),VALUE(RIGHT([@[TERM BEGAN]],4)))
Thus, January 20, 1961 (date) returns 1961 (integer), and April 30, 1789 (text) returns 1789 (integer). In the case of the year for the end of term, in order to accommodate the current president, I substitute the current year when term end is omitted.
=IFERROR(YEAR([@[TERM ENDED]]),IFERROR(VALUE(RIGHT([@[TERM ENDED]],4)),YEAR(TODAY())))
The stacked bars should float on the chart - starting at birth year, then marking each milestone. There will actually be four stacked bars, not three - the first is the birth year (hidden), the others are the incremental years to each milestone. So the birth year can be used as is, but I need additional columns to calculate the increments. This is simply done by subtraction of the previous milestone year:
Before = year term began - year born (= age at inauguration)
Term = year term ended - year term began
After = year died (or this year if alive) - year term ended
The table with these five calculated columns (shaded) looks like this:
Insert Chart
Now, if I insert chart, what will I get - how smart is Excel's "AI"? Alt-NC1-2D Stacked Bar:
Well, it's nothing like what I want, but it's a starting point from which I can take a reasonable number of steps to refine.
Format the Chart
Expand each section for formatting details. Note, as you go, expand the height of the chart as desired to avoid overlapping data.
Remove unnecessary columns
Hide the first bar
Optimize the date range
Add Data Labels
Delete Name Axis Labels
Final Formatting
Prepare for Print
Final Result
As noted above, this data set lends itself to a clean, one-page presentation. The technique of making bars float by hiding the base category can be applied to more complex data, or to relative series such as process steps. Formatting choices accentuate the visual appeal of the chart by adding focus and context.
Comments