How to make a chart of work on projects in Excel in 10 steps

2024 Author: Malcolm Clapton | [email protected]. Last modified: 2023-12-17 03:44

Using the usual tools of the most popular MS Excel spreadsheet editor, you can draw up a visual diagram of work on projects, which will help you track how each task is progressing and how much remains to be done.

If you are managing projects, it is important to always know how many tasks you have and when they need to be completed. And if you draw up a visual diagram, it will be easier to explain the key points in the life of projects to the team involved in the work, as well as to other stakeholders.

Special programs are often used to manage projects, but you can also create a clear and accessible schedule in the familiar MS Excel.

The project work diagram shows how events and tasks relate to each other over time. A well-designed diagram reflects important information about the implementation of a specific task and the development of the project as a whole, helping to see the big picture.

To create such a chart in MS Excel (versions 2007 and later), you need to take only 10 steps.

What is what in this diagram? Key details are shown with colored lines and planks:

• line of the current day - a red vertical line that shows where you are today;
• task deadlines - gray horizontal bars that show how long it will take to complete each task;
• readiness of tasks - green horizontal lines that show at what stage the task is being completed and how much remains to be done.

In different versions of MS Excel, the actions may slightly differ, but one way or another the process will be universal, and any user can cope with scheduling.

You can watch a detailed video instruction (in English), it will take half an hour of your time.

Step 1. Beginning, think about the result

To cope with the task without any problems, print or draw on a sheet of paper the schedule as it should look in the end. Hold the piece of paper in front of you to compare each step with a pattern. The diagram does not need to be carefully drawn, just fix the shape of the diagram and keep it handy, it will be a good reminder.

Step 2. Create a table with data

To build a graph, you first need to draw up a table. In the example, the project is divided into four phases, each with tasks.

Form a table below the graph. The 30th row contains the column headings.

Let's figure out what the data in the table means:

• Phase: "Phase 1", "Phase 2", "Phase 3", "Phase 4" or "Today". Shows which stage of the project the event corresponds to.
• Type of: "Phase", "Task", "Today". Shows the type of event, delimiting tasks and phases of project execution.
• Start date: start date of the event.
• Event: shows the name of the event in the diagram.
• Timing: shows how many days it takes to complete the task.
• Percentage readiness: shows the degree of completion for each task and phase, 0% - the task has not started, 100% - completed. This indicator for the "Phase" category is not related to the completion of tasks and is set as an estimate. To decide by what percentage a phase is complete, you need to apply your own rules.
• Ready in days: the period for which the event readiness indicator was reached in percent. For a task that takes 10 days and is 50% complete, this will be five days (10 days × 50%).
• Height: the height value is important for decoration because it determines how high or low the event will be reflected in the schedule map. You can specify any values, but it is better to take small numbers close to zero. The example uses a range from +25 to -25. If the height is positive, the event will be reflected on the chart above the horizontal axis, if negative - below the axis.

Step 3. Create a chart with X and Y axes, add the first data from the "Timeline" column to it

It's time to create a chart to visualize the event data from the table made in the previous step. Use a scatter plot with X and Y axes because it allows you to freely position the base coordinates on both axes.

Follow the instructions:

• Select the "Insert" tab in the main menu.
• Then select "Charts" → "Scatter" → "Scatter with markers", an empty chart will appear in front of you.
• Hook the edges of the chart and resize and resize the chart so that the chart field covers the range from cell B4 to cell K26 (press and hold Alt to align the chart area exactly to the cell borders).
• Right-click on the diagram and select the "Select data" menu item.

Add a series of data to the range by clicking on the "Add" button in the dialog box that opens.

1. To change the name "Series name" put in the corresponding field the value of cell E30 "Dates".
2. For the X-axis value, select the appropriate field and select cells C33: C46 Start Date.
3. For the Y-axis value, select the appropriate field and select cells H33: H46 Height.
4. Click OK to close the add data window.
5. Click OK to close the data selection window.

These steps will give us a simple scatter chart with automatic formatting.

Let's make the event markers green:

• Right-click on any selected data marker, select change "Data series format".
• In the list that opens on the left, select the "Marker parameters" tab, then select the built-in marker type "Crystal". Increase the size to 10 pt.
• On the Marker Fill tab, select a solid fill. Change the fill color to green.

We now have a scatter plot representing the timeline. So far, green markers only show the start of tasks.

Change the name of the chart: double click on the text field with the name and enter your name.

We'll go back to the timeline options in step 5, add horizontal bars along the X-axis, but for now continue formatting the chart.

Step 4. Putting in order the appearance of the diagram and adding the names of events

We get rid of what we don't need in the diagram.

Y-axis … Hide the vertical axis, because it does not carry information load. Select a diagram, in the main menu on the "Working with diagrams" tab, select "Layout". Then choose Axes → Main Vertical Axis → Do Not Show.

Horizontal guides … They are also useless. Select the chart, in the main menu, go to the Layout tab, then select Grid → Horizontal Gridlines Along Major Axis → Do Not Show.

Chart legend … We will then replace it with a more beautiful one, but for now disable it: "Layout" → "Legend" → "No".

Finally, we display the data labels for each marker. In the main menu on the "Layout" tab, select "Data Signatures" → "Left".

How to change data in token signature

Entering data about each event in the diagram is long and difficult, because you have to select each marker separately and rename it.

But this process can be accelerated four times:

