# How to Create a Project Time Estimation Sheet in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to create a project time estimation sheet in Excel, this article will be helpful for you. Along with time estimation, we will also show the cost estimation of projects easily here. You can download the free template also in the following section. So, let’s get into the main article.

## 4 Steps to Create a Project Time Estimation Sheet in Excel

In the following paragraphs, we have illustrated the procedures of making a complete project time estimation sheet with the help of Excel. A project time estimation sheet is helpful for calculating the total time needed for the completion of a project.
For creating this article, we have used Microsoft Excel 365 version. However, you can use any other version at your convenience.

### Step-01: Creation of Basic Outlines to Form a Project Time Estimation Sheet in Excel

In this step, we will form some basic outlines required for the project estimation sheet.

• First, we need to have some basic details of the project summary. In the Data sheet, we garnered the lists of tasks, employee names, their designation, and costs required per hour to complete each task. • Now, create another sheet named Template.
• Make the following outline in this sheet with 7 required columns; SL No, Tasks, Designation, Name, Planned Weeks; Estimated Weeks, Cost per hour, Planned Cost, and Estimated Cost. • Now, move to the right side of this sheet to add 10 more columns for 10 weeks to show how much percentage of work has been accomplished for different tasks. Here, we have shown the outline for 10 weeks only you can add more weeks if it is needed. • Add serial numbers from 1 to 19 in SL No. ### Step-02: Adding Drop-down List and Giving Input Values for Weeks

In this step, we will insert drop-down lists for the Designation and Name columns. Furthermore, we will insert the percentages of the tasks completed from Week 1 to Week 10.

• First, select the cells of the Tasks column, and then go to the Data tab >> Data Validation. Afterward, the Data Validation dialog box will open up.

• Select the List option under Allow.
• Then, choose the list of Tasks from the Data sheet and press OK. Then, the following formula will appear in the Source box.
`=Data!\$B\$5:\$B\$16` Then, you will see the symbol for the drop-down list in the cells of the Tasks column. Similarly, complete the other cells of the Tasks column by choosing different tasks from the list. • Now, select the cells of the Designation column, and then go to the Data tab >> Data Validation. Afterward, the Data Validation dialog box will open up.

• Select the List option under Allow.
• Then, choose the list of Designations from the Data sheet and press OK. Then, the following formula will appear in the Source box.
`=Data!\$C\$5:\$C\$16` Later, you will see the symbol for the drop-down list in the cells of the Designation column.

• Click on the drop-down sign in cell D5 and choose any Designation from the list.

Here, we chose the Manager. In a similar manner, complete the other cells of the Designation column by choosing different designations from the list. Now, give input the percentages of different tasks completed in different weeks in the following manner. ### Step-03: Applying Formulas

Here, we will use some formulas to complete the following indicated columns. • Type the following formula in cell E5 to get the names corresponding to different designations.
`=INDEX(Data!\$D\$5:\$D\$16, MATCH(D5, Data!\$C\$5:\$C\$16,0))`

Formula Breakdown

• MATCH(D5, Data!\$C\$5:\$C\$16,0) → becomes
• MATCH(“Manager”, Data!\$C\$5:\$C\$16,0) → The MATCH function will return the row index number corresponding to the range \$C\$5:\$C\$16 where Manager
• Output → 3
• INDEX(Data!\$D\$5:\$D\$16, MATCH(D5, Data!\$C\$5:\$C\$16,0)) → becomes
• INDEX(Data!\$D\$5:\$D\$16, 3) → The INDEX function will return the name of the employee situated in Row 3 of the range \$D\$5:\$D\$16.
• Output → Ursula  Later, you will get the name of the employees corresponding to their designations. Now, we will extract the costs for different tasks from the sheet Data.

• Write down the following formula in cell H5 and press ENTER.
`=INDEX(Data!\$E\$5:\$E\$16, MATCH(C5, Data!\$B\$5:\$B\$16,0))`

Formula Breakdown

• MATCH(C5, Data!\$B\$5:\$B\$16,0)becomes
• MATCH(“Task 3”, Data!\$B\$5:\$B\$16,0) → The MATCH function will return the row index number corresponding to the range \$C\$5:\$C\$16 where Manager
• Output → 3
• INDEX(Data!\$E\$5:\$E\$16, MATCH(C5, Data!\$B\$5:\$B\$16,0)) → becomes
• INDEX(Data!\$E\$5:\$E\$16, 3) → The INDEX function will return the cost for the corresponding task situated in Row 3 of the range \$E\$5:\$E\$16.
• Output → \$67.00

• Drag down the Fill Handle tool. Finally, we will get the costs for different tasks. • Write down your planned number of weeks for different tasks in the Planned Weeks column. It’s time to calculate the estimated or calculated times for completing each task on the basis of percentages of different tasks in different week columns.

• Put down the following formula in cell G5 and press ENTER.
`=IF(SUMIF(\$L5:\$U5,">0")>0, SUMIF(\$L5:\$U5,">0"),"")`

Formula Breakdown

• SUMIF(\$L5:\$U5,”>0″) → The SUMIF function will sum up the percentages in the range \$L5:\$U5 if their values are greater than 0.
• Output → 1.55
• IF(SUMIF(\$L5:\$U5,”>0″)>0, SUMIF(\$L5:\$U5,”>0″),””) → becomes
• IF(1.55>0, 1.55,””) → The IF function will give 55 as output as the logical condition becomes TRUE, otherwise we will get a blank.
• Output → 1.55
• Drag down the Fill Handle tool. In this way, we will get the estimated weeks for different tasks. Now, we will calculate the costs for our planned duration and the estimated duration of different tasks.

• Type the following formula in cell I5 and press ENTER.
`=IF(H5<>"",\$F5*H5*5*8,"")`

Formula Breakdown

• \$F5*H5*5*8 → becomes
• 2*67*5*8 → here we will multiply total weeks with cost per hour, and to convert the total weeks into their corresponding hours we are multiplying them by 5 (for weekdays) and 8 (daily working hours).
• Output → 5360
• IF(H5<>””,\$F5*H5*5*8,””) → becomes
• IF(67<>””,5360,””) → we will get 5360 when the logical condition will be TRUE otherwise a blank.
• Output → 5360
• Drag down the Fill Handle tool. Finally, we will get the costs for each task according to our planned data. • Type the following formula in cell J5 and press ENTER.
`=IF(H5<>"",\$G5*H5*5*8,"")`

Formula Breakdown

• \$G5*H5*5*8becomes
• 55*67*5*8 → here we will multiply the total calculated weeks with cost per hour, and to convert the total weeks into their corresponding hours we are multiplying them by 5 (for weekdays) and 8 (daily working hours).
• Output → 4154
• IF(H5<>””,\$G5*H5*5*8,””) → becomes
• IF(67<>””,4154,””) → we will get 4154 when the logical condition will be TRUE otherwise a blank.
• Output → 4154
• Drag down the Fill Handle tool. Finally, we will get the calculated costs for each task. ### Step-04: Calculating Total Values

In this step, we will calculate the total weeks and total costs for all of the projects. • Type the following formula in cell G24.
`=SUM(G5:G23)`

Here, the SUM function will return the total number of weeks. • Press ENTER. Later, we will have the total number of weeks required to complete all of the tasks of this project.

• Enter the following formula in cell J24.
`=SUM(J5:J23)`

The SUM function will give us the total costs required for completing the tasks. ## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  