With its comprehensive computing and presentation capabilities, Excel offers a completely sufficient tool to manage projects effectively and vividly. With the project planning presented here, you follow the progress in a Gantt diagram on the basis of individual tasks. You can easily spot where it gets hot. The Gantt diagram is implemented with formulas in conditional formatting. Therefore no programming is necessary. Our example works with Excel from version 2010.
1. Create a project plan
To create a project plan, you first need tasks, a start date and an end date. Since this is a planning, the data is first entered as planning data, after which the actual start and end date are displayed. This gives you an overview of the planning and the actual status of the tasks. If the actual end date is later than the planned end date, this is marked as a critical path in another column. The project plan is set up as a basis after calendar weeks. In a further table, a project plan is then set up daily so that you have both possibilities at your disposal. Start by entering the column headings in cell A5
2. Enter test data
Select the headers in columns B to F. On the Start tab, in the Group Orientation, click the Text Break icon. As a result, breaks are generated during longer content without widening the columns. Only the column height is adjusted.
3. The Gantt diagram - visualize the visualization
Now create a few test tasks. Tap the text Task 1 in cell A6. Drag the entry into the cell A15 using the copier handle. Click on the smart tag displayed and select the Fill data series command. Now you have created ten tasks. The Gantt diagram is to show the overview by the week. To do this, click in cell G5 and type 1. Drag the value to the BF5 cell by copying the handle. Turn back the Fill Data option.
4. Enter Actual Times and Overlay
Select the complete worksheet. To do this, click in the table in the field between column A and row 1. Then, double-click between columns A and B. This optimizes all column widths, and the spreadsheet is displayed.
To create the Gantt chart, type a few test data into the table beforehand. Then you can check whether the functions work properly. In this example, the tasks start with the first week at weekly intervals. To do so, type 1 in column B6. Copy the cell up to the last task, and then select the option Filling in the SmartTag.
The same is done starting with column B6 and a 2 as first input. This will start and end the tasks overlapping by one week. The actual data is filled first with a 0. These data are already sufficient to create the Gantt diagram of the Plandats.
A Gantt diagram is a bar chart showing the time sequence of tasks graphically in the form of bars on a time axis. The individual tasks are visualized in the respective lines using a horizontal bar. The longer the bar, the longer the activity takes.
In this example, the tasks are stored in the weekly grid (1 to 52). The conditional formatting is to be used to display the plandata in the Gantt area. First, select the range below the weeks: G6: BF15. In the Format Templates group, click the Conditional Formatting icon. From the submenu, choose New Rule. The New formatting rule dialog box is displayed. In the Select rule type selection field, select the Use formula to determine the cells to be formatted entry. The Edit Rule Description area is now active. Formatting values into the line for which this formula is true now comes with the formula for generating the conditional formatting.
It is to be formatted if the week is less than or equal to the bis-KW and if the week is greater than or equal to the Von-KW. Thus, the end and the beginning of the planning must be taken into account. Therefore, you must use an AND formula: = AND (G $ 5 = $ B6)
Enter this formula. Now it's about creating the format. To do this, click the Formatting button. The Formatting Cells dialog box is displayed. Select the Fill tab. Select a color, for example, beige. Once you have completed these entries, you will see the planning data in the Gantt chart.
After your project is planned, the project will be realized. To do this, the actual data is maintained in the project plan. These data should also be shown in the Gantt diagram. To make sure that the calculation is correctly entered during the conditional formatting, type the actual weeks from KW to KW to the task 1. Select the area G6: BF15 again and insert a new rule via the conditional formatting. A formula is used to determine the cells to be formatted.
This time, you can compare whether the week is less than or equal to the bis-KW in the IST range and whether the week is greater than or equal to the Von-KW in the IST range. Thus, the end and the beginning of the actual data must be considered: = AND (G $ 5 = $ D6)
5. Delayed times in the column Highlight path
To do this, set the pattern format to Lines. If the entries are correct, the first week of the first task is displayed in dashed lines in the Gantt diagram, while the second week remains beige without dashes. Now you can work with the other actual data, the result is shown immediately in the Gantt area.
As long as everything goes according to plan, no further measures are necessary. But as soon as deviations occur, a critical path is created, which you must keep in mind. To do this, you have inserted the Path column in the project plan. This column should display when it becomes critical. To do so, click in cell F6 and enter an IF formula. As soon as the planned end in the actual data is exceeded, the value Critical should be entered. As long as this is not the case, you should enter the value plan in the field. Enter the formula = IF (E6> C6; "Critical;" Plan ").
6. Graphically display delays in the Gantt chart
Copy this formula to the last task. Now the value plan is displayed in each cell of this column. Then change the actual KW of the task 1 to the value 3. Immediately the path entry changes to the value Critical. To make the Crit entry in the column appear in the red color, reapply the conditional formatting. Select the range from F6 to F15. Select the Format only cells type rule type. In the rule description, you define the following settings: Format only cells with: Certain text, with content, = "Critical". Set the formatting font color red. The critical path is immediately apparent.
The critical path is also to be displayed in the Gantt area. You need a third conditional formatting for this area. First, the area must be marked with the calendar week, and a formula for determining the cells to be formatted is to be used as conditional formatting. This time three conditions must be checked
The first step is to check whether the actual data exceeds the target data. Then again the week data in the graph must match the week data in the input area: = AND ($ E6> $ C6; G $ 5 = $ C6 + 1)
7. Project plan daily with date
8. The correct order in the conditional formatting
9. Create a timeline with Excel
As formatting, you specify the red background color and the pattern format for lines in the Fill tab. Now the critical paths in the Gantt area are shown.
What is calculated in this example project on the basis of calendar weeks can also be calculated daily. To do this, first copy the complete table sheet. Then change the week entries in the time axis in calendar days. The easiest way to do this is to enter a date in the first field of the time scale and copy it with the filling functions until the desired end date. Now fill the plan and actual data with date entries. To ensure that the time axis does not become too long, format the display: Select the time axis and click the small arrow in the lower right corner in the Number area. The Formatting Cells dialog box is displayed. On the Numbers tab, select the Category category and choose Type 14.3. out. Finally, optimize the column widths of the time axis.
You can not only build your project planning on a day-by-day basis, it can also be done on a weekly, monthly, or quarterly basis. It is important that the formats of the plan area match the formats of the time axis.
You can create a maximum of 64 rules in conditional formatting for a range. To prevent the rules from conflicting, they must be executed in the correct order. To change the order, enable the Manage Rules entry in the Conditional formatting command. Now all the rules created for the area are displayed. Use the up and down buttons to move the rule order. In this window, you can also edit and delete rules. For the project planning, keep the order Critical, Actual, Plan.
Another interesting presentation is a timeline for your project. First, create a new spreadsheet. Select the fields B5 to I5. Right-click one of the selected fields and select the Format Cells command from the context menu. Activate the Frame tab and select a line type. In the right pane, select the upper frame icon. Then click the OK button. Now mark the area B4 to I5 and select the Format Cells command from the context menu. In the Frame tab, select the middle frame icon. Click on the OK button to set the time frame.
Now tap the events to the individual cells into the cells above the time stream. Below the time beam, enter the date values. Select the date values and select the Format Cells command from the context menu. Click on the category Date and type 14.Mrz. This displays the date in this short form. You can now set these entries in color. The easiest way is to select the conditional formatting in the menu. Drag the cursor to color scales and select a color scale according to your needs. The time beam has already been completed.
You can find more information about this here!
No comments:
Post a Comment