Sunday, July 19, 2009

Create Timeline Chart


Sample Data Table

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.

Rearranged Data Table

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:

Default Chart

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.

Formatted Chart

No comments:

Post a Comment