Excel Bar Burndown Chart: Build One Step by Step

Yes. In Excel for Microsoft 365 on Windows, list each workday, record the actual work remaining, calculate an ideal remaining-work value, then insert a combo chart with actual work as columns and the ideal pace as a line. This makes a bar-style burndown chart that shows progress against a simple target without confusing work remaining with work completed.

What a bar burndown chart shows

A burndown chart tracks how much work remains over time. The vertical axis represents a quantity such as hours, story points, or tasks; the horizontal axis represents days or another sequence of time periods. As work is completed, the actual remaining-work series should generally move down toward zero.

Many project guides use a line chart for both actual and ideal progress. A bar burndown chart uses vertical columns for actual remaining work instead, often with a line showing the ideal pace. The columns make each day’s amount easy to compare; the line gives a visible reference for whether progress is ahead of or behind the planned trajectory. Excel calls vertical bars columns, so the chart type to choose is usually Clustered Column, not the horizontal Bar chart type.

The chart is a planning aid, not a forecast or a guarantee. It shows the figures entered in the worksheet. If the team changes scope, revises estimates, or updates work late, the chart reflects those inputs only after the data is updated.

Set up the worksheet data

Use one row for each planned work period. The example below has ten workdays, a starting estimate of 40 units, and one actual remaining-work entry per day. Put the total starting work in E1, with the label Total starting work in D1.

In A1, enter Date; in B1, enter Actual remaining; in C1, enter Ideal remaining.
Enter the ten workday dates in A2:A11. Exclude weekends if the team does not plan to work on them.
Enter the remaining-work estimate for each day in B2:B11. For example, B2 is 40 at the start, and later values are the amount still unfinished at the end of each day.
Enter 40 in E1. This is the initial estimate used to draw the example’s ideal line.

With the dates in A2:A11, total starting work in E1, and ten work periods represented by those rows, enter this formula in C2 and fill it down through C11:

=MAX(0,$E$1*(1-(ROW()-ROW($A$2))/(ROWS($A$2:$A$11)-1)))

The formula returns 40 in C2 and 0 in C11, with evenly spaced target values between them. For example, it returns about 35.56 in C3. It uses row position rather than the number of calendar days between dates, so the ideal pace stays even when weekends are omitted. If the number of work periods changes, adjust the A2:A11 range in the formula to match the first and last data rows.

Actual remaining values are normally entered as observations, not calculated from the ideal line. If the remaining estimate rises after new work is added, record the increase: that can reveal a scope change or a revised estimate. Do not substitute completed work for remaining work. A chart of completed work rises over time, while a burndown is intended to show remaining work falling.

Create the column-and-line chart

Select A1:C11, including the column headings.
On the ribbon, choose Insert > Insert Combo Chart > Create Custom Combo Chart.
In the chart setup, set Actual remaining to Clustered Column and Ideal remaining to Line. Keep both series on the primary axis because they use the same unit.
Choose OK. If Excel assigns the date series incorrectly, use Chart Design > Select Data to check that the date column supplies the horizontal category labels and that only the two remaining-work columns are plotted as series.

Excel may offer a combo-chart button directly under Insert > Charts, depending on the ribbon layout. The important configuration is one column series and one line series, with dates as categories. Avoid a secondary axis unless the two plotted quantities genuinely use different units; in this example, a second scale would make the comparison misleading.

On Excel for Mac, select the same range and look for Insert > Charts > Combo. If the chart is already inserted, Chart Design > Change Chart Type provides chart-type settings. The ribbon layout differs from Windows, but the intended setup remains actual remaining as columns and ideal remaining as a line.

Format the chart so it is easy to read

Give the chart a specific title, such as Work Remaining by Workday. Label the vertical axis with the unit, for example Story points remaining or Hours remaining. If the audience may not know which series is which, keep the legend visible and use distinct colors: a solid color for actual columns and a contrasting line for the ideal pace.

Use a zero-based vertical axis. A truncated scale can exaggerate small differences between actual and ideal values.
Keep the vertical-axis maximum at or slightly above the starting amount, such as 40 in this example. Excel may choose a different automatic maximum; right-click the axis, choose Format Axis, and adjust Bounds if needed.
Show dates in a compact, readable format. If labels overlap, widen the chart or use fewer date labels rather than removing the date sequence from the data.
Consider data labels only when there are few bars. Labels on every column can clutter a chart with many workdays.

