On a regular day, A project manager has to manage multiple projects at once. Tracking those projects could be tedious if they are not organized properly. Having a summary view that is dynamic can also help a lot. Tor resolve the issue of how we can track multiple projects in Excel at once, we provided an Excel template with explanations where you can practice and modify the sheet as your will to track your multiple projects in Excel.
Why Do We Need Project Tracker?
The basic necessity of using a Project Tracker comes from inefficiency and the disproportion of resource distribution. Project tracker can help us achieve more with fewer resources. The main reasons are stated below.
- Overflow of Task
- Distribution of Resources
- No Fixed Priority List
Advantages of Project Tracker
Although managing a project tracker itself takes a bit of time, the output is quite worth the effort.
The advantages of the Multiple Projects tracker are quite a few:
- Gives an overview of the data without getting into too many details.
- Can repeat the process without any hassle.
- Resource utilization that is efficient.
- Capable of jumping from one task to the other.
Step 1: Collecting Data in Excel from Multiple Projects
The most crucial step before making this project tracking Excel template, we need to create a list of tasks and distribute them into separate projects. We also can identify them as project information.
- First, organize the data, which means dividing your project into smaller tasks, then scheduling them into starting dates and due dates.
- Also, assign a manager who is going to be responsible for the task.
- Mark their progress. And note it down on the sheet.
- Next, create a new worksheet and from that worksheet, link all of the data from the datasheet tab.
- Then we will add a column of days spent in order to calculate the number of days each task is completed.
- To do this, enter the following formula in cell G3.
=E3-F3- And then drag the Fill Handle icon to cell G32.
- Now the range of cells G3 to G32 is now filled with the differences between the Start Date and the Due Date of each task.
- Now the range of cells G3 to G32 is now filled with the differences between the Start Date and the Due Date of each task.
- Next, we will add how many days spent on each task to date, to do this. enter the following formula in cell L3:
=G3*F3
- Drag the Fill Handle to cell L32, then you will notice that the range of cell L3:L32 is now filled with days spent with each task.
Step 2: Preparing Gantt Chart
In order to get a clear overview of the project timeline user can create a Gantt chart of the tasks distributed in multiple projects. We are going to use the IF and DATE functions in this step.
- Now we will prepare a Gantt chart using this information.
- For this, we will use conditional formatting, and before this, we need to create a timeline for all of the tasks done.
- For this, we set the initial date for our timeline and then set the final date of the timeline. In this case, It is 3 February 2020 and the final due date is 27 April 2021.
- As we need to set a timeline for each day, we set the formula bellow:
=J2+1Then drag the Fill Handle horizontally till 27 April 2021.
- Next format the cells to show text vertically in order to view more rows in less space.
- After that, you will notice that all of the headers are now rotated anticlockwise.
- Then select the cell J3 and enter the following formula:
=IF(AND(J2>=$D$3,J2<=$E$3),"x","")
- Then drag the Fill Handle horizontally.
- Doing this will mark “X” in which the task has been done.
Repeat the same process for the other cells, for example. select the cell J4, and then the following formula.
=IF(AND(J2>=$D$4,J2<=$E$4),"x","")And drag the Fill Handle icon to the horizontal end.
- Repeating the formula for all rows will mark all of the task’s timeline.
- Next, we will use conditional formatting to create a Gantt chart from those marking.
- To do this, first, click on Conditional Formatting from the Home tab.
- Then click on New Rules.
- Next, in the new window, select Format only cells that contain options from the Select a Rule Type box.
- Therefore, in the Format only cells with the dropdown menu, select Specific Text in the first dropdown box and select Containing in the second dropdown box.
- In the third box, we need to specify our mark letter. We put x, as we want to mark the task timeline with x.
- Then click on Format.
- Next, in the new format window, go to the Fill tab and then click on the Fill Effects.
- Then in the Fill Effects window, select Two colors.
- Right after this, select your desired color, we choose Black and White as the Color 2 and Color 1.
- Then in the Shading Styles, choose Horizontal.
- Next, invariants, choose your preferred variants. We choose the middle stripe one.
- There will be a sample window showing how the formatting would look in the worksheet.
- Click OK after this.
- Then in the Font tab, choose your desired color. We choose Black to blend the letter x with the black background.
- Click OK after this.
- After clicking OK, we will be back to the Edit Formatting Rule window, in that window. Click OK.
- Then we are again back to the Conditional Formatting Rule Manager window.
- Click Apply after this.
- The Gantt chart will be ready and visible quite well.
We can add a scroll bar for better handling of this Gantt chart.
- To do this, click on the Developer tab in the worksheet.
- Then click on the Insert command, from the dropdown menu, and click on the Scroll bar (From Control).
- After this, a scroll button will appear on the worksheet.
- Resize the scroll button and then right-click on it.
- Then click on Format Control.
In the new Format Control window, in the Control tab, enter the location of the cell which you want to link., in this case, we choose $E$38.
- Then choose the minimum value as your wish, we choose 3 here.
- Then select the maximum value as your wish, we choose 400 here, as we are dealing with over 365 days here.
- Choose incremental change as 1, as we proceed day by day.
- Click OK after this.
- Then we select cell J2 and enter the following formula to link the cell $E$38 to this date.
=DATE(2020,2,E38)
- After entering the formula, the date remains the same. but now it is now connected to the cell $E$38.now as we scroll the scroll bar, the value of cell $E$38 will increase and the date will also increase in the subsequent cells.
- And this is how the scroll bar now working properly.
- Our Gantt chart is now complete.
Step 3: Creating Performance Matrix Charts
The next step is to create performance matrix charts on the basis of the multiple project dynamic tracking. Those charts will give us an overview that is dynamic and helps us to manage different projects in one template. To complete this step, we will take help from SUMIF and AVERAGEIF functions.
- Now we open a new worksheet named Project Performance.
- Then create a table just like the below image.
- Then enter the formula in the cell F26:
=SUMIF(Table1[Project],E26,Table1[[Days Require ]])And drag the Fill Handle to cell F28.
- Then enter the formula in the cell G26:
=AVERAGEIF(Table1[Project],'Project Performance'!E26,Table1[Progress])And drag the Fill Handle to cell G28.
- Then enter the formula in the cell H26:
=1-G26And drag the Fill Handle to cell G28.
Then the table will somewhat look like this.
- Basically what we did is we compare how many days each project would need to finish, and track how much progress they actually made. Then we also calculated how much project work is still remaining.
- Then we will create a bar chart showing the multiple projects’ progress comparisons.
- To do this go to the insert tab and click on the 100% Stacked Column.
- When the chart appears, click on the chart area and right-click on the mouse.
- From the context menu click on the Select Data.
- Then on the Select Data Sources window, click on Add.
- Then on the new window, select the range of cells in the series name $E$26:$E$28.
- And as series value select the range of cells $G$26:$G$28.
- Click OK after this.
- Then similarly to the previous step, click on the Add button again and select the following range of cells $E$26:$E$28.
- Next, select the range of cells $H$26:$H$28 in the Series values.
- Click OK after this.
- Now click on the Edit button to add the axis name on the chart.
- Then select the range of cells E$26:$E$28 in the box.
- Now you will see the project names are now presented on the right side of the Select Data Source.it will also be visible in the chart.
- Click OK after this.
Now you will see the chart in full form.
Step 4: Generating Dashboard to Track Multiple Projects
To make a summery style presentation, we will make some more charts on this step based on various performance criteria which will help us track the projects efficiently. The SUMIF function will be used here.
- Now in the new worksheet, we will add a new table as below.
- And then link the value of how many days we spent, how many days remaining in the total project etc on the range of cells H13 to H16.
- Then we insert a normal doughnut chart from the Insert tab, the chart will show default settings and pick some random value.
- Then we pick the data range for the doughnut chart.
- To do this we right-click on the mouse and click on Select Data.
- Then in the new window, click on Add.
- Form the selection window, and select the range of cells $F$7:$F$8.
- After clicking OK, notice that the doughnut chart now showing with the data associated.
- After some modification, it will look somewhat like this.
- To add on top of this, we will add a rectangular Text Box shape from the Insert tab.
- And place the box in idle of the doughnut and link the box to the cell $H$15, we know that the cell $H$15 shows the percentage of work completed on the project.
- So if for any reason our data changes, the work completion percentage will also change in both the doughnut chart and the text box.
- After that, we will add the project managers’ names and their performance matrix to the dashboard.
- For doing this, we will add a drop-down for evaluating each manager’s performance in the Gantt chart.
- After adding a drop-down menu from the Developer tab. We add the manager’s name in the dropdown menu.
- After adding the names, we enter the formula in cell D15:
=SUMIF('Gantt Chart'!C3:C32,D14,'Gantt Chart'!F3:F32)
- Then we enter the following formula in the cell D16:
=SUMIF('Gantt Chart'!C3:C32,'Project Overview'!D14,'Gantt Chart'!I3:I32)
- Enter the following formula in the cell D17:
=D15-D16
- Next select cell D18 and enter the following the formula:
=D16/D15
- Enter the following formula in cell D19:
=1-D18
- You can switch between managers in the drop-down menu and see their performance of them change dynamically in the table.
- Next er will use another doughnut chart in the worksheet, through the insert tab.
- We then link the values with the chart, by right-clicking the mouse and clicking on Select Data.
- Then on the new window click on Add.
- Then enter the range of the cell $D$16:$D$17 in the Series values.
- Click OK after this.
- Then click on the Edit icon on the right side of the window.
- Next enter the range of cells $B$16:$C$17 to specify the range names.
- Now as we change the manager’s name, the doughnut chart will also change accordingly.
- Now our dashboard is ready to track multiple projects in Excel in one place.
Hope this template will help you to track and manage multiple projects in Excel efficiently.
Read More: How to Track Project Progress in Excel
Download Template
Download this template below.
Conclusion
To sum it up, the question “How to Track Multiple Projects in Excel” is answered here by providing a sample Excel template. We used a Gantt chart for the tracking purposes and then added a string of charts in order to make this managing multiple projects much easier.
For this problem, a sample workbook is attached where you can practice multiple project tracking.
Feel free to ask any questions or feedback through the comment section.
