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 that 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.
You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Project Management Template in Excel (wallstreetmojo.com)
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 at 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 ChartCreate An Excel Gantt ChartGantt chart is a type of project manager chart that shows the start and completion time of a project, as well as the time it takes to complete each step. The representation in this chart is shown in bars on the horizontal axis.read more to track all the activities.
- Select Only Start time and insert a column bar chart. Go to the INSERT tab and insert the Stacked Bar Chart.
- Now, your chart must be looking like this.
- Now, right-click on the chart and choose Select Data.
- Now, in the below window, choose Add.
- In the below window, in the Series name, choose Minutes, and for series value, select data from C2: C14. Click on OK.
- In the below window, select the EDIT option on the right-hand side for our Horizontal Axis Labels.
- Now select horizontal values as our tasks. Click on OK.
- Your chart should be looking like this now.
- 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.
- Now, the time format will look like this in the chart.
- Select blue colored bars, right-click and select, format data series.
- Go to FILL and select NO FILL.
- Now coffee-colored bars and click on format data series and go to FILL and choose the different colors 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 arrives, 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 at the current date by applying the TODAY formula in ExcelTODAY Formula In ExcelToday function is a date and time function that is used to find out the current system date and time in excel. This function does not take any arguments and auto-updates anytime the worksheet is reopened. This function just reflects the current system date, not the time.read more.
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 excel chartsCreate Some Visual Excel ChartsIn Excel, a graph or chart lets us visualize information we've gathered from our data. It allows us to visualize data in easy-to-understand pictorial ways. The following components are required to create charts or graphs in Excel: 1 - Numerical Data, 2 - Data Headings, and 3 - Data in Proper Order.read more 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 chartColumn ChartColumn chart is used to represent data in vertical columns. The height of the column represents the value for the specific data series in a chart, the column chart represents the comparison in the form of column from left to right.read more.
Step 2: Right Click on the chart and choose Select Data.
Step 3: Now click on ADD.
Step 4: Now, in the below window, don’t select anything, just press the 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 a 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 that look like this now.
You just need to update your daily sales numbers in cell B3. Everything will change automatically for you.
If you observe the template now as on 14th Feb 2019 total of 33 days are completed, and the 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 of today, the 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 –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
58.6K subscribers