Master Dynamic Burn Down Charts in Excel

Welcome to another Excel video! In this tutorial, I will guide you on how to create a dynamic Burn Down Chart used in Agile and all forms of Project Management in an Excel template, eliminating any chances of user errors. I'll demonstrate how to enhance the formulas to make them more dynamic, ensuring accurate data representation. Additionally, I will show you how to utilize conditional formatting and a touch of VB magic to emphasize active rows and columns, simplifying data input. Stay tuned to learn these valuable Excel skills!
#excel #agile #scrum #burndown #projectmanagement #pmo #fyp
Download a copy of the Excel file here: drive.google.com/drive/folder...
Also check Patreon for a version of the Burn Down Chart Template which includes more task capacity (25) with some additional data validation on the data entry: www.patreon.com/ChangeTipsand...
Delivered by @Changetipsandtools
This video contains:
00:00 - Intro
00:59 - Demo of the Burn Down Chart Template
07:00 - Start with a blank canvas and save it as a macro enabled workbook.
07:34 - Build our initial layout for data entry.
13:58 - Build our Burn Chart calculations without the automation first.
20:07 - Create our Burn Down Chart for Actuals, Ideal Burn Down and Forecast data
23:27 - The problem I have with this version and why we need to make the formulas more dynamic and make life easier for the user.
24:55 - Make our formulas dynamic and remove user error.
26:33 - The XMATCH Function
28:08 - The VALUETOTEXT Function
29:05 - Automate the Actuals calculation for our Burn Down Chart
32:05 - Automate the Forecast calculation for our Burn Down Chart
41:05 - Using Find and Replace insert the XMATCH function into multiple formulas
42:01 - How to hide errors with Conditional Formatting
43:05 - How to highlight the active row and column with Condition Formatting and a very simple piece of visual basic.
46:28 - Add a header to our sheet
47:20 - Setup our protection on our sheet.
50:46 - Amend our vertical axis to deal with finishing our work early (i.e. stop negatives)