Quick, clean, and to the point

This video comes from our video training for Excel Charts.

How to chart generations with floating bars

In this video, we show how to plot American generations in a floating horizontal bar chart. This seems like a simple problem, but it requires a formula, clever formatting, and a special feature of data labels. A nice example of what can be done with Excel's charting engine.
Video Transcript 

In this video, we'll plot American generations in a chart with floating bars.

On 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 ready 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 being plotted.

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 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 2025...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.

Dave Bruns

Related shortcuts