How do I make a burn down chart in Excel?

Question

I have several books I want to finish reading by a certain date. I'd like to track my progress completing these books, so I decided to try making a simple burn down chart. The chart should be able to tell me at a glance whether I'm on track to completing my books by the target date.

I decided to try using Excel 2007 to create a graph showing the burn down. But I'm having some difficulty getting the graphs to work well, so I figured I could ask.

I have the following cells for the target date and pages read, showing when I started (today) and when the target date is (early November):

Date         Pages remaining
7/19/2009    7350
11/3/2009    0

And here's how I plan to fill in my actual data. Additional rows will be added at my leisure:

Date            Pages remaining
7/19/2009       7350
7/21/2009       7300
7/22/2009       7100
7/29/2009       7070
...

I can use Excel to get either of these bits of data onto a single line graph. I'm just having difficulty combining them.

I want to get both sets of data on the same chart, with Pages on the Y axis and Date on X axis. With such a graph, I could easily see my actual read velocity relative to target read velocity, and determine how well on track I am toward my goal.

I have tried several things, but none of the help documentation seems to point me in the right direction. I get the feeling this might be a bit easier if all my data was in 1 big block of data points rather than in 2 separate blocks of data. But since I only have 2 data points for the target data (start and finish), I can't imagine I should need to make up fake data to fill the holes.

The question...

How can I put these two sets of data into a single chart?

Alternatively,

What's a better way to plot my progress toward a goal over time?

Answer 1

Say your data set is in Columns A and B of the first sheet.

1. On Insert ribbon, pick chart type as "Line with Markers"
2. Right-click on chart, "Select Data...". Select your data in columns without column labels, so your data range would be something like =Sheet1!$A$2:$B$5.
3. Profit! I mean you're done :-) You might want to change 'Series1' label Excel generates with an actual book name, you can do so in the above "Select Data" dialog.

You can do this with multiple books too - as long as their "pages remaining" data points are tracked on the same dates (e.g. Book2 data would be in Column C, etc...) Books will be represented by additional series.

Answer 2

For anything to do with Excel charts, you can't do better than Jon Peltier's site.

Answer 3

Have you tried Google? Found this on the first result for "excel burndown chart" gave me a page with what you're looking for.

Here's the link!

Answer 4

Why not graph the percentage complete. If you include the last date as a 100% complete value you can force the chart to show the linear trend as well as the actual data. This should give you a reasonable idea of whether you are above or below the line.

I would include a screenshot but not enough rep. Here is a link to one I prepared earlier. Burn Down Chart.