Floating Timeline Chart

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

Alt-JCE, Select Data Source dialog.

On the right, the Horizontal (Category) Axis Labels include # and name. Edit the source range as desired to show name only. On the left, under Legend Entries (Series), select and Remove Died, Start, End.

This reduces the time axis from 0-9000 years to 1550-2050.

Hide the first bar

Select one of the bars in the "Born" series, and right-click - Format Data Series, or Ctrl+1 (same shortcut as cell formatting). Select "No fill".

Optimize the date range

Select the date axis, Axis Options, Bounds, Minimum, enter 1700.

Add Data Labels

1. Right-click a bar in the "Before" series and Add Data Labels.

2. Format Data Labels to include Category Name and Value, and Position at Inside End

3. Uncheck Wrap text in shape

Delete Name Axis Labels

With name on the data bar, the name axis is redundant. Select it and delete.

Final Formatting

Now comes the finesse.

• Expand the chart height. If the data labels are taller than the bar, widen the bar by reducing the gap width: select a bar, Series Options, Gap Width, reduce it until the label text fits in the bar (e.g. from 150% to 100%).

• Set bar fill colors to contrast the text and to emphasize the "Term" series. For example, as shown: "Before" series, Gold, Lighter 40%; "Term" series, Dark Red; "After" series, White, Darker 25%.

• Add Title, e.g. "US Presidents"

• Delete "Born" from the Legend.

• Select time axis, Axis Options, Labels, Label Position, High

• Select the legend, Legend Options, Top Right, allow overlap (uncheck the box)

• Right-click the time axis, Add Minor Gridlines

• Tweak further as desired

Prepare for Print

Select the chart and hit Ctrl+P to Print Preview. Depending on the purpose, set orientation and margins. Go back and resize the chart or add text if needed.

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.