Software Project Management System in Excel: Task Tracker, Sprint Burndown, Risk Register and Project Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: software-project-management-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Tasks, Burndown, Risks, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Billing app for a Nashik distributor, as of 17 October 2026 (made-up project).

For time and billing of a single freelancer see the freelance timesheet; the industry view of utilisation is in 27.19 IT services utilisation and billing.

2. Build it sheet by sheet

Step 1. Setup: as-of date, team and lists

1. On Setup, type the as-of date in B3 (17-10-2026; use =TODAY() to make it live) and the hours budget in B5 (520).
2. In A8:C12 list team members, roles and cost per hour. In E8:E11 the statuses, in F8:F10 the priorities, and in H8:J10 the sprints with start and end dates.

Step 2. Tasks: the log and its formulas

1. On Tasks, row 4 headers: ID, Task, Module, Owner, Priority, Sprint, Start, Due, Estimate, Actual, Status. Add drop-downs for Owner, Priority, Sprint and Status from Setup (drop-down lists).
2. Remaining L5: =IF(K5="Done",0,MAX(0,I5-J5)). Variance M5: =J5-I5 (positive means over estimate).
3. Overdue N5: =IF(AND(K5<>"Done",H5<Setup!$B$3),"Overdue","") (AND). Days left O5: =IF(K5="Done","",H5-Setup!$B$3).
4. Cost P5: =J5*XLOOKUP(D5,Setup!$A$8:$A$12,Setup!$C$8:$C$12): actual hours × the owner's rate. Highlight the row red when N is Overdue (formula rules).

Step 3. Burndown for the current sprint

1. On Burndown, type the sprint in B4 (S2). Hours planned B5: =SUMIFS(Tasks!$I$5:$I$60,Tasks!$F$5:$F$60,B4): 178.
2. First date A8: =XLOOKUP(B4,Setup!$H$8:$H$10,Setup!$I$8:$I$10) and A9 =A8+1 down to A21.
3. Ideal remaining B8: =ROUND($B$5*(1-(A8-$A$8)/($A$21-$A$8)),1). Type actual remaining in column C each evening.
4. Insert a line chart of B and C against A (line charts). When the actual line sits above the ideal line, the sprint is behind.

Step 4. Risk register

1. On Risks, type risk, probability (1-5), impact (1-5), owner and action. Score D5: =B5*C5.
2. Rating E5: =IF(D5>=15,"High",IF(D5>=8,"Medium","Low")); colour High red. Client changes the invoice format late scores 16: High.

Step 5. Report and Dashboard

1. On Report, per owner: tasks =COUNTIF(Tasks!$D$5:$D$60,A5), done =COUNTIFS(Tasks!$D$5:$D$60,A5,Tasks!$K$5:$K$60,"Done"), estimate and actual with SUMIFS, overdue with COUNTIFS and cost =SUMIFS(Tasks!$P$5:$P$60,Tasks!$D$5:$D$60,A5) (COUNTIFS).
2. Status block from row 13: tasks and hours per status.
3. On Dashboard: work done B5 =SUMIFS(Tasks!$I$5:$I$60,Tasks!$K$5:$K$60,"Done")/SUM(Tasks!$I$5:$I$60), overdue C5, budget used D5 =SUM(Tasks!$J$5:$J$60)/Setup!B5, cost E5, open high-priority F5 and high risks G5.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Counting progress by number of tasks. Six of 16 tasks done sounds like 38%, but tasks differ in size. Weight by estimated hours: 154 ÷ 380 = 40.5%.
2. Using TODAY() while preparing a report. Keep a fixed as-of date when you send a report, so numbers do not change when the client opens it a day later.
3. Overwriting the estimate when a task runs over. Keep the original estimate and let Variance show the overrun; that is how estimates improve next time.
4. A burndown with no actual line. The ideal line alone says nothing. Type remaining hours every evening, even if they did not move.
5. Risks without owners. A risk with no owner and no action is just a worry. The register asks for both.

5. More real examples

Example 1: Website project for a Kolhapur shop. Same sheets, modules such as Home page, Catalogue, Checkout; one sprint a week.

Example 2: Fixed-price contract. Add a contract value in Setup and a KPI margin so far = contract value × work done − cost to date.

Example 3: Support desk. Tasks become tickets with an Opened date; days open = as-of date − opened, and Overdue compares with the agreed response days.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

Can Excel be used for project management?

Yes, for small teams. A task log with owners, dates and hours, plus formulas for overdue flags, cost and a burndown, covers what most small projects need.

How do I make a burndown chart in Excel?

List the sprint dates, compute the ideal remaining hours that fall evenly to zero, type the actual remaining each day and plot both as a line chart.

How do I flag overdue tasks?

Use =IF(AND(status<>"Done",due<as-of date),"Overdue","") and a conditional formatting rule on that column.

How is project cost calculated?

Actual hours × each person's cost per hour, looked up from Setup with XLOOKUP, then totalled with SUMIFS by owner.

What is a risk score?

Probability × impact on 1-5 scales. 15 and above is High, 8 to 14 Medium, below 8 Low in this template.

7. Quick quiz

Ten questions on this page. Sign in to save your score.