#FREE EXCEL PROJECT MANAGEMENT TEMPLATE UPDATE#

IF logical test & Conditional Formatting is most commonly used to prepare a comprehensive project plan.Gantt Chart is also used to prepare and track a project plan.It also helps quantify the resources requirements for the allocated task and determine budgets and timelines for each task & its completion.It outlines the goals and objectives of each task with an actual planned start date, actual executed start date & estimated end date or the due date of It.Things to Remember about Project Management Template in Excel You can apply conditional formatting to the status parameter range, with green color for the cell containing the keyword вЂњCompletedвЂќ & red color for the cell containing вЂњPendingвЂќ so that you will come to know the status of each task. You can format the task table for a better appearance with the dual color tone and apply a border to the project plan. If you change from pending to completed, color changes are observed in color bars. Now, if you click on any of the status parameters, you can observe the dropdown list. вЂњCompleted & PendingвЂќ separated by a comma. Simultaneously you can create & apply a data validation list to the status range by selecting the complete status range, press ALT+D+LВ so that the вЂњData ValidationвЂќ popup appears, under the вЂњValidation criteriaвЂќ in the вЂњAllowвЂќ dropdown, select вЂњListвЂќ and in the source enter the two status parameters, i.e.

#FREE EXCEL PROJECT MANAGEMENT TEMPLATE UPDATE#

If you enter or update the end date & duration of a task, automatically, the color bar width changes based on the completion date you enter.ДЄfter applying the formula, the output is shown below. Now, the date range color bars need to be linked with the end date of each task this needs to be done because once you complete the pending task in the activity list, the duration of the task needs to be entered. Now, you can observe only colored data bars appear.

Now, I need to remove the text content on the color bars, it can be done by selecting the entire date range of color bars and right-click, select Format Cells option, under that select вЂњCustomвЂќ in the category of number format, enter semicolon three-times under вЂњTypeвЂќ and click OK. Now, the cells which contain completed will appear in red color.

In the Format Cell, click on Fill under that, select red color, click OK. Similarly, under New Formatting Rule, select вЂњFormat only cells that containвЂќ, under the cell values, select вЂњequal toвЂќ and enter the keyword вЂњPendingвЂќ, click on the format option for color selection. Now the cells which contain completed will appear in green color.ДЄfter selecting a color, the output is shown below. In the format cell, click on Fill under that, select Green color, click OK. Under the New Formatting Rule, select вЂњFormat only cells that containвЂќ, under the cell values select equal to enter the keyword вЂњCompletedвЂќ, click on the format option for color selection. Now, select the entire date range & apply conditional formatting to the date range by applying a new rule. Make the text alignment of the date range to the center and remove the gridlines & table borders. So that it gives status reference values if the IF logical condition criteria meet. By selecting the whole date range, you need to press CTRL+R & CTRL+D keys. Now, apply this formula to a whole date range, i.e. Similarly, I will be dragging this formula down I need to lock row reference as well, i.e. $C7 & $D7) & the status reference column as well, i.e. I apply this formula towards other columns to the right-hand side (Other date range), to get an output, I need to lock column C & D (i.e. If I want to apply this formula to a whole date or project range, then I need to modify the formula with mixed reference, i.e. =IF(AND(Jan 1 st 2019>= Jan 1 st 2019, Jan 1 st 2019<=Jan 2 nd2019), If yes or if the criteria meet, give the value of status Cell reference or leave it blank). Illustration #3 вЂ“ Formula Explanation (Two Conditions) Set the column width as 4, and row height is adjusted for a better appearance of text content. Now, enter the alignment to 90-degrees orientation & format it in a similar way by dragging that task 1 start date to the last task end date or until you get Jan 30 th, 2019. Go to Home > Format > select Format Cells option.ДЄfter selecting the Format Cells option, the Format Cells window appears.

Illustration #2 вЂ“ Project Timeline Creation After using the formula, the output is shown below.

Free excel project management template