The chart’s columns represent the remaining amount at each recorded point; they are not durations or separate pieces of completed work. A column height of 25 means 25 units remain at that point. It does not mean 25 units were completed that day.

Choose the right burndown data

Agree on the unit and update timing before comparing actual values with the ideal line. A team that estimates in story points should not mix those values with hours in the same series. Likewise, choose whether each actual value is recorded at the start or end of a workday and apply that convention consistently. A value recorded before the day’s work is not directly comparable to one recorded after it.

The example assumes a fixed starting estimate and a straight-line target across ten work periods. That is a simple baseline, not the only possible plan. If the work is intentionally scheduled unevenly, create ideal values that reflect the agreed plan rather than using an even decline. Keep scope changes visible instead of silently resetting the starting total: a sudden increase in remaining work may be useful information about added scope, newly discovered tasks, or changed estimates.

For a sprint chart, the team may track total story points remaining; for a task-based chart, it may track tasks still open. These measures answer different questions. Counting tasks gives every task equal weight even when their sizes differ, whereas effort-based units represent the estimates the team has chosen to use. The chart is only as meaningful as the consistency of its measurement.

Common chart problems and fixes

The chart slopes upward. Check that the actual series contains remaining work, not cumulative completed work. An increase can still be valid if added or re-estimated work raised the remaining total.
The dates appear as bars or as a plotted series. Use Chart Design > Select Data to remove any unintended date series and set the date column as the horizontal category labels.
The ideal line is flat or missing. Check that C2:C11 contains numeric formula results, that the chart includes column C, and that the ideal series is set to Line in the combo settings.
Actual bars show gaps or zeros. Blank cells, text entries, and zero values are not interchangeable. Use a number for a measured remaining total; leave a cell blank only when there is no observation, and decide how the chart should display missing data.
The chart looks too dramatic. Check the vertical-axis minimum. Set it to zero when comparing amounts, rather than letting a high minimum magnify small fluctuations.
The line does not reach zero on the final date. Make sure the last row is included in the formula range and in the chart selection. The example formula reaches zero in the final row of its defined period.

When you add another day, extend the date and actual-data rows, update the ideal formula’s final-row reference, and expand the chart data range. Converting the input range to an Excel table with Insert > Table can help a chart include appended rows, but check the combo series and formula references after changing the layout.

Bar chart, line chart, or another option?

A column-and-line combo chart works well when the reader needs to compare a discrete daily actual value with an ideal trend. A line chart for both series is often cleaner when there are many dates, because columns can become crowded. A horizontal bar chart is usually less suitable for a timeline: its categories are laid out vertically and do not naturally read as time moving from left to right.

A burndown is also different from a burnup chart. A burndown displays remaining work and typically trends toward zero. A burnup commonly displays completed work increasing toward a target, sometimes with a separate total-scope line. If the main question is whether completed work is growing while total scope changes, a burnup may communicate that more clearly. For a small project, a formatted worksheet table can be sufficient; a chart is useful when the trend needs to be scanned or shared.

Frequently Asked Questions

Can Excel make a burndown chart automatically?
Excel does not infer a project’s remaining-work data or ideal pace from a task list automatically. Enter or calculate those values in worksheet columns, then create and configure the chart. The combo-chart setup takes only a few steps once the data is arranged.

Should a burndown chart use bars or lines?
Either can work. Use columns for actual remaining work when day-by-day comparisons matter, and a line for the ideal pace. Use lines for both series when the timeline is long or a continuous trend is easier to read.

Why does my burndown chart go up?
First check whether you plotted completed work instead of remaining work. If the correct measure is plotted, an upward move can reflect added scope, revised estimates, or corrected task data; it does not automatically mean the chart is broken.

Can I include weekends in the chart?
Yes, if weekends are part of the project’s schedule or you want them visible. If the team only works on weekdays, using workdays as the data rows avoids implying that the same amount of work is expected every calendar day. Keep the ideal formula aligned with the periods you actually include.

Can I use story points instead of hours?
Yes. Story points, hours, or task counts can be charted as remaining-work units, but choose one measure and use it consistently. Avoid treating estimates in different units as directly comparable.

Keep the chart useful over time

Review the underlying values whenever the chart seems surprising. Confirm the dates, unit, starting estimate, actual remaining figures, and formula range before changing colors or axes. Update the chart at a consistent cadence and preserve meaningful scope changes in the data. With those habits, an Excel bar burndown chart can make progress and changing expectations visible without suggesting more certainty than the estimates support.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.