Excel remains one of the most accessible tools for sprint planning, especially for teams just adopting agile practices or working with limited budgets. With the right structure and formulas, you can create a functional sprint planning system that tracks stories, estimates, team capacity, and deliverables. This guide walks you through building and executing sprint planning entirely in Excel.
Why Excel
Excel works for sprint planning because it offers flexibility without forcing you into rigid workflows. Unlike specialized agile tools, you control the exact columns, calculations, and views you need. Teams already familiar with spreadsheets can adopt this approach immediately without training overhead or subscription costs.
Excel also excels at ad-hoc calculations and custom reporting. If you need to analyze velocity trends, adjust capacity for holidays, or create a burndown chart specific to your process, you can build these directly into your sheet. The tool plays well with other systems too - you can import data from Jira via CSV exports, or export your sprint plan to share with stakeholders in a format everyone understands.
Step-by-Step Guide
1. Create Your Workbook Structure
Start by opening a new Excel workbook and setting up multiple worksheets for different sprint planning functions. Create sheets labeled: "Backlog," "Sprint Plan," "Team Capacity," "Burndown," and "Archive." This separation keeps your data organized and makes it easier to reference information across different planning phases.
In the "Team Capacity" sheet, list your team members in column A and their available hours per sprint in column B. For a typical 2-week sprint with standard 40-hour work weeks, each person has 80 hours available. However, subtract time for meetings, reviews, and administrative tasks. Most teams use 60-70 hours per team member per sprint. Add a "Days Off" column (C) to track vacation time and adjust capacity accordingly.
Create a summary calculation at the bottom: in cell B12, enter a formula like =SUM(B2:B11) to show total team capacity for the sprint. This becomes your constraint when selecting stories. If your formula shows 240 total hours available and your team is in a sprint with a holiday, adjust individual rows in column B downward to reflect that.
2. Set Up Your Backlog Sheet
In your "Backlog" sheet, create columns for tracking all potential work. Column A is "Story ID" (use your existing naming convention from Jira if applicable). Column B is "Title." Column C is "Description" (brief, one or two sentences). Column D is "Story Points" (use Fibonacci sequence: 1, 2, 3, 5, 8, 13). Column E is "Business Priority" (scale of 1-5, with 1 being highest). Column F is "Dependencies" (list other story IDs this depends on). Column G is "Status" (values like "Not Started," "In Progress," "Done," or "Backlog"). Column H is "Notes."
Start entering your product backlog items into rows starting at row 2 (row 1 contains headers). Focus on having realistic, well-estimated stories. If you're working from an existing backlog in another system, copy the titles and estimates here and manually recreate the structure. Include enough backlog items for the next 3-4 sprints so you have choices during planning.
Add data validation to column D (Story Points) to restrict entries to valid values. Go to the Data menu, select Validation, choose List, and enter "1,2,3,5,8,13" in the Source field. This prevents estimation errors. Do the same for column E (Business Priority) with values "1,2,3,4,5" and column G (Status) with your defined status values.
3. Build Your Sprint Plan Sheet
Create a new sheet called "Sprint Plan" and set up headers in row 1: "Story ID," "Title," "Assigned To," "Story Points," "Actual Hours," "Status," "Sprint," and "Completion Date." This sheet is where you pull stories from your backlog into the actual sprint you're planning.
In column H (Sprint), you'll reference the current sprint name in a way that makes filtering easy. Enter something like "Sprint 24 (Jan 15-28)" for each story you commit to this sprint. This naming convention helps when you archive past sprints.
Starting in row 2, manually select stories from your Backlog sheet that you want to include in this sprint. Type the Story ID, then use a VLOOKUP formula to pull the Title from your Backlog sheet. In cell B2, enter: =VLOOKUP(A2,Backlog!A:B,2,FALSE). Copy this formula down for all stories in your sprint. This links your sprint plan to your backlog, so if you update a title in the backlog, it automatically updates here.
In column D (Story Points), use the same VLOOKUP approach: =VLOOKUP(A2,Backlog!A:D,4,FALSE). In column E (Actual Hours), leave it blank initially - this is where team members will log hours as work progresses.
4. Calculate Capacity Allocation and Velocity
Create a summary section at the top of your Sprint Plan sheet (rows above your story table) that shows your sprint metrics. In a dedicated area, calculate total committed story points. In cell C2 (outside your table), enter: =SUM(D:D) to show total points for the sprint. Label this cell "Total Sprint Points."
Below that, add a "Committed Capacity" calculation. In cell C3, reference your Team Capacity sheet: ='Team Capacity'!B12. This pulls your total available hours into your sprint plan view.
Create an "Average Points Per Hour" calculation to show efficiency. In cell C4, enter: =C2/C3 (total points divided by total hours). This gives you a metric for how many story points your team completes per hour of work. Track this across multiple sprints to establish your actual velocity, which improves future sprint planning.
Add a "Utilization Rate" cell to show what percentage of your team's capacity is committed. In cell C5, enter: =SUM(E2:E100)/C3. This formula divides actual hours logged against your available capacity. If this exceeds 100%, you're overcommitted.
5. Create a Burndown Chart
Navigate to your "Burndown" sheet. Set up two columns: "Day" (column A) and "Remaining Points" (column B). List each day of your sprint in column A, starting with Day 1 through your sprint length (typically 10 business days for a 2-week sprint).
Manually log remaining story points at the end of each day. Alternatively, create a formula that references your Sprint Plan sheet and calculates remaining points based on stories marked "Done" in column G. In cell B2, enter: =C2-COUNTIF('Sprint Plan'!G:G,"Done")*AVERAGE('Sprint Plan'!D:D). Adjust this formula to match your actual data structure, but the concept is to subtract completed story points from your total commitment each day.
Create a line chart from your burndown data. Select cells A1:B11 (Day and Remaining Points columns), go to Insert menu, select Chart, choose Line Chart, and click OK. This visualization shows whether you're on track to complete your sprint commitment. The line should slope downward toward zero by sprint end.
For comparison, add an "Ideal Burndown" column (column C) that shows a straight line from your total committed points down to zero across your sprint days. In cell C2, enter: =C2*(1-(ROW()-2)/10) (assuming a 10-day sprint). This helps your team see if actual progress matches expectations.
6. Add Status Tracking and Updates
In your Sprint Plan sheet, use column G (Status) to track progress daily. Team members should update this column as they work. Valid values are "Not Started," "In Progress," "Done," and "Blocked."
Create a conditional formatting rule to visually highlight story status. Select column G, go to Home menu, click Conditional Formatting, choose Highlight Cell Rules, select "Equal To," and set up a rule for "Blocked" with red highlighting. Do the same for "Done" with green highlighting. This makes it immediately obvious which stories are at risk.
In column F (Completion Date), ask team members to enter the actual date when they mark a story "Done." Use the formula =TODAY() in a helper column and copy the value when marking complete. This creates an actual completion timestamp.
At the bottom of your Sprint Plan sheet, add a summary line showing: Stories Not Started, Stories In Progress, Stories Done, and Stories Blocked. Use COUNTIF formulas: =COUNTIF(G2:G100,"Not Started") for each status. This dashboard view shows sprint health at a glance.
7. Link to Your Tool Capacity Calculator
While working in Excel, reference external capacity planning tools to validate your estimates. Before committing to a sprint, run your team composition and capacity assumptions through a dedicated calculator to stress-test your plan. Export your Team Capacity sheet to CSV, import it into a tool that accounts for different role types and specializations, and compare results.
This cross-check helps catch overallocation issues early. For example, if you have one senior developer who's the only person who can work on backend infrastructure, your simplified Excel capacity calculation might miss that bottleneck. A capacity calculator surfaces these constraints.
8. Archive and Report on Completed Sprints
After each sprint ends, move completed stories to your Archive sheet. Create an "Archive" workbook or sheet where you keep historical sprint data for velocity tracking and pattern analysis.
In your original workbook, right-click on the Sheet tab and duplicate it, then rename the copy to "Sprint 24 Archive." Clear out the story-level detail but keep the summary metrics (total points, velocity, burndown slope). This preserves your planning reference without cluttering your active planning sheets.
Create a separate "Velocity Report" sheet that tracks points completed per sprint over the last 4-6 sprints. Column A is "Sprint Name," column B is "Points Committed," column C is "Points Completed," column D is "Velocity Percentage" (completed divided by committed). This report is essential for improving your estimation and sprint planning accuracy over time. See our guide on agile product management for how to use velocity data strategically.
Pro Tips
- Use Data > AutoFilter on your Sprint Plan sheet to filter by Status or Assigned To. This lets you quickly see all in-progress work or stories assigned to a specific person, making daily standups faster.
- Create a "Risk Register" sheet to list blockers and dependencies before the sprint starts. Link to stories in column A and describe the risk in column B. As blockers get resolved, move them to an "Archived Risks" area so you learn what typically slows sprints.
- Color-code your Story ID column by story type (Feature, Bug, Technical Debt) using conditional formatting. This helps you see at a glance whether you're weighted too heavily toward one type of work.
- Set up a simple email reminder using Excel's built-in alert features or Power Automate to ping your team on Friday to update their story status, ensuring your burndown data stays current.
- Use the tools directory to research how other teams structure their sprint data if you find your Excel approach needs evolution.
When to Upgrade to a Dedicated Tool
Excel works well for teams of 2-10 people planning 1-2 week sprints with straightforward backlogs. If your team grows beyond 10 people, multiple teams need to plan interdependent work, or you need real-time visibility across projects, consider a dedicated tool like Jira, Monday.com, or Asana.
You should also move beyond Excel if you need Gantt chart capabilities for dependencies across sprints, automated integrations with your development tools, or if your backlog exceeds a few hundred items. Once you're managing multiple concurrent sprints or release trains, the manual nature of Excel becomes a liability rather than a feature.
Use the Airtable vs Notion comparison to evaluate whether stepping up to a lightweight specialized tool makes sense for your team's scale.