So, we have a table listing the stages of the project, the start and end dates and the duration of each stage:
The task is to build a chart-calendar chart using standard tools, as in the figure:
Let’s go, step by step:
Select the source data for the chart – range A2: B13 and choose from the menu Insert – Chart, type — Ruled with accumulation:
Push the button Next and go to the 2nd step of the Chart Wizard. On the tab Data range Choose Rows in columns:
On the Advanced tab Row press the button Add, set the cursor in the field The values and select cells with stage durations (C2: C13):
Press the button Next and on the third step of the Wizard on the tab Legend uncheck the box Add legend. All – click Finish. It should look something like this:
Do not be afraid – everything is going according to plan – you just need to “bring to mind” our diagram. To do this, right-click on the vertical axis with the names of the stages and select from the context menu Axis Format:
On the Advanced tab Scale in the window that opens, put two “checkmarks” – Reverse order of categories и Intersection with the Y-axis in the maximum category. We press OK. Now let’s get rid of the blue columns. Double-click on any of them and in the window that opens, select an invisible frame and a transparent fill. You should get the following:
Looks like the truth already, right? It remains to correctly configure the range of data displayed on the chart. To do this, you need to find out the real contents of the cells from which the timeline begins and ends (yellow and green cells in the table). The fact is that Excel only displays the date in the cell as day-month-year, but actually stores any date in the cell as the number of days that have passed from 1.1.1900/38350/38427 to the current date. Select the yellow and green cells and, one by one, try to set the General Format for them (menu Format – Cells). It turns out 38340 and XNUMX, respectively. Let’s add three more days to the end date – we get XNUMX. Remember these numbers.
It remains to right-click on the horizontal time axis and select Axis Format and enter these numbers on the tab Scale:
After clicking OK the diagram will take the required form:
It remains to “bring some shine” – adjust colors, fonts, axis labels, etc. – I think you can handle this without my advice … 🙂
- Project Gantt Chart Using Conditional Formatting
- Video tutorial on creating a project Gantt chart in Excel 2007-2010