How to Create a Burndown Chart in Google Sheets

Trying to figure out if your project is on schedule by staring at a dense list of tasks is a surefire way to get a headache. A burndown chart cuts through the noise, offering a simple, visual way to see your project's progress at a glance. This guide will walk you through exactly how to build and interpret a dynamic burndown chart using nothing but Google Sheets.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly Is a Burndown Chart?

A burndown chart is a visual representation of the work remaining in a project versus the time available to complete it. Think of it as a progress speedometer. The vertical (Y) axis shows the amount of work left to do (often measured in tasks, hours, or story points), and the horizontal (X) axis represents time (usually in days or weeks).

At its core, a burndown chart has two critical lines:

• The Ideal Effort Line: This is a perfectly straight line that slopes downwards from your starting point (total work planned) to zero on your final day. It represents the perfect, steady pace you would need to maintain to finish exactly on time.
• The Actual Effort Line: This is the line that tracks the real amount of work left at the end of each day. It will zigzag up and down depending on your team's progress, unplanned work, and other real-world factors.

By comparing where your "Actual" line is relative to your "Ideal" line, you can immediately tell if you're on track, ahead, or falling behind schedule. It’s an invaluable communication tool for project managers, stakeholders, and the entire team.

Before You Build: Setting Up Your Data in Google Sheets

A great chart starts with well-organized data. Before we even think about creating a chart, let's set up a simple table in Google Sheets to track our project's progress. A clean data source makes the rest of the process incredibly easy.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Define Your Work Unit

First, decide how you will measure "work." The key is consistency. Common units include:

• Number of Tasks: Simple and straightforward for projects with similarly sized tasks.
• Story Points: Popular in agile and scrum methodologies, where points represent the complexity and effort of a task.
• Estimated Hours: Useful for time-based projects where you can accurately estimate the effort required for each task.

For our example, we'll keep it simple and use the total number of tasks. Let’s assume we have a project that will last for a 10-day sprint and has a total of 80 tasks to complete.

Step 2: Create a Project Data Table

In a new Google Sheet, set up a table with the following columns. This structure is designed to make both tracking and charting as simple as possible:

• Day: The day of the sprint or project (e.g., Day 0, Day 1, Day 2...). You can also use specific dates.
• Actual Remaining: The number of tasks you actually have left to complete at the end of each day. This is the only column you will update manually.
• Ideal Remaining: The calculated "perfect" number of tasks that should be remaining if you were proceeding at a steady pace. We will use a formula to automate this.

Your initial setup should look something like this. You can also add some key project variables above your table to make your formulas dynamic, such as "Total Tasks" and "Total Days."

Step-by-Step Guide to Creating the Burndown Chart

With our data table created, we can now start plugging in the formulas and building the chart itself. Follow these steps carefully.

1. Calculate Your Ideal Burndown Rate

The "Ideal Remaining" line needs a consistent rate of reduction. This is your ideal burndown rate, or the amount of work you should complete each day to stay on track. The formula is simple:

Ideal Burn Rate = Total Work / Total Time

In our example, that's 80 tasks / 10 days = 8 tasks per day.

2. Populate the "Ideal Remaining" Column with Formulas

Now, let's use that burn rate to fill out the "Ideal Remaining" column automatically.

1. In the first cell of the "Ideal Remaining" column (cell D3 in our example, corresponding to Day 0), simply reference your total tasks. So, the formula is =B1.
2. In the cell below that (D4), which corresponds to Day 1, we will subtract the ideal burn rate from the previous day's value. The formula will be =D3-($B$1/$B$2). Here, we've used absolute references (the $ signs) for the Total Tasks and Total Days so the formula doesn't break when we drag it down.
3. Click the small blue square in the bottom-right corner of cell D4 and drag the formula down to the last day of your project. Google Sheets will automatically calculate the ideal burndown path dropping to zero.

Your sheet should now look like this:

(Note: formatting placeholders, as actual visual cannot be rendered here)

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

3. Fill in Your "Actual Remaining" Data

This is where reality meets the plan. At the end of Day 0, your "Actual Remaining" value will be the same as your "Ideal Remaining" value (80 tasks). From then on, at the close of each day, you'll update this column with the true number of tasks still open. This is the only manual part of the process.

Let’s say after Day 1 you completed 7 tasks. Your actual remaining would be 73. After Day 2, you finished 10 more, bringing the total down to 63. Fill in this data as your project progresses. To make the chart look clean, leave the "Actual Remaining" cells for future days empty.

4. Insert and Configure the Chart

Now for the fun part! Let’s visualize this data.

1. Select your data range. Click and drag to highlight your entire data table, including the headers (e.g., A2:D13).
2. Go to the menu and click Insert > Chart.
3. Google Sheets will likely guess a chart type. In the Chart Editor on the right, under "Setup," change the "Chart type" to a Line chart.
4. Make sure your "X-axis" is set to the 'Day' column. Your "Series" should automatically pick up "Actual Remaining" and "Ideal Remaining." If not, you can add or remove them here.

You should now have a basic but functional burndown chart directly on your sheet!

5. Customize Your Chart for Clarity

A default chart is good, but a well-customized chart is much more effective. Let's make a few tweaks using the "Customize" tab in the Chart Editor.

Chart & Axis Titles

Give your chart a clear title, like "Project Burndown Chart." Add titles to your axes: set the Horizontal axis title to "Project Sprint (Days)" and the Vertical axis title to "Tasks Remaining."

Series Formatting

Make your lines distinct. It's common practice to make the Ideal line a bit less prominent. Click on "Series," select the "Ideal Remaining" series, and change its color to light gray and perhaps make it a dashed line. Make your "Actual Remaining" line a strong, solid color like blue or dark gray.

Set a Vertical Axis Minimum

A burndown chart should always go to zero. Under "Vertical axis," set the "Min" value to 0. This properly anchors your chart and makes the journey towards completion much clearer.

After these customizations, you'll have a professional and easy-to-read chart that clearly communicates your project's status.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Read and Act on Your Chart

Having the chart is one thing, using it to make decisions is what matters most. Here’s what the position of the "Actual" line tells you:

• If the "Actual" line is above the "Ideal" line: Your project is behind schedule. This is a signal to investigate. Is the team blocked? Was the work underestimated? Was new work added (scope creep)? This is a great prompt to open a conversation and find a solution.
• If the "Actual" line is below the "Ideal" line: You are ahead of schedule. Your team may be working more efficiently than planned, or perhaps the tasks were easier than estimated.
• If the "Actual" line zigzags around the "Ideal" line: You are more or less on track. Small variations day-to-day are completely normal in any project.

The burndown chart is less of a grading tool and more of a diagnostic tool. Use it to foster transparency and proactive problem-solving within your team. When everyone can see the progress (or lack thereof), it's much easier to rally together to get the project back on course.

Final Thoughts

Creating a burndown chart in Google Sheets is a straightforward and powerful way to bring clarity to your project management. By plotting your ideal pace against your real-world progress, you provide your whole team with an at-a-glance status update that’s far more effective than a giant to-do list.

While this manual method works, we know that updating spreadsheets across multiple teams and projects can feel like a full-time job. It’s also difficult to connect project completion data to marketing campaign results or sales pipeline velocity without more manual data wrangling. That’s why we created a tool to automate this process. With Graphed, we can connect directly to dozens of sources and ask for the report we need in plain conversation. Instead of formula-building and manual updates, we simply ask, "create a burndown chart from my HubSpot projects for this quarter," and get a live, interactive dashboard instantly.