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.
0 kommentar(er)
