Template Google Sheets Theo Doi Du An IT va Phan Mem 2027: Agile Sprint Board Don Gian

Doi ngu IT nho thuong phai doi mat voi thach thuc lon: can quan ly du an theo Agile nhung chi phi cong cu nhu Jira, Linear hay Monday.com qua cao. Mot team 3-5 nguoi khong the bo ra 200-500 USD moi thang chi de theo doi task va bug. Google Sheets la giai phap thuc te, mien phi va du manh cho team IT co quy mo nho.

Bai viet nay huong dan xay dung Agile Sprint Board bang Google Sheets, bao gom backlog, sprint planning, task tracking, bug tracker, velocity chart va burndown chart don gian - tat ca trong mot file, toan doi co the truy cap tu bat ky dau.

Phan 1: Thach Thuc Cua Team IT Nho Khong Co Cong Cu Chuyen Dung

1.1 Chi Phi Cong Cu Qua Cao So Voi Quy Mo

Mot team IT 5 nguoi dung Jira Software ton khoang $42.5/thang (Premium), Linear ton $40/thang. Doi voi startup hay team IT noi bo cua doanh nghiep SME, day la khoan chi phi kho binh minh hoa. Trong khi do Google Sheets:

• Hoan toan mien phi voi Google Workspace ca nhan
• Da co san trong Google Workspace Business ma nhieu cong ty dang dung
• Khong can cai dat, chay tren trinh duyet
• Collaboration realtime cho ca team
• API de tich hop voi cac he thong khac

1.2 Tinh Phuc Tap Khi Dung Nhieu Cong Cu Roi Rac

Nhieu team IT nho dung Trello cho task, email cho bug report, Excel cho release tracking va chat app cho communication. Thong tin bi phan manh, kho tong hop bao cao, kho theo doi tien do tong the. Google Sheets tich hop tat ca vao mot noi.

1.3 Kho Dao Tao Thanh Vien Moi

Cong cu phuc tap nhu Jira co learning curve cao. Thanh vien moi can 1-2 tuan de quen. Voi Google Sheets, hau het moi nguoi da biet dung, co the bat dau ngay trong ngay dau tien.

Phan 2: Kien Truc Template Agile Sprint Board

2.1 Sheet 1: Product Backlog

Noi chua tat ca cac tinh nang, cai tien va bug chua duoc xu ly:

Cong thuc tao Story ID tu dong:

=IF(C2="","",
  "US-" & YEAR(L2) & "-" &
  TEXT(COUNTIFS($L$2:L2,"<>"&""), "000")
)

Cong thuc tong Story Points trong backlog:
=SUMIF(I:I, "Backlog", F:F)

Cong thuc loc backlog theo Priority (ARRAYFORMULA):
=ARRAYFORMULA(
  IF(G2:G="Critical", 1,
  IF(G2:G="High", 2,
  IF(G2:G="Medium", 3, 4)))
)

2.2 Sheet 2: Sprint Planning

Quan ly tung sprint: muc tieu, capacity, cac item duoc chon:

Thong tin Sprint:
| Truong | Gia tri |
|--------|---------|
| Sprint Name | Sprint 5 |
| Start Date | 2027-03-03 |
| End Date | 2027-03-14 |
| Sprint Goal | Hoan thien Reporting Module |
| Team Capacity | 80 story points (4 nguoi x 2 tuan x 10 diem/nguoi/tuan) |
| Committed Points | =SUMIF(Sprint!B:B, A2, Sprint!F:F) |
| Completed Points | =SUMIFS(Sprint!F:F, Sprint!B:B, A2, Sprint!M:M, "Done") |
| Velocity | =Completed Points / Total Points * 100 |

Tinh Capacity theo tung thanh vien:
| Thanh vien | So ngay lam viec | Ngay nghi | Hours/ngay | Total Hours | Story Points |
|-----------|-----------------|-----------|------------|-------------|-------------|
| Nguyen A  | 10              | 1         | 8          | 72          | 18 SP        |
| Tran B    | 10              | 0         | 8          | 80          | 20 SP        |

