3 Steps to Create cool dashboards in excel

Posted on September 5th, 2006 in Analytics. Learn Excel – 15 comments

Dashboards are very common business monitoring tools, but creating them in excel with all the bells and whistles is not so easy. So here is a quick 1-2-3 on how to do it.

Lets take a sample of 2 consecutive year sales figures for 7 regions. The colums have Region name, 2004-05, 2005-06 figures and finally YoY Growth percentages. The lame dashboard should look something like this:

But may be we can make it little better. Ideally, a person looking at this would like (to know) the following things:

  • What are the things that are going up / down / remaining constant
  • The chart should look simple and not cluttered; meaning, there cant be multiple columns to present information. He/she should be able to look at one column and concluded something
  • May be little graphics wont hurt the presentation while retaining the information.

Well, how to get it in 3 steps?

  1. Type the following formula in the cell F5 and drag it to apply to all the cells

    [Click on the image to see bigger version of the formula]
  2. Select the range F5:F11, goto Format- Conditional Formatting and enter the following values there:

    [Click on the image to see bigger version of the formula]
  3. Finally, if its already not, change the font of the worksheet to Arial, (see those arrow marks, they are not available in all fonts. And btw, if you dont know how to insert them in the formula use Start- Programs- Accessories- System Tools- Character Map and then locate the symbols.)

So, go ahead and impress everyone with the cool dashboards.

Share this tip with your friends

Andrew Pearce says:

This is great BUT I am attempting to modify and adapt for a dashboard at work. with regard to conditional formatting how can I show an insignificant change ie if the % change is -1% and 1% then colour the cell contents yellow?

This way significant changes are coloured red (negative) or green (positive) with insignificant changes coloured yellow.

The collective intelligence in the office is stumped and would dearly love to come back with an answer!

Thanks in advance – Andrew

I think there is some thing wrong in the formula that you have provided. If you notice the first sheet all your data is in the C and D columns whereas your formula is making comparisons with Column E. i.e. IF E11 0. To begin with there is no data in the cell E11. And, if I go by your 2nd excel screenshot all the up and down percentages should be in column E and I do not understand why you have asked to choose the range F5:F11 and under the conditional formatting you are putting it as $E11. How can this conditional formatting be done in Excel 2007?


Microsoft Excel Basics

Become a confident, capable Excel 2007 and 2010 user with this customized Excel training!

Covers Excel 2007 and 2010

Microsoft Excel is one of today’s most powerful and versatile business tools, but if you’re not familiar with its basic functions, formulas, commands and keystrokes, you’re setting yourself up for frustration and disappointment. This Excel for beginners training seminar gives you strong fundamentals to build on. With the basic Excel training you’ll learn how to use charts, graphs, fonts, borders, shading and more and produce spreadsheets that communicate better and really get attention. You’ll discover professional Excel tips for brilliant, distinctive output every time, on every worksheet.

This one-day workshop will show you how to turn Excel into your most powerful productivity ally. You’ll learn hundreds of Excel tips to help you work faster, smarter and savvier than ever before!

With just one class of Excel training, we’ll show you how to:

  • Master Excel formulas and functions
  • Design and organize professional looking worksheets
  • Add charts and graphs to give your spreadsheets a visual punch
  • Copy, move, delete, insert or replace anything in your worksheet with ease
  • Solve common printing problems and end irritating surprises once and for all
  • Create and use simple macros that save time and sidestep mistakes
  • And much more!

What our Customers are Saying.

Seminar Overview

