Project Management Template in Excel (Table of Contents)
Excel Project Management Template
Project management is one of the important roles in any organization. You cannot simply track all the activities in the project just by hand you need some software to help you in managing the project better.
MS Excel is one of that software which can help you in tracking the status of the project with easy templates in place. In this article, we exclusively show you to use excel to prepare project management templates in excel.
How to Use Excel to Prepare Project Management Templates in Excel?
Below are the examples to prepare project management templates in excel.
Example #1 – Excel Project Management Template by Using Gantt Chart
Gantt Chart is one of those charts in excel which is very helpful in reviewing the tasks of the project, current status against set standards.
When the project is up and running after a week as a project manager want to know the status of the project whether it is in line or not. From the start of the project to the end of the project each stage you can track the project stages.
Assume you are an event management company and you had taken a very important project which is very lucrative for you. But the only thing is you need to execute the tasks based on the agenda given by the organizers. Consider below is the agenda given by the organizers.
Now let us create an Excel Gantt Chart to track all the activities.
Step 1: Select Only Start time and insert a column bar chart. Go to INSERT tab and insert Stacked Bar Chart.
Step 2: Now your chart must be looking like this.
Step 3: Now right click on the chart and choose Select Data.
Step 4: Now in the below window choose Add.
Step 5: In the below window, in the Series name choose Minutes and for series value, select data from C2:C14. Click on OK.
Step 6: In the below window select EDIT option on the right-hand side for our Horizontal Axis Labels.
Step 7: Now select horizontal values as our tasks. Click on OK.
Step 8: Your chart should be looking like this now.
Step 9: We need to make some formatting to make it look better. Select the horizon series values i.e. time value. Right-click and choose to format Axis.
In the Axis, formatting applies below values.
Step 10: Now time format will look like this in the chart.
Step 11: Select blue colored bars, right click and select, format data series.
Step 12: Go to FILL and select NO FILL.
Step 13: Now coffee colored bars and click on format data series and go to FILL and choose the different color as per your wish.
And your chart is ready to take over the task and you can manage the project better now.
Example #2 – Sales Cycle Tracker Example
Now we will see how to create a sales cycle tracker with excel. Consider the below data for this example. Data includes Sales Starting Date, Ending Date, Target, No., of Days Completed, Current Achievement and Variance and many other things.
Step 1: First mention Target & Current Sales. Arrive variance value i.e. Target vs Current.
Step 2: Now arrive Project Completion %.
Step 3: Now mention Sales Starting Date & Ending Date.
Step 4: After this arrive net workdays between a start date and end date.
Step 5: Now arrive Target Per Day by dividing the Target with No., of Days Available.
Step 6: Now arrive current date by applying TODAY formula.
Step 7: Now calculate no., of day’s completed from start date to the current date.
Step 8: Arrive as per daily target what should be the current revenue.
Step 9: Now calculate Avg Per Day Achieved so far.
Step 10: Calculate if the average trend sales continue for the rest of the project, what would be the end revenue.
Step 11: Now arrive achieved percentage if the same trend continues.
Now we have completed our calculation part. Using this let’s create some visual charts which can help us track the project by just looking at the chart.
Step 1: Let’s create a simple column chart first. Select the cell B6 and create a column chart.
Step 2: Right Click on the chart and choose Select Data.
Step 3: Now click on ADD.
Step 4: Now in the below widow don’t select anything, just press OK.
Step 5: We have a chart like this now.
Step 6: Now select the bar and click Format Data Series. On the right-hand side, you will see the Format Data Series dialogue box.
Step 7: Make the series overlap to 100%.
Step 8: Now both the columns are overlapped and the chart looks like this.
Step 9: Now change the color of the red bar to NO FILL.
Step 10: It is looking like this.
Step 11: Draw round shape just below the chart and adjust it.
Step 12: Add chart title.
We have one chart ready. Similarly, create the second chart as well for Achieved % at the same trend.
We have two charts looks like this now.
You just need to update your daily sales numbers in the cell B3. Everything will change automatically for you.
If you observe the template now as on 14th Feb 2019 totally 33 days are completed and current should have been 1.51 lakh USD but the actual revenue now is 1.1 lakh i.e. 22% of the overall revenue.
Target per day, in the beginning, was 4587 USD (B11 cell) per day but as on today, achievement is 2894.74 USD per day (B16 cell).
If the same trend of 2894.74 Per day continues for the rest of the period of the project it will yield you 2.53 lakh USD which is 50.67% of the targeted revenue at the start.
Using this Project management template you can track your daily sales Target vs Actual revenue.
Things to Remember About Project Management Template in Excel
- Charts will automatically charge if you change the manual entry columns.
- You can create a Gantt Chart for activities where tasks are followed by one after the other.
- In the case of different project management templates, you need to identify the tasks and line of activities involved in the project.
You can download this Project Management Excel template here – Project Management Excel Template
Recommended Articles
This has been a guide to Project Management Templates in Excel. Here we discuss how to create a Project Management Excel Template along with practical examples and a downloadable template. You may learn more about excel from the following articles –
- Excel Grouped Bar Chart
- Stacked Bar Chart in Excel
- ROUND Formula in Excel
- Inventory Template Excel
- Gauge Excel Chart
- Pareto Excel Chart
- Create Project Timeline in Excel
- Doughnut Excel Chart
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