Cong thuc Total Capacity:
=SUMPRODUCT(
  (D$2:D$10 - E$2:E$10) * F$2:F$10
) / 4  -- chia 4 vi 4 hours = 1 SP

2.3 Sheet 3: Sprint Task Board (Kanban View)

Hinh thuc Kanban board trong Google Sheets, cho phep nhin tong quan trang thai task trong sprint hien tai:

Cau truc Task Board cho Sprint hien tai:

Cot A: Story ID
Cot B: Tieu de task  
Cot C: Nguoi thuc hien (Assignee)
Cot D: Story Points
Cot E: Status (To Do / In Progress / In Review / Done)
Cot F: Ngay bat dau thuc te
Cot G: Ngay hoan thanh thuc te
Cot H: Blocked? (Yes/No)
Cot I: Ly do bi block (neu co)
Cot J: Link code/PR

Conditional Formatting theo trang thai:
To Do: Mau xam nhat
In Progress: Mau xanh duong nhat
In Review: Mau vang nhat  
Done: Mau xanh la nhat
Blocked: Mau do dam - uu tien xu ly

Cong thuc tinh % hoan thanh sprint:
=COUNTIF(E:E, "Done") / COUNTA(E2:E) * 100

Cong thuc tinh Story Points da hoan thanh:
=SUMIF(E:E, "Done", D:D)

Dem task bi block:
=COUNTIF(H:H, "Yes")

2.4 Sheet 4: Bug Tracker

Theo doi tat ca bug tu khi phat hien den khi fix xong:

Cong thuc phan tich bug theo severity:

Diem uu tien xu ly bug (Bug Severity Matrix):
=IFS(
  AND(D2="Critical", F2="Production"), 1,
  AND(D2="Major", F2="Production"), 2,
  AND(D2="Critical", F2="Staging"), 3,
  AND(D2="Major", F2="Staging"), 4,
  TRUE, 5
)

Thoi gian xu ly bug trung binh theo severity:
=AVERAGEIF($D$2:$D$100, "Critical",
  $L$2:$L$100 - $I$2:$I$100
)

Bug count theo thang (de phat hien xu huong):
=COUNTIFS(
  $I:$I, ">="&DATE(YEAR(NOW()),MONTH(NOW()),1),
  $I:$I, "<"&DATE(YEAR(NOW()),MONTH(NOW())+1,1)
)

Ty le bug duoc dong trong SLA (Crit: 24h, Major: 72h):
=COUNTIFS(
  D:D, "Critical",
  L:L-I:I, "<=1"
) / COUNTIF(D:D, "Critical") * 100

2.5 Sheet 5: Velocity Tracking

Theo doi velocity qua cac sprint de du bao kha nang delivering:

Cau truc Velocity Tracker:
| Sprint | Start | End | Committed SP | Completed SP | Velocity | Ghi chu |
|--------|-------|-----|-------------|-------------|---------|---------|
| Sprint 1 | 2027-01-06 | 2027-01-17 | 40 | 35 | 87.5% | |
| Sprint 2 | 2027-01-20 | 2027-01-31 | 45 | 42 | 93.3% | |
| Sprint 3 | 2027-02-03 | 2027-02-14 | 45 | 45 | 100% | |
| Sprint 4 | 2027-02-17 | 2027-02-28 | 50 | 38 | 76% | Tet nghi nhieu |
| Sprint 5 | 2027-03-03 | 2027-03-14 | 45 | ? | ? | Dang chay |

Average Velocity (3 sprint gan nhat):
=AVERAGE(OFFSET(E2, COUNTA(E:E)-4, 0, 3, 1))

Du bao so sprint can de hoan thanh backlog:
=CEILING(
  SUMIF('Product Backlog'!I:I, "Backlog", 'Product Backlog'!F:F)
  / F_Average_Velocity,
  1
)

Velocity trend (tang hay giam):
=IF(E_Latest > E_Previous, "Tang", IF(E_Latest < E_Previous, "Giam", "On Dinhk"))

