How to chart generations with floating bars

In this video, we'll plot American generations in a chart 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.

Now, neither the clustered or stacked option is really what I want, but let me insert the clustered bar chart so that we can take a closer look.

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.

So, the chart actually make sense in a way, but its not going to work for our purposes.

What we need are bars that start at the correct year, and extend for the right number of years. 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.

Notice the orange bars are now in the right location, since they're being supported by the bars plotting start years.

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.

I'll start the axis at 1915, and end at 2020...using 10 years for the major unit.

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.

Finally, I'll delete the vertical axis and legend, make the data labels white, and give the chart a title.

And now we have our floating bar chart plotting generations.