Looking to create a burndown chart in Excel?
In this article, we will walk you through the concept of a burndown chart, the types of burndown charts, and how to make a burndown chart in Excel.
Burndown chart is used by agile project managers to track their projects. This makes it easy to determine, whether things are going according to plan and if you’ll be able to meet your deadline and reach your objective.
Burndown charts are one of the easiest ways to track your project’s progress in relation to its goals and deadlines. While for many teams, Microsoft Excel is the preferred method, the easier way is to use a visual project management software like Nifty to automate progress tracking in real-time.
What is a burndown chart?
A burndown chart is a visual representation of the amount of work remaining to perform versus the amount of time available. Such a chart is commonly used in scrum boards and other agile workflow lifecycles for software development. In addition to software development projects, Burndown charts can be used on any project in which progress overtime matters.
The vertical axis in a burndown chart represents the amount of work while the horizontal axis shows the amount of time, the project (or sprint) starts in the upper left corner and ends in the lower right corner.
Here you can see a sample of a burndown chart:
The two lines that travel between the two spots reflect the following:
- The ideal work line (green): If all goes according to plan, this is how the scrum team will actually burn down the remaining work. It’s a perfect estimate that serves as the foundation for all project computations.
- Actual work remaining line (blue): This is a representation of the actual work completed and the amount of work that remains in the pipeline. Because it is subject to real-life events and delays in the team’s path, it is rarely a straight line. You want the real work line to keep under the ideal line as much as possible.
A burndown chart can be used to estimate how long it will take to complete all of the tasks. During a working day, the software development team or any other team that is using the burndown chart can update the chart so that the whole team would know about the stage of the project and the amount of time needed to complete the project.
Burndown charts are essentially two sorts in the agile scrum process:
- A sprint (release) burndown chart: used to keep track of how much work is remaining in a particular sprint.
- A product burndown chart: used to control the amount of work remaining on an entire project.
Why use a burndown chart?
Here are some of the reasons to use a burndown chart for your software development projects or any other projects:
- Keeping track of the project’s scope creep
- Maintaining the team’s schedule
- Comparing the scheduled work to the progress of the team
How to create a burndown chart?
You can create a burndown chart in two ways. You may choose to use a project management tool that generates such important charts for you. Another way to create a burndown chart is to use Microsoft Excel to take a more manual approach.
How to make a burndown chart in excel?
So, let’s now see how to make a burndown chart in Excel in only three easy steps. We will focus on making a sprint burndown chart and suppose that the time needed to complete the sprint is 10 days and there are 10 tasks to do.
1. Create a Table in the Excel Sheet
The first step to making a burndown chart in Excel is creating a table in the Excel sheet. This table will establish the foundation for the sprint burndown chart and provide you with basic information about the amount of time you have left and the tasks you still have to complete.
Divide the tasks into two columns, one for the estimated amount of tasks you’ll have (your planned effort) and the other for the actual tasks you’ll have that day. Create columns according to your project’s demands in a new Excel sheet.
Here is a sample of a burndown chart in Excel:
This is the very simple version of data in the cells of the table and you can customize them so that they show what you actually mean. For instance, you can write the name of your product backlogs, estimated effort, actual effort, and remaining effort.
2. Add Planned Tasks Data
Now what you should do is to enter your data such as numbers in the planned tasks column, which represents the number of jobs you would like to have left at the end of each day of the 10-day sprint. Remember that the number of planned tasks is an ideal value and can be different from what actually happens in reality due to many reasons. For instance, your team may have a personal reason for a one-day leave, there may be out-of-schedule tasks and projects, etc.
As a result, any numbers you enter here do not account for any real-world issues that your team may face throughout the sprint. Here in this sample, let’s suppose that you have considered your planned tasks schedule to be one task per working day. The point to take into account is that you should weekends should not be assigned any tasks as they are not working days. So, remember not to take them into consideration while scheduling your planned tasks column.
The actual tasks column should be updated after each working day based on actual data. This number should represent the actual number of tasks that remain for the rest of the days of the sprint.
3. Create a Graph via a Burndown Chart
The rest of the work to make a burndown chart will be done by Excel. Your task is just to add the data based on your ideal as well as the actual data. To ask Excel to create the graph or the burndown chart, you should follow these steps:
- Select the three right columns of ‘Dates,’ ‘Planned,’ and ‘Actual.’
- In the top menu bar, select Insert.
- Select the line chart icon from the drop-down menu.
- Choose a basic line chart from the menu.
After making your burndown chart, you can manually change the values of the actual tasks column in the table and change the burndown chart.
Here is what your burndown chart should look like:
If your actual amount of work done is the same as what you planned in the planned tasks column, there would be only one line in your burndown chart. However, that is unlikely to happen because, as mentioned before, many things can occur to procrastinate completing the tasks based on what you have idealized before the start of the sprint.
Limitations of using Excel for burndown charts?
Excel is a powerful tool when it comes to data management. It makes data clear, dependable, and accessible. However, while the process of making a burndown chart in Excel is easy especially if you are an Excel expert, there are some flaws and limitations with it. Here is a list of Excel limitations for burndown charts:
1. Time-Consuming
Entering the sprint or project data in the Excel sheet can be very boring and time-consuming when making a burndown chart. It is because it should be done manually which would take up your speed. And the purpose of burndown charts is to show you the process of your sprints and projects within less than seconds so that you quickly know what is going on in your business and how productive your team has been. Making a burndown chart in Excel kills the whole purpose of burndown charts as it is slow, manual, and boring.
Imagine that you want to have a project burndown chart with many sprint burndown charts within it. Think of the amount of time you will lose as a project manager that you could spend on much more important things to make your team more productive.
2. Error-Prone
Excel is great for accounting because it facilitates the work of accountants by eliminating the calculator from their desk. However, in a process like making a burndown chart and especially when you are manually entering the data related to the actual amount of work done, Excel is prone to errors that would burn your burndown chart and your team’s efforts to ashes. If you, as a project manager or a scrum master, want to have an errorless and authentic burndown chart, Excel is a limited tool to meet your need.
3. Non-Collaborative
Being part of an Agile Scrum team means that there has to be a lot of communications and collaborations going on and the team should be working together. Yes, you can share your Excel sheet with your scrum team or ask the team at the end of each working day to report to you but is that not too traditional? Ever thought that it would give a kind of insecurity to your scrum team that you do not trust it? Or, it may also mean that there is a hierarchical order in your company that would keep your team members having normal communications with you? Excel is, therefore, not enough for scrum teams when it comes to collaborative burndown charts.
4. No Support for Mobile Devices
One of the top ten features to look for in any software is mobile compatibility. However, accessing or making the Excel burndown chart on your phone will be inadequate. On MS Excel, you study the fundamentals of data administration but that will not be enough as your business needs grow. Therefore, you will need to upgrade to more important, better, and more powerful tools.
What is the best alternative to Excel for making burndown charts?
The above-mentioned limitations can really affect your team’s productivity when managing projects. The alternative is to use a visual project management software that create burndown charts for you so you can focus on actual work . Wasting time on unnecessary tasks like building a burndown chart in Excel can easily be automated with better tools would eventually result in not focusing on more important things.
The best alternative for Excel and your visual project management needs is Nifty.
Nifty is a cloud-based project management tool that can meet all your project management needs. You can create milestones (Gantt Charts) for your projects as well as track time on projects for performance analysis to measure the productivity of your team in real-time.
Here are some key features of Nifty:
- Reporting: Get a birds-eye view across all your projects & workloads with Overviews. You can get an eye-catching project overview with colorful visuals instead of a plain burndown chart you get with Excel.
- Discussions: Discussions allow project team members to focus their efforts in order to make more informed decisions. They also facilitate collaboration in real-time to discuss ideas, gather input, and make important choices especially with the use of an idea board.
- Task Management: Organize, prioritize, and manage tasks with a high level of detail.
- Project Time Tracking: Drive efficiency and smarter decision-making with meaningful time logs.
- Milestones: Milestones serve as your visual project guide by automating progress based on task completion.
- Docs & Files: The centralized docs and files can help you maintain an organized workflow by consolidating project documents and files.
- Project Portfolios: That helps you bring better organization and more automation to your workflow.
👉 Ready to try Nifty? Get started for free.
Conclusion
Microsoft Excel is credited with laying the groundwork for advanced data management. But, time has changed and it is all about speed and efficiency when it comes to using tools. And using an Excel spreadsheet to create burndown charts will make you a traditional project manager in the modern era.
That is easy to avoid if you choose a proper alternative like graphic design software to Excel for making your burndown charts in your agile projects. Turn to Nifty and enjoy all its project management features at a reasonable price or eb=ven for free forever! Wish you the best of luck!