2.6 Sheet 6: Burndown Chart Data

Du lieu cho bieu do burndown don gian:

Burndown Chart Data - Sprint 5:
| Ngay | Du kien con lai (Ideal) | Thuc te con lai (Actual) |
|------|------------------------|------------------------|
| 03/03 | 45 | 45 |
| 04/03 | 40.9 | 43 |
| 05/03 | 36.8 | 38 |
| 06/03 | 32.7 | 36 |
| 07/03 | 28.6 | 30 |
| 10/03 | 24.5 | 28 |
| 11/03 | 20.5 | 22 |
| 12/03 | 16.4 | 18 |
| 13/03 | 12.3 | ? |
| 14/03 | 0 | ? |

Cong thuc Ideal Burndown (giam tuyen tinh theo ngay lam viec):
=C$2 - (C$2 / NETWORKDAYS(B$2, B$12)) * (NETWORKDAYS(B$2, A3))

Cong thuc Actual Remaining (tinh tu Sprint Board):
=C$2 - SUMPRODUCT(
  ('Sprint Board'!E$2:E$100="Done")*
  ('Sprint Board'!G$2:G$100<=A3)*
  'Sprint Board'!D$2:D$100
)

Tao Sparkline Burndown don gian:
=SPARKLINE(
  B3:C12,
  {"charttype","line";
   "linewidth",2;
   "color1","#4CAF50";
   "color2","#f44336"}
)

Phan 3: Huong Dan Su Dung Agile Voi Google Sheets

3.1 Sprint Planning Meeting (2 tuan/1 lan)

Qui trinh Sprint Planning:

BUOC 1: Review Product Backlog (15 phut)
- Product Owner trinh bay top backlog items
- Team hoi ve Acceptance Criteria
- Dam bao moi nguoi hieu ro yeu cau

BUOC 2: Tinh Sprint Capacity (5 phut)
- Moi thanh vien bao ngay nghi/off trong sprint
- Tinh tong capacity theo cong thuc trong sheet

BUOC 3: Chon Items Cho Sprint (30-45 phut)
- Bat dau tu items Priority cao nhat
- Vote Story Points theo Fibonacci (dung Google Forms de vote kin)
- Neu chenh lech >2 muc: thao luan va vote lai
- Dung khi Committed SP = 90% Capacity

BUOC 4: Xac Dinh Sprint Goal (10 phut)
- Mot cau mo ta ket qua chinh cua sprint
- Nen co the do luong duoc
- Lien quan den gia tri business

GOOGLE FORMS STORY POINT VOTING:
Tao form voi cac cau hoi:
- Story nao dang vote?
- Story Points cua ban: 1/2/3/5/8/13/21
- Chia se: Responses -> Sheet (Sheet Backlog)

3.2 Daily Standup (15 phut moi sang)

Moi thanh vien tra loi 3 cau hoi (update truc tiep vao Sprint Board):
1. Hom qua lam gi? (update Status sang Done neu xong)
2. Hom nay lam gi? (update assignee/status cac task moi)  
3. Co gi can ho tro khong? (danh dau Blocked neu co)

Sprint Board Google Sheets - UPDATE HANG NGAY:
- Keo task tu "To Do" sang "In Progress" khi bat dau
- Keo sang "In Review" khi goi PR
- Keo sang "Done" khi merge va deploy
- Danh dau "Blocked: Yes" + ly do khi bi chan

Tao Slack notification khi co thay doi (Apps Script):
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  
  if (sheet.getName() === 'Sprint Board' && range.getColumn() === 5) {
    const taskName = sheet.getRange(range.getRow(), 2).getValue();
    const newStatus = range.getValue();
    const assignee = sheet.getRange(range.getRow(), 3).getValue();
    
    // Gui webhook toi Slack
    const webhookUrl = 'YOUR_SLACK_WEBHOOK_URL';
    const payload = {
      text: assignee + ' da chuyen task "' + taskName + '" sang ' + newStatus
    };
    
    UrlFetchApp.fetch(webhookUrl, {
      method: 'post',
      payload: JSON.stringify(payload)
    });
  }
}

