How to How to Create a Velocity Chart in Excel in Excel - Practical Guide

Learn to create a velocity chart in Excel to track project progress and team performance over time. Velocity charts visualize the amount of work completed in each sprint or iteration, helping agile teams identify trends, forecast capacity, and optimize delivery. This tutorial guides you through data organization, chart creation, and customization.

Why This Matters

Velocity charts are essential for agile project management, enabling teams to forecast sprint capacity, identify bottlenecks, and communicate progress to stakeholders with data-driven visualizations.

Prerequisites

•Basic Excel knowledge and familiarity with data entry
•Understanding of sprint/iteration metrics and velocity concepts

Step-by-Step Instructions

Organize Your Velocity Data

Create a table with three columns: Sprint (A), Story Points Completed (B), and Date (C). Enter sprint names in column A, corresponding velocity values in column B, and sprint end dates in column C. Ensure data is sorted chronologically.

Select Your Data Range

Click cell A1 and drag to select your entire data table including headers (A1:C10 or your data range). This selection will be used to create the chart.

Insert a Line Chart

Navigate to Insert > Charts > Line Chart, then select Line with Markers. Excel will automatically plot Sprint (X-axis) versus Story Points Completed (Y-axis).

Customize Chart Titles and Labels

Right-click the chart and select Edit Chart > Chart Design > Add Chart Element. Add a title (e.g., 'Team Velocity'), axis titles (X: 'Sprint', Y: 'Story Points'), and enable the data table via Add Chart Element > Data Table.

Add a Trend Line for Forecasting

Right-click the data series (the line) and select Add Trendline. Choose Linear or Moving Average to visualize velocity trends and forecast future sprint capacity.

Alternative Methods

Use Column Chart for Sprint Comparison

Select data and insert a Column Chart instead of Line Chart to emphasize individual sprint performance and compare story points completed side-by-side across sprints.

Create Velocity with Cumulative Line

Add a helper column with cumulative story points using SUM formulas, then create a dual-axis chart combining velocity bars with cumulative burndown.

Tips & Tricks

✓Use consistent sprint naming conventions (Sprint 1, Sprint 2, etc.) to ensure clear X-axis labels.
✓Include at least 5-6 sprints of historical data for meaningful trend analysis and forecasting accuracy.
✓Format velocity values as integers to match typical story point systems (Fibonacci: 1, 2, 3, 5, 8).

Pro Tips

★Add a 3-sprint moving average trendline to smooth out velocity fluctuations and identify true performance trends.
★Use conditional formatting on the velocity column to color-code high/low performance sprints for quick visual identification.
★Create named ranges for your data (Data > Define Name) to make chart updates automatic when new sprint data is added.

Troubleshooting

Chart shows incorrect axis or jumbled data

Reselect your data range carefully, ensuring headers are included. Delete the chart and recreate it using Insert > Charts with the correct selection.

Trendline is not displaying

Ensure you're right-clicking on the actual data series line (not the chart background). If using non-numeric data, convert values to numbers first.

Chart legend is confusing or missing

Right-click the chart, select Edit Chart > Chart Design > Legend, and position it to the right or bottom for clarity.

Related Excel Formulas

SUM AVERAGE FORECAST

Frequently Asked Questions

What is a velocity chart?

A velocity chart is a line or column graph that displays the amount of work (story points) a team completes in each sprint or iteration. It helps track productivity trends and forecast future sprint capacity for agile project management.

What data do I need to create a velocity chart?

You need sprint names/numbers and corresponding story points completed in each sprint. Optional data includes sprint dates for better time-series visualization. Ensure you have at least 3-5 sprints of historical data.

Can I update the velocity chart automatically when new sprints are added?

Yes, use named ranges (Data > Define Name) for your data table and reference them in the chart. When you add new rows to the named range, the chart updates automatically.

What's the difference between velocity and burndown charts?

Velocity charts show completed story points per sprint (productivity), while burndown charts show remaining work over time within a single sprint (progress toward sprint goal).

Should I include incomplete stories in velocity calculations?

No, velocity traditionally counts only completed (done) stories. Incomplete work should not be included in velocity calculations to maintain accuracy and consistency.