• Click on the caption of the first marker (upper left corner of the chart) to select all values at once. Do not click on the green marker, this will select other objects!
• Click again on the marker caption to edit the single name.
• In the formula input field, type the symbol =.
• Click on cell D33, it contains the event "Phase 1", press Enter.
• Repeat the first four steps for the remaining labels for each marker.

How to pin areas

It is helpful to pin the regions on line 28 so that you can always see the diagram while working on the document. The first 27 lines will remain in front of your eyes, even if you scroll through the rest of the lines.

To freeze areas:

• Select cell A28.
• Select the "View" tab from the main menu.
• Choose Window → Freeze Areas.
• Select "Freeze Areas" from the drop-down menu.

A black horizontal line will appear above the 28th line. Everything above is now docked and only the bottom lines will move when scrolling.

Step 5. Add error bars to display timelines to turn the chart into a visual timetable

The horizontal error bars on the diagram will show how long it takes to complete each task. To do them:

• Highlight the graph.
• From the main Chart Tools menu, select the Layout tab.
• In the drop-down list in the upper left corner (plot area), select the "Timeline" data series.
• On the Layout tab, select Error Bars → Advanced Error Bars Settings. A dialog box will open.
• In the drop-down list in the upper left corner, select the X-axis error bars to format the chart.
• In the dialog box to change horizontal bars, select the check box for the Plus pin. End Style → No Point.
• In the "Error value" list, select the "Custom" item and click the "Specify value" button. In the new window “Configuring the error bar” that opens, specify the “Positive error value”. To do this, place the cursor in the appropriate field and select the range of cells from E33 to E74. Click OK.

Now gray lines extend from the green markers to the right, they show how much time you need to spend on this or that task. They need to be formatted for clarity:

• In the Format Error Bars window, go to the Line Color tab. Select Solid Line. Make the fill gray.
• On the Linetype tab, increase the line width to 4 pt.

To better navigate the diagram, you need to draw thin vertical lines down from the markers. To do this, in the drop-down list in the upper left corner, select "Error bars along the Y axis". The dialog box displays vertical error bars. Select the Minus direction, End Style → No Point, Error Amount → Relative Value, enter 100% in this field. Choose the color and thickness of the lines yourself.

Step 6. Add the readiness value to the chart

It's time to add a series of data to the chart that shows the percentage of the task completed.

If a task takes 10 days and is 50% complete, the bar for completion will be half full. This is a measure for tracking work and does not include weekends and holidays. To add new information to the chart, follow the instructions below.

• Right-click on the chart area, select "Select Data" from the context menu.
• Click the "Add" button, in the window that appears, define the "Row Name" by selecting cell G30. The data range for the X and Y axes is the same as in step # 3.
• Red markers will appear on the diagram, which will hide green ones.

Step 7. Formatting the new data metrics

To make the chart reflect in the same style, and the data does not overlap, we format the markers:

• In the drop-down list in the upper left corner, select "Ready Row". Immediately below the drop-down list there is a button "Format of the selected fragment". Click it, a dialog box will open.
• Change the marker parameters. Select the "Crystal" shape type, set the size to 10 pt, and select a solid green fill for the marker. Close the dialog.
• Without removing the selection of markers, in the main menu on the "Layout" tab, click the "Data labels" button, select the "Do not show" item in the menu that appears.

Step 8. Add error bars to track the percentage of completion of tasks

Now you need to add new planks so that you can see at a glance what stage the tasks are at.

• Select Row Ready again from the drop down list on the left. Press the button "Error bars" in the main menu. Select "Advanced Options" from the drop-down menu. A dialog box will open. Set the vertical slats to a fixed value by entering zero in the corresponding field.
• Switch to the parameters of the planks parallel to the X-axis. Set the direction to "Plus". End Style → No Point, Error Amount → Custom. In the Positive Error Value dialog box, specify the range of cells G33-G47.
• Change the color of the lines to green by choosing a solid fill. Set the line width to 7 pt.

The horizontal green lines show whether the tasks have been completed in part or in full.

Step 9. Add a vertical line representing the current day

The red line shows what day it is, and makes it clear how the project as a whole meets the deadline.

• Call the context menu for the diagram with the right button. Select "Select Data".
• Click the "Add" button, enter the word "Today" in the "Series name" field. Enter the range of cells C31-C32 for the X-axis value and H31-H32 for the Y-axis value.

Two new markers have appeared on the chart. Label them: select the data labels and write the word "Today" in the formula bar. Right-click on the signature and select "Data Format" from the context menu. Put the position of the caption "Above".

• Go to the main menu on the "Layout" tab.
• Select "Today Row" in the drop-down list in the upper left corner.
• Add error bars with additional parameters, following the instructions as in the fifth step. You will need a plank along the vertical axis, with the Minus direction, the final style, No point, with a relative value of 100%.

You now have a vertical line representing the current day. Change the color of the line to red, increase the size to 3 pt.

In the sample, the current date is fixed, so it will not change. But for projects that are actively developed, a different way of recording data is recommended. To do this, put the date in cell B2 by entering the formula = TODAY () and specify in the data selection dialog box for the value of the X axis cell B2 instead of the range that you specified earlier.

Step 10. Add text blocks

Now the diagram lacks only a legend for the legend so that it can be understood by the whole team.

• Select the diagram, go to the main menu on the "Insert" tab. Select "Lettering".
• Position the label area in the upper right corner of the chart. Select the border of the text box with the right mouse button, select the "Format Shape" item in the context menu. In the window that opens, select the fill type "Solid", the color is gray. Format the label. You can insert any number of blocks.

Now you can make a diagram in 20-30 minutes using only your favorite editor MS Excel.