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.)
Tip: To see any figure in larger format, click on it to display it in a pop-up window.
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.
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.
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,
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.
In cell H5 enter the INDEX function,
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.
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
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,
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.
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.
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
5. Click in a cell to deselect the scroll bar.
Your Excel chart timeline should now look like this,
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.