How to Track Multiple Projects in Excel (Download Free Template) (2024)

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents Expand

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.

How to Track Multiple Projects in Excel (Download Free Template) (1)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (2)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (3)

  • 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

How to Track Multiple Projects in Excel (Download Free Template) (4)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (5)

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+1

Then 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.

How to Track Multiple Projects in Excel (Download Free Template) (6)

  • After that, you will notice that all of the headers are now rotated anticlockwise.

How to Track Multiple Projects in Excel (Download Free Template) (7)

  • Then select the cell J3 and enter the following formula:

=IF(AND(J2>=$D$3,J2<=$E$3),"x","")

How to Track Multiple Projects in Excel (Download Free Template) (8)

  • Then drag the Fill Handle horizontally.
  • Doing this will mark “X” in which the task has been done.

How to Track Multiple Projects in Excel (Download Free Template) (9)

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.

How to Track Multiple Projects in Excel (Download Free Template) (10)

  • Next, we will use conditional formatting to create a Gantt chart from those marking.
  • To do this, first, click on Conditional Formatting from the Hometab.
  • Then click on New Rules.

How to Track Multiple Projects in Excel (Download Free Template) (11)

  • Next, in the new window, select Format only cells that contain options from the Select a Rule Typebox.
  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (12)

  • Next, in the new format window, go to the Fill tab and then click on the Fill Effects.

How to Track Multiple Projects in Excel (Download Free Template) (13)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (14)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (15)

  • After clicking OK, we will be back to the Edit Formatting Rule window, in that window. Click OK.

How to Track Multiple Projects in Excel (Download Free Template) (16)

  • Then we are again back to the Conditional Formatting Rule Managerwindow.
  • Click Apply after this.

How to Track Multiple Projects in Excel (Download Free Template) (17)

  • The Gantt chart will be ready and visible quite well.

How to Track Multiple Projects in Excel (Download Free Template) (18)

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).

How to Track Multiple Projects in Excel (Download Free Template) (19)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (20)

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.

How to Track Multiple Projects in Excel (Download Free Template) (21)

  • Then we select cell J2 and enter the following formula to link the cell $E$38 to this date.

=DATE(2020,2,E38)

How to Track Multiple Projects in Excel (Download Free Template) (22)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (23)

  • Then enter the formula in the cell F26:

=SUMIF(Table1[Project],E26,Table1[[Days Require ]])

And drag the Fill Handle to cell F28.

How to Track Multiple Projects in Excel (Download Free Template) (24)

  • Then enter the formula in the cell G26:

=AVERAGEIF(Table1[Project],'Project Performance'!E26,Table1[Progress])

And drag the Fill Handle to cell G28.

How to Track Multiple Projects in Excel (Download Free Template) (25)

  • Then enter the formula in the cell H26:

=1-G26

And drag the Fill Handle to cell G28.

How to Track Multiple Projects in Excel (Download Free Template) (26)

Then the table will somewhat look like this.

How to Track Multiple Projects in Excel (Download Free Template) (27)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (28)

  • When the chart appears, click on the chart area and right-click on the mouse.
  • From the context menu click on the Select Data.

How to Track Multiple Projects in Excel (Download Free Template) (29)

  • Then on the Select Data Sources window, click on Add.

How to Track Multiple Projects in Excel (Download Free Template) (30)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (31)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (32)

  • Now click on the Edit button to add the axis name on the chart.

How to Track Multiple Projects in Excel (Download Free Template) (33)

  • Then select the range of cells E$26:$E$28 in the box.

How to Track Multiple Projects in Excel (Download Free Template) (34)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (35)

  • Click OK after this.

Now you will see the chart in full form.

How to Track Multiple Projects in Excel (Download Free Template) (36)

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.

How to Track Multiple Projects in Excel (Download Free Template) (37)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (38)

  • Then we pick the data range for the doughnut chart.
  • To do this we right-click on the mouse and click on Select Data.

How to Track Multiple Projects in Excel (Download Free Template) (39)

  • Then in the new window, click on Add.

How to Track Multiple Projects in Excel (Download Free Template) (40)

  • Form the selection window, and select the range of cells $F$7:$F$8.

How to Track Multiple Projects in Excel (Download Free Template) (41)

  • After clicking OK, notice that the doughnut chart now showing with the data associated.
  • After some modification, it will look somewhat like this.

How to Track Multiple Projects in Excel (Download Free Template) (42)

  • To add on top of this, we will add a rectangular Text Box shape from the Insert tab.

How to Track Multiple Projects in Excel (Download Free Template) (43)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (44)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (45)

  • After adding the names, we enter the formula in cell D15:

=SUMIF('Gantt Chart'!C3:C32,D14,'Gantt Chart'!F3:F32)

How to Track Multiple Projects in Excel (Download Free Template) (46)

  • Then we enter the following formula in the cell D16:

=SUMIF('Gantt Chart'!C3:C32,'Project Overview'!D14,'Gantt Chart'!I3:I32)

How to Track Multiple Projects in Excel (Download Free Template) (47)

  • Enter the following formula in the cell D17:

=D15-D16

How to Track Multiple Projects in Excel (Download Free Template) (48)

  • Next select cell D18 and enter the following the formula:

=D16/D15

How to Track Multiple Projects in Excel (Download Free Template) (49)

  • Enter the following formula in cell D19:

=1-D18

How to Track Multiple Projects in Excel (Download Free Template) (50)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (51)

  • Then click on the Edit icon on the right side of the window.

How to Track Multiple Projects in Excel (Download Free Template) (52)

  • 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.

How to Track Multiple Projects in Excel (Download Free Template) (53)

  • Now our dashboard is ready to track multiple projects in Excel in one place.

How to Track Multiple Projects in Excel (Download Free Template) (54)

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.

Track Multiple Projects.xlsx

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.

Related Articles

  • How to Create Project Pipeline in Excel
  • How to Track Time Spent on Projects in Excel
How to Track Multiple Projects in Excel (Download Free Template) (2024)
Top Articles
Latest Posts
Recommended Articles
Article information

Author: Francesca Jacobs Ret

Last Updated:

Views: 6317

Rating: 4.8 / 5 (68 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Francesca Jacobs Ret

Birthday: 1996-12-09

Address: Apt. 141 1406 Mitch Summit, New Teganshire, UT 82655-0699

Phone: +2296092334654

Job: Technology Architect

Hobby: Snowboarding, Scouting, Foreign language learning, Dowsing, Baton twirling, Sculpting, Cabaret

Introduction: My name is Francesca Jacobs Ret, I am a innocent, super, beautiful, charming, lucky, gentle, clever person who loves writing and wants to share my knowledge and understanding with you.