Sometimes the best Excel projects start with something the program can't do out of the box.Surprisingly, Excel doesn't have a built-in timeline chart.So I experimented with a regular line chart, and 10 minutes later, I had a series of events mapped out in a dynamic timeline.
Want to give it a try this weekend? Here's what I did.Set up your timeline data Start with three simple columns As with any chart in Excel, the first thing you need to get straight is the underlying data: In cells A1:C1, type three column headers: Date, Event, and Y-axis.Select these three cells, then press Ctrl+T to format the area as an Excel table.
Make sure you check My table has headers in the dialog box.Select a cell in your new table, and in the Table Design tab, name it Timeline.Select the first cell in the Date column, and in the Number group of the Home tab, choose a Date format.
Make sure Excel recognizes your entries as dates, not text.Fill in your Date and Event columns with your timeline details.Make sure all the dates are in chronological order (earliest first).
In the first cell of the Y-axis column, type the following formula and press Enter.This generates a sequence of positive and negative values so your data points are clearly spaced out.=CHOOSE(MOD(ROW()-ROW(Timeline[#Headers])-1,6)+1,10,-10,20,-20,30,-30) Close Notice how this produces a repeated sequence of 10, -10, 20, -20, 30, and -30.
This means the first marker on your timeline will appear just above the x-axis line (10), the second just below it (-10), the third a bit higher than the first (20), and so on.Insert your timeline chart A basic line chart is all you need Now that your data is set up, you're ready to insert a line chart.This will form the basis of your timeline chart: Select the Date column in your table (including the header).
Hold Ctrl, then select the Y-axis column (again, including the header).Open the Insert tab.Expand the Insert Line or Area Chart drop-down menu, and choose the Line with Markers option.
At this point, you'll see the foundations of your timeline: the dates are marked on the x-axis, and the event markers are nicely spaced apart.Close But it still looks more like a line chart than a timeline, so you need to make a few tweaks.Format your chart Let your timeline take shape First, let's get rid of the gridlines, which aren't needed for this timeline.
Click one to select them all, and press Delete.You can also select and delete the chart title.The next tweak is to turn the markers into vertical lines: With the chart selected, click the + symbol that appears to the right.
Check Error Bars.Expand the Error Bars menu, and click More Options.In the Format Error Bars pane, check Minus and No Cap.
Now, check Percentage, then type 100 into the Percentage text field and press Enter.Close The chart now has vertical lines that connect the individual data points back to the x-axis.You still have the horizontal line that makes the timeline look like a standard line chart, so let's remove it.
Select the line, click the paint bucket icon in the Format Data Series pane, and check No line.OK, things are starting to take shape now, but the axes are still a bit messy, so let's clean them up: Select the y-axis labels and press Delete.Select the x-axis labels.
Click the chart icon in the side pane to open the Axis Options menu.Expand the Tick Marks section and set Major type and Minor type to None.In the Labels section, set Label Position to None.
Close Now, you have something that looks like a timeline chart.The problem is that it doesn't have any data labels (as you've just removed them), so let's re-add them, but in the right place: Click one marker to select them all.Right-click one of the selected markers and click Add Data Labels.
Right now, the data labels show numbers from the helper column, so you need to change that.First, click one of them to select them all.Click the chart icon in the side pane to open the Label Options menu.
Then, do these three things in this order: Check Category Name.Uncheck Value.Check Value From Cells.
Close As soon as you check Value From Cells, the Data Label Range dialog appears.Place your cursor in the text field, select your Event column (excluding the header), and click OK.Now, your chart actually looks like a timeline.
Add the finishing touches Make your timeline look the part You could leave your timeline there, but a few final polishes will make it stand out.Let's tidy up the data labels first.At the moment, the event and date are separated by a comma, but they'll look better if they're on separate lines.
Also, they're centrally aligned, but left-aligning them will make them more clearly associated with the vertical indicators you added.So, do these things: Click one data label to select them all.In the Label Options section of the Format Data Labels pane, set the Separator option to New Line.
With the data labels still selected, head to the Home tab, then click Align Left.Select only the plot area and click and drag the rightmost handle a few pixels to the left.This stops the rightmost text label from overlapping its marker.
Close If any of the text labels are much longer than the others, think of ways to reduce them in the source table.This will make sure there aren't any untidy overlaps in the chart.Next, select the outer edge of the chart and click and drag the left or right handles outward to give it a bit more room to breathe.
Alternatively, right-click the timeline, click Move Chart, and select New Sheet.Finally, to show a clear sense of chronology, add an arrow to the end of your x-axis line: Select the x-axis.In the Format Axis pane, click the paint bucket icon.
Expand the Line options.In the End Arrow type menu, choose an arrowhead.Adjust the End Arrow size option so the arrow is clearly visible.
Close And that's it! Try adding more rows to the end or in the middle of your original dataset, and watch the timeline adjust automatically.Just remember that the Date column must always stay in chronological order.One more Excel project for your weekend Excel has plenty of chart types, but there are still some obvious omissions.
A timeline chart is one of them, and a Gantt chart is another.If you've still got some time left this weekend, you can build your own dynamic, color-coded Gantt chart with a few formulas and conditional formatting rules—and it will update automatically as your project changes.
Read More
