Using Excel to work with task management software

Original: Using Excel to work with task management software skills

Using Excel to work with task management software

In project management, there are a variety of tools on the Internet to choose, in the end with which, once troubled me. I am a person with mild obsessive-compulsive disorder, always like to try a variety of systems to compare their differences, trying to choose the most perfect system. However, after the comparison of various systems, there are always a variety of shortcomings, and in the actual project, there are always a variety of their own to achieve the effect can not be achieved. In fact, we use the Excel tool, we can achieve most of the project management functions.

An example

Click "Here" to download the template

The features implemented in the example include

• Task is added within 7 days, the background is yellow (based on date of presentation)
  
• After the task starts, the task name is displayed in bold, purple
  
• Different priority levels show different background colors, such as red for urgent tasks
  
• Different task statuses, showing different background colors, where the background color of finish, advance, pending, Reopen affects the entire row of records
  
• For tasks that are not completed in the expected time, the expected time, the cell background of the expected time bar becomes red, and is highlighted. Time is compared with the current actual time
  
• Task Progress Display Percentage
  
• The task status is set to finish, etc., prompting for additional completion time, documentation, etc.
  
• The current handler starts with ">", then Highlights

Do you think the above Excel format is too complex, days, I did not update a project, all to set the format of this cell, how much work!!

Am I going to teach you such stupid tricks? The world's most powerful office software, there will not be a beautiful solution? Let's take a look at the following ways to automate your style changes:

Implementation Notes

1. Percentage Show progress

1.1 Add a new data bar

1.2 Change the maximum value of the data bar to 1

1.3 Format cells as percentages

2. Change the contents of the current cell change the style of the cell

2.1 Add a conditional rule

2.2 Select the format only for cells that contain the following, and then depending on the value or content settings you want to set

3. Change the background color of the entire row based on the conditions of one or more cells

3.1 Add a conditional rule

3.2 Select "Use formula to format cells", which can only be selected, otherwise it will not affect other cells

3.3 Setting multiple conditions with and and OR

=OR($J2="完成",$J2="提前")
=AND($G2<TODAY(),$G2<>"",$J2<>"完成",$J2<>"提前",$J2<>"待确认")

3.4 Using if to determine the condition

=if(condition,true,false)

4. Time Comparison

4.1 Direct comparison

=$a3>today()

4.2 Using the DATEDIF function

=datedif($a3,today(),"d")<=7

4.3 using hour and other letters

=if(hour(today())>5

5. Quantitative statistics count,sum, and additional conditions

Number of statistics tasks
="aa (任务数："&COUNTA(D2:D16)&"；完成率："&TEXT(SUMIF(P2:P16,"<="&TODAY(),H2:H16)/COUNTIFS(J2:J16,"*",P2:P16,"<="&TODAY()),"0%")&")"

Scan QR Code, pay attention to horse non-horse 's public number, the latest article automatically push:
   

Using Excel to work with task management software