How to calculate percent complete in Microsoft Excel

In Excel, “percent complete” usually refers to a calculation that shows the progress made towards completing a task or project as a percentage of the total work required. It is an important tool for anyone managing a complex or long-term project. By regularly updating the percentage completed for each task, you can monitor progress and identify areas where work is falling behind, allowing you to adjust your plans and resources as needed to stay on track. 

The generic formula for calculating the percent complete is the number of total completed tasks divided by the total tasks required i.e. Completed / Total. For this calculation, we can use some built-in formulas i.e. COUNTA and COUNTIF function or we can simply calculate the percent complete with the generic formulae.

To illustrate the calculation of percent completion for a construction project, let’s consider a data set with 6 tasks, each requiring 50 hours of work, and information on the hours of work completed and tasks completed. 

We will explore three methods for calculating percent completion based on distinct scenarios. The first method involves using numeric values, such as the hours of work completed. The second method involves using a column to indicate whether the task is completed or not, with a blank cell indicating incomplete and a “complete” indicating completion. The third method involves using a column to indicate whether the task is completed or is in progress, with a “complete” indicating completion and an “in progress” indicating in progress.

Method 1: Calculating the Percent Complete using Hours Worked i.e. Numeric Values

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to calculate the percent completion of the project.

Step 2 – Use the Percent Complete Generic Formulae

• The formula for percent complete is  (Completed Tasks / Total Tasks).
• The syntax will becomes SUM(D2:D8) / SUM(C2:C8).
• Where D2:D8 is the range containing the work hours completed for each task and C2:C8 is the range containing the total work hours required for each task. 
• We use the SUM function to calculate the total work hours completed and the work hours required.

Step 3 – Press the Enter Key

• Press the Enter key.

Step 4 – Change the Cell Format

• Change the cell format to percentage.
• This can be done by the Percent Style button in the Numbers section of the Home tab or we can use the CTRL+SHIFT+% shortcut keys.
• The percent completion of the project will be displayed.

Method 2:  Calculating the Percent Complete from the Tasks Completed (If the Cells for the Progress of Tasks are Blank) 

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to calculate the percent completion of the project.

Step 2 – Use the COUNTA Function

• The COUNTA function returns the non-blank cells in the range.
• We can use it to determine the number of tasks completed (if the fields for the in-progress tasks are left blank) and then divide it by the total number of tasks which can also be calculated by the COUNTA function.
• Hence, the syntax will be:  COUNTA(B2:B8) / COUNTA(A2:A8)

• The COUNTA(B2:B8) returns the number of completed tasks.
• The COUNTA(A2:A8) returns the total number of tasks.

Step 3 – Press the Enter Key

• Press the Enter key.

Step 4 – Change the Cell Format

• Change the cell format to percentage.
• This can be done by the Percent Style button in the Numbers section of the Home tab or we can use the CTRL+SHIFT+% shortcut keys.
• The percent completion of the project will be displayed.

Method 3:  Calculating the Percent Complete from the Tasks Completed (If the Cells for the In-Progress Tasks are not Blank) 

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to calculate the percent completion of the project.

Step 2 – Use the COUNTIF and COUNTA Function

• The COUNTIF function returns the number of cells containing a specific value.
• We can use it to determine the number of tasks completed (if the fields for the in-progress tasks are not blank) and then divide it by the total number of tasks which can be calculated by the COUNTA function.
• Hence, the syntax will be: COUNTIF(B2:B8, “ COMPLETE” ) / COUNTA(A2:A8)
• The COUNTIF(B2:B8, “ complete” ) specifies the number of completed tasks by returning the number of cells containing the specific text i.e. complete.

Step 3 – Press the Enter Key

• Press the Enter key.

Step 4 – Change the Cell Format

• Change the cell format to percentage.
• This can be done by the Percent Style button in the Numbers section of the Home tab or we can use the CTRL+SHIFT+% shortcut keys.
• The percent completion of the project will be displayed.

Conclusion:

Undoubtedly, the most precise approach among the three is the first method, which relies on quantitative data analysis. The rationale for this preference is that the other methods merely involve making informed estimations regarding task progress, whereas the first method involves objectively measuring the percentage of completed tasks based on factual quantitative data. Therefore, when seeking to determine the percentage of completed tasks, it is advisable to utilize quantitative data to calculate progress, rather than relying on other sources of information.