How to Make a Sprint Burndown Chart in Excel

A burndown chart is a tool from Agile, where all the “Stories” or items of work to be completed in a given iteration (a two-week time frame), with their accumulated points (how small or large each story is) are shown on a chart as the points are completed, from their total at the beginning to zero at the end of the iteration.

This video and article shows how to make a sprint burndown chart in Excel. Enjoy!

Step 1

First add the general framing and colours to your sheet:

Step 2

Next, fill out the backlog IDs, feature or story names, dates, and points as necessary.

Step 3

Now we can add in the formulas for Remaining Effort, Ideal Trend, and Points Completed This Sprint:

• Remaining Effort: First =SUM(D7:D18), then =D19-SUM(E7:E18)
• Ideal Trend: =D20-($D$20/COUNT($E$5:$N$5))
• Points Completed This Sprint: =SUMIF(E7:N11,”> 0″)

Step 4

Now we can create the burndown chart. Select Remaining Effort and Ideal Trend rows, go to Insert > Line chart.

And now you have a beautiful sprint burndown chart in Excel!

How to Make a Customer Journey Map in Excel

A Customer Journey Map is a brilliant way to discover pain points in your customer’s journey, as they buy your product or service.

It notes the different areas (such as customer service points, digital apps or web pages) that a customer will travel through, the different process steps in each area, then rates their experience at each point from 1 to 10. Each rating is given a nice “emoji” icon (Happy or Sad), and a few notes on what the customer said at that point.

With all this it gives you a complete customer picture. It also allows us to find areas to improve, what features to deliver next, or where to focus our project on.

Enjoy!

Step 1

First setup the general framing and colours on your sheet:

Step 2

Then add in the areas and process steps for each area that a customer will go through:

Step 3

Note the customer satisfaction from 1 to 10 at each point in the journey.

Create the emojis by going to Home > Insert > Shapes, and insert a curved line and a circle together, to make a face. Right click them and group them together.

Step 4

Add in the customer comments so you can see exactly where to improve.

And now you have a beautiful Customer Journey Template made in Excel!

How to Make a Product Comparison Template in Excel

Product or feature benchmarking is a key part of finding out where to go forward in your business.  It is easy to do with the right product comparison template. You simply write the features that you are looking at, down one side. Then you note the different companies (or areas, or products) along the top, and note whether they have that feature where the two parts intersect.

Before you know it, you have a clear idea of the companies or products that have the features you want. Here’s how you create one!

Step 1

First setup the general framing and colours for your sheet:

Step 2

Next, add in “Wingdings 2” font, “O” and “P” letters, which will make ticks and crosses.

You can use conditional formatting to colour the “O”s and “P”s like this:

• Home > Conditional Formatting > Cell Value Equal to “P” and “O”, changing the font colour for each.

Step 3

Next add the “Ranking System”, with the formula:  =COUNTIF(C6:C37,”P”) at the top of each column.

You can add the right colours with Conditional Formatting:

• Home > Conditional Formatting > Only Top or bottom Ranked Values (1)

Now you have a beautiful Multi-Criteria Product Benchmarking template in Excel!