Creating dynamic Excel timelines that scroll

Analysis

Last week’s newsletter described how to create a static event timeline in an Excel chart. In this article you’ll learn how to make the timeline dynamic so you can scroll the timeline through a database of events or project due dates.

Timelines are great for project management in Excel, event management, and editorial or marketing planning. In fact, they’re a great visual tool to help you show the order of events over time.

In this week’s article you’ll see how easy it is to turn that static timeline into a dynamic timeline that enables you to scroll through a database of events. (The article “Create an Excel Timeline Chart to Manage Your Projects and Events” explains step-by-step how to create a static event or milestone timeline in Excel.)

<Download the Excel XLSX template containing static and dynamic Excel timelines.>

Tip: To see any figure in larger format, click on it to display it in a pop-up window.

Use the dynamic Excel timeline to scroll through projects or event timelines.

Use the dynamic Excel timeline to scroll through projects or event timelines.

Timelines are great for giving a visual map of the timing of different events. But most projects or marketing plans usually involve many milestones or events stretched over six months or more. Showing all that data in a static timeline isn’t workable and manually updating the timeline every week or so gets to be a real pain. So what to do!

Since most projects management in Excel involve a database or Excel list of events, the easiest thing to do is create a timeline you can scroll through with a scroll bar. Dragging the scroll bar reads new data from the Excel list on the left into the list in the middle which is used to create the chart. If you download this Excel timeline template you can hook it up to your task or event list pretty easily.

Data from an Excel list or database is retrieved into the range used by the chart.

Data from an Excel list or database is retrieved into the range used by the chart.

This layout starts with the static timeline worksheet and inserts columns on the left so the first data cell is H5. The list in H4:K16 is static and feeds data to the chart data in M3:Z28.

The list of dates, milestones, and label heights in the range B4:E28 is also static, but it will be used as an example of a larger database for a large project. The figure below shows this data so you can type it in to follow along. At this point all of the data in both lists are static (fixed). What we want to do is to manually control the update of range B4:E28 (feeding the chart) from the data in M3:Z28. Here’s how we do that.

Selectively Updating Data from One List To Another

Once you have setup or downloaded the sample worksheet as shown you need to enter formulas in range M4:Z16 that will dynamically pull data from the range B4:E28. To do this you will be entering an INDEX() function in the ranges M4:Z16. The INDEX functions parameters are,

=INDEX(array,row_num,column_num)

To easily keep track of the rows and columns around the source range, enter numbers along the sides of the B4:E28 as shown below.

Numbering the rows and columns on the side of the list makes it easier to understand the cell retrieved by INDEX.

Numbering the rows and columns on the side of the list makes it easier to understand the cell retrieved by INDEX.

In cell H5 enter the INDEX function,

=INDEX($B$5:$E$28,$A5,B$3)

The source range is $B$5. The row of the cell being retrieved is in $A5 and the column number of the cell being retrieved is B$3. You should see Apr 2 in the cell H5. It was retrieved from the first row and first column of $B$5:$E$28.

To fill the range H5:K16 just copy the cell H5 down and across the range. The numbers in cells A5:A28 control the row being retrieved. The numbers in cells B3:E3 control the column being retrieved. The cell values in H5:K16 will still be used to create the chart, but now they are being pulled from $B$5:$E$28.

Controlling the INDEX function with a Cell Value

Now, what if you could control the row numbers being retrieved? That would enable you to select which data appears in H5:K16 and therefore what data is charted. It turns out that’s pretty easy to do. First, enter the following,

K28 Row Increment

K29 0

Cell K29 will hold a value used to increment the rows being retrieved. Changing the value in K29 will change the rows being retrieved.

In cell H5 modify the INDEX function to include the row increment in K29. The new H5 INDEX function will be,

=INDEX($B$5:$E$28,$A5+$K$29,B$3)

Now copy H5 down and then across to fill H5:K16. You may need to reformat the cells.

To see what happens if the number in K29 changes, type in values from 1 to 5 and see how the chart data updates.

Controlling the Chart with a Scroll Bar

The only thing left to make this Excel timeline dynamic is to add a scroll bar that changes the data being charted. If you have not used Excel Form Controls before you will need to add the Developer tab to the ribbon. To add the Developer tab, check this article.

Draw the scroll bar under the timeline chart by,

1. On the Developer tab, in the Controls group, click the Scroll Bar tool in the Forms Controls. This enables you to draw a scroll bar.

Use the Form Control to insert a scroll bar.

Use the Form Control to insert a scroll bar.

2. Draw the scroll bar by dragging the mouse underneath the chart from top left to bottom right where you want the scroll bar. Hold down the Alt key as you do this if you want the scroll bar to align with worksheet cell corners. If you need to delete the scroll bar, right mouse click on it and select Cut.

3. Click outside the scroll bar you have drawn.

4. Right click the scroll bar and select Format Control. Enter the following values in the Format Object dialog box and then click Ok.

Current Value 0

Minimum Value 0

Maximum Value 12

Incremental Change 1

Page Change 4

Cell Link $K$29

Use the Format Object dialog box to control the scroll bar behavior.

Use the Format Object dialog box to control the scroll bar behavior.

5. Click in a cell to deselect the scroll bar.

Your Excel chart timeline should now look like this,

The dynamic Excel timeline chart allows you to scroll backwards or forwards through time.

The dynamic Excel timeline chart allows you to scroll backwards or forwards through time.

When you drag the slider across the scroll bar the chart will show a scrolling timeline. Clicking in the scroll bar will increment changes by 4, the amount you entered in Page Change in the Format Object dialog box.

Now, that you have a cool Excel timeline chart you can hook it up to your Excel project management system or marketing events calendar.

<Download the Excel XLSX template containing static and dynamic Excel timelines.>

Share the power...