How to chart generations with floating bars

In this worksheet we have a list of six generations.

Each generation has a start year and end year, which represent birth years.

How can we plot these generations in an easy-to-read bar chart?

This seems like a simple problem, but a solution takes a little creative thinking.

To help illustrate the challenge, I’ll plot this data in the most obvious way.

I want a horizontal bar chart, so I’ll use the icon next to recommended charts.

What we have in this chart are start years and end years, plotted as separate bars.

The horizontal axis is automatic, based on the values in the data.

It’s this duration that we don’t have in the source data.

Luckily, with Excel’s formula engine at our disposal, this is a trivial problem.

I’ll delete this chart and start again.

First, I’ll add a column to calculate duration.

The formula is basic, just end year minus start year.

Now I’ll create a new chart.

The trick here is to plot only start year and the duration, using a stacked bar chart.

Now the clever bit.

I’m going to make the start bars invisible, by setting fill and line to none.

Then, I’ll make the remaining bars thicker, and tighten up the horizontal axis.

On the vertical axis, I’ll reverse the categories.

Next, I’ll label the bars with generation names.

To do this, I need to enable data labels.

Initially, this shows duration.

But, under label options, I can uncheck value, then check category name.

And now we have our floating bar chart plotting generations.