Office hacks: create a concise Excel project schedule to complete

To create a project schedule with multiple links, we can use a combination of tick symbols and signal icons, each set of tick symbols will automatically display a different style of signal icons, and the number of cells of signal icons will indicate the completion status. In this way, it is clear at a glance (Figure 1).

2005A-JDB-1

Specifically for this example, suppose the whole project schedule contains 7 stages as shown in the figure, and the project is divided into 6 projects from A to F. If all 7 stages are indicated as “tick”, the project will be completed in the following way If all the 7 phases are indicated as “√”, “completed” and full signal icon will be displayed at “completion status”; if there are 4 to 6 “√” in the 7 phases, “completed” and “full signal icon” will be displayed at “completion status”. If 4 to 6 of the 7 phases are marked with “√”, then “In Progress” and a 3-frame signal icon will be displayed at “Completion Status”; if 1 to 3 of the 7 phases are marked with “√”, then “Completion Status” will be displayed at “Completion Status”. “, then the “completion status” will show “in progress” and 2 signal icons; if there are no “√” in any of the 7 cells, then “completion status” will show “not started” and full-space signal icon. Here we will open the production.

First, we enter the statistical formula “= COUNTIF (B2: H2, “√”)” in the cell “I2”, statistics “B2: H2” in “√” the number, and then fill down, you can count the number of “√” in front (Figure 2).

2005A-JDB-2

Next, select the I column data area, right-click, select “Set cell format”, in the pop-up window “Number” tab “Category”, select “Custom”, enter “[<1] “not started”; [<7] “in progress”; “completed”;” in the type. This type means: less than 1, “Not Started”; less than 7, “In Progress”; otherwise, “Completed” is displayed (Figure 3).

2005A-JDB-3

Next, set the icon set. Select the I data area, switch to the “Start” tab, click “Style → Conditional Format → Icon Set → Other Rules”, in the pop-up window of the “icon style” at the choice of “Four levels” set (of course, you can also choose your favorite style according to your needs), set the following “Type” to “Number”, the first set is “> 6”, the second group “> = 4”, the third group “> = 1”, OK can be (Figure 4).

2005A-JDB-4

Finally, the necessary settings for the form style can be.

QQ图片20210420165237 

Leave a Comment