How to Share Big Picture Data with an Excel Timeline Chart

It can be difficult to quickly, efficiently communicate project timelines. A graphic representation of your deadlines and milestones can clear up a somewhat complicated meeting. Think of project management. You can see when certain processes are started and completed in relation to other processes. Or, try an Excel timeline chart in scheduling to make sure there are no conflicts at a glance. Communicating with team members and external shareholders is much easier when everyone is on the same page!

Fred Pryor Seminars_Excel Timeline Chart_figure 1

Unfortunately, creating this timeline chart is not very intuitive in Microsoft Excel. I wouldn’t recommend a detailed Gantt chart in Excel, but you can create a simple Excel timeline chart with the steps below.

Step 1: Populate Your Data

To begin, you must first have a data table that contains all of the task names in the left column (A) and the start date and task duration in the two right columns (B and C).

Fred Pryor Seminars_Excel Timeline Chart_figure 2

Step 2: Create Your Chart

Select your data table and then hit the Charts button under the Insert tab. Choose a horizontal 2D stacked bar graph.

Fred Pryor Seminars_Excel Timeline Chart_figure 3

Step 3: Get the Right Data in the Chart

This is always the hardest step because initially your chart will have the right data in the wrong places – if the data appears at all!

To check, click on the select data button under the Design contextual ribbon. Make sure that your start date and duration are the two items listed under Legend Entries (Series). These should be the only two items listed under this.

Fred Pryor Seminars_Excel Timeline Chart_figure 4

Let me guess. Is your information jumbled or in the wrong spot? Let’s troubleshoot.

To fix your data, choose Add/Edit under Legend Entries (Series). To add the Start Date data, select B1 for the Series Name and B2:B13 for the Series values. You can edit or check the Duration data in the same way – Series name should be C1 and Series values should be C2:C13).

To edit your categories, choose Edit under Horizontal (Category) Axis Labels. The data range should be =Sheet3!$A$2:$A$13.

Fred Pryor Seminars_Excel Timeline Chart_figure 5

Right now your chart should look like a stacked bar chart with your task names on the vertical axis and dates on the horizontal access.

Step 4: Make it a Gantt Chart

Essentially, all you have to do now is to change the fill color of the leftmost part of the stacked column either white or “no fill.” If you have grid lines in chart then you will want to use the “no fill” option.

Step 5: Make it Look Good

Of course, the final step is making the chart look good enough to send to management or your peers. Make sure that you resize the horizontal axis to show only your duration bars. Click on your chart on the horizontal axis and right click. This will bring up the axis options area where you can change minimum bound on your access. After that, change the colors of the bars to something more interesting. Be sure to add a title when you are done.

An Excel timeline chart can be used for many purposes, and management will love it that you are able to make one without the need for expensive project management software!