3.3 Sprint Review va Retrospective (cuoi sprint)

Sprint Review Checklist:
[ ] Update Velocity Tracker voi ket qua sprint
[ ] Review Burndown chart - sprint co smooth khong?
[ ] Demo cac tinh nang hoan thanh cho stakeholder
[ ] Cap nhat trang thai backlog items: Backlog -> Done
[ ] Review Bug Tracker: bug moi phat sinh trong sprint

Retrospective - 3 cot Google Sheets:
| Lam Tot (Keep) | Can Cai Thien (Improve) | Thu Nghiem (Try) |
|----------------|------------------------|-----------------|
| Code review nhanh | Mo ta task chua ro rang | Pair programming |
| Daily standup dung gio | Bug report thieu info | Mob testing |

Metrics Retrospective tu dong:
- Velocity: Sprint nay so voi trung binh 3 sprint truoc?
- Bug rate: So bug phat sinh trong sprint / Story Points
- Cycle time: Thoi gian trung binh tu "In Progress" den "Done"
- Blocked rate: So task bi block / Tong task

Cong thuc Cycle Time trung binh:
=AVERAGEIFS(
  G2:G100 - F2:F100,
  E2:E100, "Done",
  F2:F100, "<>"&""
)

Phan 4: Release Tracker va Version Management

Sheet "Release Tracker":
| Version | Release Date | Status | Stories Included | Bug Fixes | Release Notes Link |
|---------|-------------|--------|-----------------|-----------|-------------------|
| v2.5.0  | 2027-03-14  | Planned | US-001, US-002  | BUG-011   | [Link]            |
| v2.4.1  | 2027-02-28  | Released | -              | BUG-008, BUG-009 | [Link]    |
| v2.4.0  | 2027-02-14  | Released | US-011-US-015  | BUG-005   | [Link]            |

Go-Live Checklist tu dong (Conditional Formatting):
[ ] Unit tests passed
[ ] Integration tests passed  
[ ] Staging deployment successful
[ ] Performance test: response time < 500ms
[ ] Security scan: 0 critical issues
[ ] Rollback plan ready
[ ] Monitoring alerts configured

Tong so bug theo phien ban (lich su chat luong):
=COUNTIF('Bug Tracker'!G:G, A2)

Bug fix rate trong version:
=COUNTIFS(
  'Bug Tracker'!G:G, A2,
  'Bug Tracker'!K:K, "Closed"
) / COUNTIF('Bug Tracker'!G:G, A2)

Phan 5: Khi Nao Nen Upgrade Len Jira

5.1 Chi Phi So Sanh: Google Sheets vs Cong Cu Chuyen Dung

Team 5 nguoi, 2 nam su dung:
- Google Sheets (da co GWS): 0 dong
- Jira Software (Standard): 3.79 USD x 5 x 24 = 454.8 USD
- Linear: 8 USD x 5 x 24 = 960 USD
- Monday.com (Basic): 9 USD x 5 x 24 = 1,080 USD

Tiet kiem khi dung Google Sheets: 454-1080 USD trong 2 nam
(Tuong duong 11-27 trieu VND)

Phan 6: Meo Nang Cao Hieu Qua Su Dung

6.1 Dung Data Validation Cho Truong Trang Thai

Cai dat dropdown cho Status trong Sprint Board:
Data > Data Validation > Dropdown from list:
To Do, In Progress, In Review, Done, Blocked

Cai dat dropdown cho Priority:
Critical, High, Medium, Low

Cai dat dropdown cho Story Points (Fibonacci):
1, 2, 3, 5, 8, 13, 21

Cai dat dropdown cho Severity (Bug Tracker):
Critical, Major, Minor, Trivial

6.2 Su Dung Named Ranges

