Create Timeline Chart

David from the Mullins
Steel Boat Club asked about creating a timeline chart. He wanted to be able to show which
particular models were available in a given year. David tried creating a timeline by using timeline options
in the chart wizard, but unfortunately, they did not look too appealing.

I imagine that David's data looks something like the table to the left. For each model, he lists
the starting and ending year that the model was in production.

In order to create the chart, let's rearrange the data as shown at the secont picture. As in the
original data, show the starting year for each model, and then replace the ending year with a duration.
This duration number will indicate the width of the bar on our bar chart.

Excel doesn't offer a floating bar chart, but we can create the same thing by using a stacked bar chart
and making the first chart series invisible. So, for model E, the first series will be 1960 units high
and the second series will be 21 units high. When the first series is invisible, the chart will appear
to have a bar floating from 1960 to 1981.

Highlight the table and click on the chart wizard. If you cannot find the icon for the chart wizard,
you can go to the Insert menu and go to chart. Next, make sure that under the chart type, that you
choose the Bar option, and that under the sub-type that you picked a stacked bar. Click Finish and you
will have a default chart. The default chart is still a long way from what we want:

Now here is the trick to the whole thing. Go to the graph, right click on one of the blue bars and
choose the Format Data Series option. Now under the Patterns tab there are two options of Border and
Area. Make sure you choose the None option and then click OK. Now - boom - the start bar will disappear,
leaving you will floating bars for each model.

Right click on the X-axis and choose the option called scale. Under that menu, you may want to change
the minimum and maximum years to better fit the data. In this case, I chose to have the minimum start
at 1900. Click on the Legend and press the delete button to delete the legend with the now-confusing
reference to start year. Right click the chart area, choose Format Plot Area, and change the default
gray background to None or another color if desired.

Notice it is somewhat frustrating that the Excel plots the first model in the table closest to the X -
axis. Keep this in mind when setting up your data table. By listing the models in reverse order, you
could easily have the oldest model appear at the top of the chart.

Thanks to MrExcel.com's summer intern from the University of Notre Dame's MIS program, Mr. Anhtuan Do,
for his help in creating this tip of the week.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.MrExcel.com debuted on November 21, 1998.

All contents Copyright 1998-2017 by MrExcel Consulting.
This page last updated on July 05, 2014Privacy Policy