Take a quick tour of Excel basics!
  • Shortcut menus: the most efficient ways to access commands for any task
  • Help for Help! How to find what you need in Help without endless searching and scrolling
  • How to select cells or ranges of cells 2 methods to choose from
  • Excel Workbooks: the backbone to efficient file management
  • How to find a single file or a particular workbook in record time
  • Build a worksheet from the ground up
    • What to consider before you create a worksheet expert advice on building a worksheet framework that will accomplish your objectives
    • Tips for entering text and numbers, and a super-quick way to cancel or confirm your entry
    • Ways to copy and move data without anxiety
    • The view you need how to zoom in or zoom out of the worksheet
    • When titles and labels keep moving out of view: techniques to freeze them in place while you move around your worksheet
  • Harness the real power behind the numbers
    • Formula and Function basics on-the-mark advice that will end confusion once and for all
    • Mathematical formulas made easy: how to subtract, multiply or divide data
    • Basic Excel functions that let you calculate averages, percentages and much more using built-in predefined formulas
  • Learn the techniques that produce visually exciting worksheets!
    • The secrets to attractive, readable worksheets: 5 formatting guidelines that apply to any worksheet you produce
    • How to control alignment, decimals, dollar signs and more in one easy operation
    • Fonts, shading and borders that increase the readability and attractiveness of your worksheet
  • Get the output you want without trial-and-error printing
    • Print Options: the many choices Excel gives you to customize your printing. Plus, how to add headers, footers and page numbers to a worksheet
    • No more strange page breaks how to set manual page breaks and ensure that your titles and labels print on every page
  • Add impact and visual punch to your data
    • An easy-to-understand glossary of the terms used in charting and graphing data
    • The Chart Wizard how to easily produce vivid, exciting charts to accompany your data, even if you’re a total novice
    • Where to actually put your chart: all about embedded charts, separate charts and chart sheets within a workbook
  • Gain speed, efficiency and ease at every session!
    • Steps to customize Excel and achieve maximum personal productivity
    • Large worksheets with Split Windows the best way to effortlessly view distant figures side by side
    • A practical look at database basics to get you started
    • For mouse-haters: a quick guide to shortcuts that keep your hands on the keyboard
    • An end to macro mystery how you can automate many tasks using macros
  • *NOTE: This seminar is delivered via computer-less training to ensure you won’t waste valuable time entering data, correcting mistakes and waiting for other seminar attendees to catch up. You’ll receive a handy workbook guide that summarizes key tips for easy reference once you return to the office.

    Download Brochure

    Continuing Education

    This event is eligible for the following continuing education credits:

    Register Today

    1-Day Seminar – US $79.00


    Welcome to RK Solutions

    All of us from RK Solutions are well known to the world through the means of providing dedicated Accounting Software Solutions and Web Interface Solutions with connectivity to Tally ERP 9.

    Our experts are well experienced in providing the best sort of results to the client’s requests and requirements. We are specialized in Rental Software, ERP Software Solutions, Interface Solutions, Tally.ERP 9 Set up, Tally Implementation, Tally.ERP 9 Assistance, Tally Migration, Tally Integration in addition to a total business answer upon Tally.ERP 9 Software Solutions and Internet Marketing Solutions.

    We offer our top class services for affordable and competitive packages which is the reason for having ever lasting relationship with our existing clients and new clients. Trust is the character and client satisfaction is the motto behind our company in order to attain, we do more than what you need. We are proud enough to say that we can handle any order related to our dedicated field of work.

    Tally Products

    Tally.ERP 9 – the latest evolution of the Tally series-took 3 years and the dedicated efforts to create. It has grown from a basic accounting package into a simple-yet-sophisticated business management software product.

  • Tally.ERP 9 Single User
  • Tally.ERP 9 Multi User
  • Tally.ERP 9 Auditor’s Edition Order Now
    Tally Support and Services

    As a company we do not believe that our responsibility ends just by selling our product, in fact that’s when our engagement starts. During your installation or following your installation, you may still have queries, so it should be comforting to know that our support team will be on hand to help you.

  • Tally AMC Contract
  • Tally Onsite width: 250px; margin: 0 10px 5px 0;” src=”http://www.rksolution.co.in/img/icons/home/icon-1.png” />Rich web client technology provides stand-alone application look and feel

    Users can view files and file version differences, streams, and perform stream maintenance (create and delete).

    Secure access via HTTPS ensures that all transmitted data is safe.

    Tally.NET Services

    Your business data stays with you locally, and is never stored on Tally.NET servers or on systems accessing that data via Remote Access.Integrated within Tally.ERP 9 and Shoper 9, this new feature enables the users to report and track their queries from within the product.

  • Tally.ERP 9 Remote Access Services
  • Tally.ERP 9 Integrated Support Services
  • Self-service using Control Centre
  • Software Assurance Services
  • Tally.ERP 9 Data Synchronisation Services
    Business Advisory Services

    CAs can deliver various services around Tally – ranging from continuous audit to system configuration ?>

  • #

    All articles with ‘dashboards’ Tag

    In this amazing guest post, the winner of our 2016 dashboard contest Chandeep Explains how he constructed the jaw dropping beauty (shown above) using Excel, creativity, love and sweat. Grab a full cup of coffee (or whatever liquid fancies you) and read on. Take lots of notes and play with the ideas in Excel while reading to maximize your learning.

    Around 2 months back, I asked you to visualize multiple variable data for 4 companies using Excel. 30 of you responded to the challenge with several interesting and awesome charts, dashboards and reports to visualize the financial metric data. Today, let s take a look at the contest entries and learn from them.

    I am really sorry for the delay in compiling the results for this contest. Originally I planned to announce them during last week of July. But my move to New Zealand disrupted the workflow. I know the contestants have poured in a lot of time effort in creating these fabulous workbook and it is unfair on my part. I am sorry and I will manage future contests better.

    Here is a simple but effective rule for your next dashboard. Tell all versions of truth. All versions? But there is only one version no? Of course, there is only one version of truth (or data), but you can present that in different ways, thus creating different perspectives, offering different insights. By using an interactive element (slicers [ ]

    This is the final part of our series on how to analyze half a million customer complaints. Click below links to read part 1 2.

    Customer satisfaction scorecard

    In the previous parts of this case study, we understood what kind of complaints were made and where they came from (states). For the customer satisfaction scorecard, let s focus on individual companies.

    This is part two of our three part series on how to analyze half a million customer complaints. Read part 1 here .

    Analyzing Regional Trends

    As introduced in part 1, our complaints dataset has geographical information too. We know the state zip code for each complaint. Please note that zip codes are partial or missing for a 10% of the data.

    In this article, let s explore three ways to analyze regional trends.

    1. Regional trends by state, product issue
    2. Complaints per million by state
    3. Complaints by zip code


    Most advanced Excel users know that slicers are cool. Today, let s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.

    First see a quick demo

    Looks slick, eh? Read on.

    Hello all, prepare to be amazed! Here are 43 creative, fun informative ways to visualize KPI data.

    About a month ago, I asked you to visualize KPI data. We received 65 entries for this contest. After carefully reviewing the entries, our panel of judges have discarded 22 of them due to poor charting choices, errors or just plain data dumps. We are left with 43 amazing entries, each creatively analyzed the data and presented results in a powerful way.

    How to read this post?

    This is a fairly large post. If you are reading this in email or news-reader, it may not look properly. Click here to read it on chandoo.org .

    1. Each entry is shown in a box with the contestant’s name on top. Entries are shown in alphabetical order of contestant’s name.
    2. You can see a snapshot of the entry and more thumbnails below.
    3. The thumb-nails are click-able, so that you can enlarge and see the details.
    4. You can download the contest entry workbook, see play with the files.
    5. You can read my comments at the bottom.
    6. At the bottom of this post, you can find a list of key charting dashboard design techniques. Go thru them to learn how to create similar reports at work.

    Thank you

    Thank you very much for all the participants in this contest. I have thoroughly enjoyed exploring your work learned a lot from them. I am sure you had fun creating these too.

    So go ahead and enjoy the entries.

    Imagine you are the head of training department at ACME Inc. You arrange training programs round the year to empower your team. It is hard work, coordinating between employees, trainers, department heads, venues and coffee machines. What if there is something to help you keep track of all this? I am not talking about getting you a shiny new iPad, you silly. I am talking about a tracker calendar built in Excel that ties everything together (well, almost everything, you still have to fill the coffee machine.)

    We are going to build a training program tracker calendar using Excel.

    What is in this session?

    In this podcast,

    • Book announcement about Dashboards for Excel
    • Story of my first ever dashboard
    • Important lessons Requirement Analysis for dashboards
    • Resources for creating awesome dashboards
      • Podcasts
      • Books
      • Courses


    I have an exciting announcement to share with you. My first ever print book Dashboards for Excel . co-authored with Jordan Goldmeier is releasing on15th September .

    Dashboards for Excel what is it?

    As the name suggests this book is a handy guide for anyone preparing dashboards, management reports and business intelligence workbooks using Excel. The key benefits are,

    • Learn the visualization tools, charts, tables, and graphs important to management.
    • Understand what management doesn’t want to see in a report
    • Turn around dashboards faster and cheaper than ever before
    • Understand the key role dashboards play in an organization
    • Analyze real-world dashboards to apply important features to your own organization
    • Utilize understated, but powerful, Excel formulas and VBA code
    • Avoid common pitfalls in Excel development and dashboard creation
    • Get started with the Excel data model, PowerPivot, and Power Query

    As you can see, this is an advanced Excel book with lots of examples, case studies and practical tips.

    Cascading drop downs enhance usability of your dashboards interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.

    There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.

    Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.

    How to combine the power of pivot tables with elegance of your dashboards?

    The answer is: GETPIVOTDATA()

    Here is a best practice to improve your dashboard usability. If you have an interactive dashboard, highlight user selections thru conditional formatting.

    Check out below quick video to understand what this means.

    Whenever you have a dashboard that is quite long or spans across multiple worksheet tabs, it can be hard to use. Here is a simple trick to make your dashboards user friendly. If your dashboard has form controls, create duplicate sets of them and place them in locations where users are looking. For example, If your [ ]

    Making your workbooks, charts, dashboards presentations beautiful is a time consuming process. It is a mix of art craft. Naturally, we spend hours polishing that important slideshow or visualization. But do you know about simple features in Excel that can save you a lot of time and help you create gorgeous output?


    Infocaptor Excel Dashboard license allows you to connect to flat files and Excel based data sources and quickly enable you to perform ad-hoc data visualizations.

    The best tool to build your dashboards on your desktop and put them on the web or intranet

    For very limited time, when you make a purchase you get free 60 mins of dashboard development/consultation. Once you request for the meeting, we will schedule a time and send you a webex or Gotomeeting invitation to join the meeting. We want to make sure you really get started building your dashboard.

    Microsoft Excel Files can be used as database to store information and this data can be visualized on the dashboards.

    In order to bring data from Excel to the dashboards there are some basic rules to be followed

    Organize your data

    Intuitive Visualizations that helps you find insights from your Data

    Create Giant visualizations You have infinite canvas to work upon. Vector Graphics (SVG) scales with your imagination



    The MCSA 2012 includes the following courses:

    70-410: Installing and Configuring Windows Server 2012 (MCSA)

    Mentoring 70-410 Installing and Configuring Windows Server 2012 – 0 hr(s)
    TestPrep 70-410 Installing and Configuring Windows Server 2012 – 2.5 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: Servers – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: Storage – 2 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: Print Services – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: Hyper – V – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: Networking – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: DNS – 1 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: Active Directory – 2.5 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: AD Groups & GPOs – 2 hr(s)
    Microsoft Windows Server 2012 R2 – Installing and Configuring: Security – 2.5 hr(s)

    70-411: Administering Windows Server 2012 (MCSA)

    Mentoring 70-411 Administering Windows Server 2012 – 0 hr(s)
    TestPrep 70-411 Administering Windows Server 2012 – 2.5 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: Deploying and Managing WDS – 1 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: Updating and Monitoring – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: File Services and Encryption – 2.5 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: Network Services and Access – 2 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: RADIUS, NPS, and NAP – 2 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: Service Accounts and Policies – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: Managing Active Directory – 2.5 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: Group Policy Processing – 2 hr(s)
    Microsoft Windows Server 2012 R2 – Administration: Managing GPOs and Preferences – 1 hr(s)

    70-412: Configuring Advanced Windows Server 2012 Services (MCSA)

    Mentoring 70-412 Configuring Advanced Windows Server 2012 Services – 0 hr(s)
    TestPrep 70-412 Configuring Advanced Windows Server 2012 Services – 2.5 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: HA – 2 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: VMs and DR – 2.5 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: Storage – 1 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: DAC – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: DHCP & DNS – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: AD Domains – 1 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: AD Replication – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: PKI – 1.5 hr(s)
    Microsoft Windows Server 2012 R2 – Configuring Advanced Services: Federation – 2 hr(s)

    Bundle price. R 4,495 or R4 995.00 with 35% deposit R1 748.25 & 9 monthly installments of R360.75

    International exams are optional and can be written at any Pearson Vue Testing Centre in South Africa.��Discounted international exam vouchers can be purchased through IT Academy.

    How our courses work:

    On enrolment we issue our learners with an username and password to access their courses for a 12 month subscription.�Courses are designed in video with audio and coupled with 24/7 advanced mentoring. At the end of each module learners are required to answer multiple choice questions, these marks are registered in our database in real time.� On successful course completion of 70% and above learners will receive IT Academy Course Mastery Certification via registered mail, at no additional cost.

    Courses are Printable & Downloadable
    24/7 Advanced Mentoring
    24 x 7 x 365 e-mail Support
    Free Cram Tests from www.troytec.com and www.certmagic.com
    Discounted international exam vouchers
    12 months Subscription
    IT Academy Course Mastery Certification


    Advanced Excel – Online Course

    This Microsoft Excel Advanced training course will give you a deeper understanding of the many complex and in-depth features of Excel.

    With over 400 formula functions you need to know how to find the ones you need, This course will prepare you to locate and use the many advanced functions in Excel and will provide an in-depth look at the most commonly used.

    This is an interactive course that will not only show you but also allow you to participate in the lesson by clicking and typing the appropriate commands which will drastically improve your memory of the course.
    This Course has been approved by the International eLearning Standards

    Module 1 – Understanding Commands

    Module 2 – Finding and Highlighting Formulas

    Lesson 1: How to Find and Highlight Formulas
    How to find and highlight formulas, Hierarchy of actions in formulas, ROUND, ROUNDUP, ROUNDDOWN and Using CONVERT

    Module 3 – Date and Time

    Lesson 1: Understanding and Using Date and Time
    Understanding Date and Time formatting, Using formula with your Date and Time, Using DATEDIF and END-OF functions

    Module 4 – Macros

    Lesson 1: Understanding and Using Macros
    Understand Macros, How to create Macros, Creating triggers, Multiple actions and Fixing errors.

    Module 5 – Using Formulas

    Module 6 – PivotTables

    Lesson 1: Understanding and Using PivotTables
    Understanding and creating PivotTables, Using formulas in PivotTables, Making interactive PivotTables

    Module 7 – Cleaning your Spreadsheets

    Lesson 1: Cleaning your Spreadsheets
    Steps to making charts cleaner, Adding projections and trends, Adding pictures and charts, Creating break-even charts

    What Will I Learn?
    You will learn the advanced features that will improve your excel skills including the following:
    – Finding and Highlighting Formulas
    – Date and Time Functions
    – Macros
    – Using formulas to find and edit data
    – PivotTables
    – Cleaning your spreadsheets
    – Problem Solving and Commands

    – Microsoft Excel 2013 (most functions taught in this lesson are compatible with other Excel versions)
    – A basic understanding of Excel

    About Shawn
    Shawn is a Professionally Certified Instructional Designer and eLearning Developer with 25+ years experience. Committed to continuing education, current and emerging technologies and best practices.
    Shawn specialises in customizing solutions involving a collaborative approach to ensure the concepts introduced in the program can come to life within an organization and are supported for sustainability.
    Read More

    Great Course


    How to create Microsoft Excel dashboards to monitor the progress of a software development project (part 1 of 3)

    When it comes to manage software development projects, you have to monitor a lot of different quantitative and qualitative metrics in order to answer the main question:

    As in any other project you have to take care of the usual suspects in project controlling like the completion rate of tasks, the milestones and quality gates, the budget adherence, etc. In software development projects, however, there are a couple of very important specific additional facets to be monitored closely:

    1. The actual status and the trend of software defects
    2. The test progress, test coverage and test success
    3. The actual status and the development of change requests

    Today’s article is the first of a 3 post series on how to create minimalist, dynamic software project dashboards with Microsoft Excel; this time a software defect monitor dashboard including the Microsoft Excel workbook for free download.

    Create a one-page dashboard for monitoring the actual status of software defects in order to have an overview of the software quality at a glance. A dashboard that can be used in your overall project reporting as well.

    The Key Performance Indicators

    The main metric we care about in this part of the series is the number of software defects. According to Wikipedia a software defect (aka software bug) is

    “… the common term used to describe an error, flaw, mistake, failure, or fault in a computer program or system that produces an incorrect or unexpected result, or causes it to behave in unintended ways.”

    Usually software defects are categorized by severity, e.g. assigning a defect to one of the following classes:

    • Severity 1: Critical
      Critical defects result in a total failure of the software or unrecoverable data loss. A workaround is impossible.
    • Severity 2: Major
      Major defects result in massively impaired functionality. A workaround does not exist or is impracticable.
    • Severity 3: Minor
      Minor defects result in non-critical failures of the system. A satisfactory workaround exists.
    • Severity 4: Cosmetical
      Cosmetical defects are of low or very low impairment. A reasonable workaround exists.

    Different strokes for different folks. Other projects and other companies may use different naming conventions (like showstopper / severe / medium / minor, or others), but usually there are 4 or maximum 5 categories to classify the severity of a software defect.

    The actual status of a software bug can be classified into one of the following 3 main categories:

    • Assigned
      The defect was detected by Quality Assurance or during other tests (like the User Acceptance Test), assigned to and accepted by the software development team.
    • Resolved
      The defect was detected and assigned to the software development. The bug fix has already been delivered, but it was not yet retested by Quality Assurance.
    • Closed
      The defect was detected and assigned, development delivered the bug fix and Quality Assurance already successfully retested the fix.

    Again, terminology may differ across different software projects or companies. Furthermore there are a some more possible status like “in discussion”, “in review”, “rejected”, “duplicate”, etc. Though, in order to keep the showcase lean and simple, I am restricting myself to these 3 defect status.

    The Visualizations

    1. Software defects by severity and status for the actual week
      The snapshot of actual number of defects by status and severity is probably the first thing to look at. A simple table enhanced by a stacked bar chart and explanatory texts allows a quick overview:
    2. Software defects by status over time – cumulated and week on week
      An area chart showing the cumulated values of software defects over time and an additional line chart displaying the trend of assigned, resolved and closed defects visualizes all relevant information on the development of software bugs during the project:
      At the beginning of the project we expect the number of assigned defects to grow faster than the number of defects resolved or even closed. Towards the end of the project, however, we should see the cumulated number of assigned defects in the area chart to level off and the gaps between the areas to decrease. With regards to the line chart, this means the orange (resolved) and green (closed) line should tend to be above the red line (assigned) towards the end of the project.
    3. Average resolution and conclusion times – actuals and development over time

    The average resolution time and average conclusion time are very interesting additional measures regarding the status of software defects. The average number of days between the date of assignment and the date of resolution shows how long software development needs on average to fix a defect. The span between the date of resolution and the date of conclusion reveals the performance of Quality Assurance regarding retesting the delivered fixes. Finally, the period between the date of assignment and the date of conclusion can be used as one of the basic inputs on an estimation to complete.

    The visualization is pretty minimalistic: a table displaying the needed average number of calendar days by severity and a line chart showing the evolution of the 3 metrics over time.

    The Implementation

    As already discussed in a previous post. I am always trying to implement a workbook structure that strictly separates the data input sheet (“data”), the consolidation / calculations (“control”) and the dashboard. This case is no exception.

    Hence, you will find all formulas for consolidating the data on the worksheet “control”. There are some advanced formula techniques like array formulas, formulas using SUMPRODUCT and one complex formula calculating the ISO 8601 week numbers. I will not provide a detailed how-to tutorial on these formulas, but the following links might help you understand the used techniques:

    • For understanding the concept of array formulas you may want to check out Chip Pearson’s great website
    • Charley Kyd has a very good article on his website ExcelUser regarding SUMPRODUCT
    • An explanation on how to calculate week numbers is available in a previous post here on Clearly and Simply

    Agreed, all these formulas require advanced skills in Microsoft Excel, but they are no rocket science.

    The implementation of the used visualizations, however, should be self-explanatory: 2 tables directly linked to the results on the control sheet, a stacked bar chart, an area chart and 2 line charts. Microsoft Excel standard functionality and charts, no tricks and hacks necessary.

    The Result

    Here is the final result, a screenshot of the complete dashboard:

    A variety of other metrics may be added, like an estimation to complete, the defect fix fault ratio (i.e. the number of bad fixes compared to the number of delivered fixes), etc. but I guess this little showcase should get you going.

    The Download Link

    Here is the Microsoft Excel workbook for free download:

    All dates and numbers in this workbook are made up.

    What’s next?

    Monitoring the software defects of your project is only half the battle won. Another extremely important thing to track is the progress and performance of your Quality Assurance and testing. The next article will discuss a Microsoft Excel dashboard to monitor the test progress of a software development project.

    | | |