Dinh nghia Named Ranges de cong thuc de doc hon:
- "CurrentSprint": 'Sprint Planning'!A1 (ten sprint hien tai)
- "BacklogItems": 'Product Backlog'!A2:L500
- "SprintItems": 'Sprint Board'!A2:J200
- "BugList": 'Bug Tracker'!A2:N500

Cach dung:
=COUNTIFS(
  INDEX(BacklogItems,0,9), "In Sprint",
  INDEX(BacklogItems,0,7), "High"
)

6.3 Dashboard Tong Hop Tren Sheet "Overview"

Dashboard nhin mot nhin biet moi thu:

Sprint hien tai:
- Ten sprint: =INDIRECT("'Sprint Planning'!A2")
- So ngay con lai: =NETWORKDAYS(TODAY(), 'Sprint Planning'!C2)
- % hoan thanh: =COUNTIF('Sprint Board'!E:E,"Done")/COUNTA('Sprint Board'!E2:E)*100
- Story Points con lai: =SUMIF('Sprint Board'!E:E,"<>Done",'Sprint Board'!D:D)

Bug metrics:
- Open bugs: =COUNTIF('Bug Tracker'!K:K,"New")+COUNTIF('Bug Tracker'!K:K,"In Progress")
- Critical bugs: =COUNTIFS('Bug Tracker'!D:D,"Critical",'Bug Tracker'!K:K,"<>Closed")
- Bug fix rate thang nay: =COUNTIFS(
    'Bug Tracker'!L:L, ">="&DATE(YEAR(NOW()),MONTH(NOW()),1),
    'Bug Tracker'!K:K, "Closed"
  ) / COUNTIF('Bug Tracker'!L:L, ">="&DATE(YEAR(NOW()),MONTH(NOW()),1))

Team workload:
- Task per person: =COUNTIF('Sprint Board'!C:C, B2)
- SP per person: =SUMIF('Sprint Board'!C:C, B2, 'Sprint Board'!D:D)

Phan 7: FAQ - Cau Hoi Thuong Gap

Q: Template co ho tro Scrum va Kanban khong?

Template chinh duoc thiet ke cho Scrum (sprint-based). De dung Kanban, ban co the bo cac sheet Sprint Planning va Velocity, chi giu Backlog va Sprint Board (doi ten thanh "Kanban Board"). Thay vi sprint, task chay tu Backlog -> To Do -> In Progress -> Review -> Done theo flow lien tuc.

Q: Lam sao de nhieu project dung chung mot file khong bi lon?

Co hai cach: (1) Them cot "Project" vao moi sheet va loc bang FILTER/QUERY. (2) Tao file rieng cho moi project nhung dung IMPORTRANGE de tong hop dashboard tren file master. Cach 2 duoc khuyen nghi hon cho tren 3 projects.

Q: Lam sao tracking bug tu khach hang bao cao (customer bug)?

Tao Google Form "Bao Cao Loi" cho khach hang hoac internal team. Responses tu dong vao Bug Tracker. Them truong "Nguon" (Customer/QA/Dev/Monitor) de phan loai nguon goc bug. Tich hop Zapier hoac Apps Script de tu dong cap nhat trang thai bug vao email cho nguoi bao cao.

Q: Template co theo doi technical debt khong?

Them loai "Tech Debt" vao Product Backlog (ben canh User Story, Bug, Task). Them tag "Tech Debt" va theo doi ty le SP cho tech debt / tong SP moi sprint. Best practice: danh 20% sprint capacity cho tech debt de tien xu ly dan.

Ket Luan

Google Sheets la cong cu Agile project management mien phi nhung day du suc manh cho team IT nho. Voi template nay, ban co the:

• Quan ly Product Backlog ro rang, co priority
• Chay Agile Sprint tu dong tu planning den retrospective
• Theo doi bug tu phat hien den dong vu severity matrix
• Nhin burndown chart va velocity de du bao chinh xac
• Tiet kiem toi thieu 454 USD moi 2 nam so voi Jira

Tai template mien phi tai SheetStore va bat dau sprint dau tien ngay hom nay!