How to Update the Project Status Automatically Using Conditional Formatting

Small Excel tips for a large project

Photo by Tobias Tullius on Unsplash

This story will cover something useful for project management.

It’s about using Excel for updating the checklist for certain project based on its progress, as shown in the image below:

As you can see, the projects are automatically set to DONE and changed in color should all the steps are checked else they are set to OPEN.

To recreate the same system, you only need to do these 3 things:

• IF() function to return the DONE/OPEN
• COUNTA() to count the checked cell(s)
• Conditional Formatting to format the cell.

Let’s do this step by step, starting with the following table:

IF+COUNTA

Select cell E2 and enter the formula:

=IF(COUNTA(B2:D2)=3;”DONE”;”OPEN”)

Then copy it down.

IF() will check if the logical test is TRUE or FALSE.

The logical test here is whether the checked cells in column B to D equal to 3

COUNT(B2:D2)=3