Contents
Suppose we are working on a long and complex project with several phases. The task is to visually show the entire chronology of work on the project by placing the key points of the project (milestones, milestones) on the time axis. Approximately like this:
In project management theory, such a schedule is usually called a calendar or project timeline (project timeline), although I also met another -language analogue – “timeline”. In any case, the main thing is not how to name it, but how to build it. Go…
Step 1. Initial data
To build, we need to arrange the initial information on the milestones of the project in the form of the following table:
Pay attention to two additional service columns:
- line – a column with the same constant near zero for all cells. It will give a horizontal line on the chart, parallel to the X axis, on which nodes will be visible – milestones of the project. In principle, a full zero could also be used, but then the graph coincides with the X axis, which then gives problems with setting the appearance of the chart in Excel 2007-2010. New Excel 2013 perceives zeros calmly.
- Callouts — invisible columns for raising signatures to milestones by a given (different) value, so that signatures do not overlap. Values 1,2,3 etc. set the level of elevation of signatures above the time axis and are chosen arbitrarily.
Step 2: Building the Foundation
Now we select everything in the table except the first column (i.e. the range B1:D13 in our example) and build a regular flat chart with markers on the tab Insert – Graph – Graph with markers (Insert — Chart — Line with markers):
We remove the grid lines, vertical and horizontal scales and the legend. You can do this manually (selection with the mouse and the key Delete) or by disabling unnecessary items on the tab Layout (Layout). The result should be the following:
Now select a row Callouts (i.e. broken orange line) and on the tab Layout select team Lines – Projection lines (Layout — Lines — Projection Lines):
From each point of the upper graph, a perpendicular will be lowered to the lower one. In the new Excel 2013, this option is on the tab Constructor – Add Chart Element (Design — Add Chart Element).
Step 3. Adding Step Names
This part will be easy for those who have already dared to install the new Excel 2013 and more difficult for those who are still working with older versions.
Everything is simple in Excel 2013. As I already wrote here, it can make data point labels simply by taking text from any user-specified range. To do this, select the data row (orange) and on the tab Constructor select Add Chart Element – Labels – Advanced Options (Design — Add Chart Element — Data Labels), and then in the panel that appears on the right, check the box Values from cells (Values from cells) and select the range A2:A13:
Versions of Excel 2007-2010 and older do not have this option, but you have two alternatives:
- Add any labels to the orange graph (values for example). Then select each signature in turn, put the equal sign in the formula bar and click on the cell with the name of the stage from column A. The text of the selected signature will be automatically taken from the selected cell:
- With a large number of stages, the first option, of course, does not please with its “hand-to-hand combat”. Therefore, for the wholesale insertion of labels from cells, you can use additional VBA add-ons. In particular, the XYChartLabeler add-on (by Rob Bovey, Excel MVP). Download the add-on, install and get it on the tab Add-ons (Add-ins) button XY Chart Labeler — Add Chart Labels. After clicking on it, a dialog box appears, where you can set the range with data for the labels on the chart:
Step 4: Hide the Lines and Shine
Let’s make the final edits to bring our already almost finished diagram to a complete and final masterpiece:
- Select a row Callouts (orange line), right-click on it and select Data series format (Format Data Series). In the window that opens, remove the fill and color of the lines. The orange graph, in fact, disappears from the chart – only the captions remain. Which is what is required.
- Adding date labels to the blue time axis on the tab Layout – Data Labels – Additional Data Label Options – Category Names (Layout — Data Labels — More options — Category names). In the same dialog box, labels can be positioned below the graph and rotated 90 degrees, if desired.
- Project schedule (Gantt chart) in Excel using conditional formatting
- Video tutorial on creating a project schedule (Gantt chart) in Excel 2010
- What’s New in Charts in Microsoft Excel 2013