When it comes to Excel, my favorite topic and the thing I have the most fun with is building Excel dashboards.  The truth is, a dashboard is like a picture, it’s worth a 1000 words.  You can LITERALLY at a glance determine the health of your business by simply plugging in your raw data into a dashboard and then getting some output that will either tell you the good news or the bad news.  That being said, Chandoo has come out with a pretty slick product that allows you to dump pretty much any set of KPIs (key performance indicators) into the dashboard and have a beautiful looking finished product dashboard that summarizes your numbers.  Today however, I want to look at 11 unexpected things that I learned from dissecting Chandoo’s Excel dashboard template workbook.

CHECK OUT THE EXCEL DASHBOARD TEMPLATE 

 

# 1: You Can Use Cell References To Create User Controlled Conditional Formatting

 

It is unbelievable that you are able to refer to a cell when applying your conditional formatting threshold settings.  This one I should have already realized, but to tell you the honest truth I hadn’t even thought about it. Make sure you start by using name ranges to make your life easier as Chandoo does in the example below.

 

 

 

# 2: Using Data Validation for Dynamic Data and Column Headings Within your KPI Excel Dashboard Details

 

I think this is just a great use of space within your dashboard to help you tell multiple different stories.  You can add data validation at the column heading level of your details on your dashboard and have new KPI values appear.

 

 

 

# 3: How to Use Excel Slicers to flip between displaying the top 10 records versus the Bottom 10 records.

 

Often times we want to see the top 10 as well as the bottom 10 records of our key metrics.  Chandoo has built in table slicers that sort your data depending on which slicer option is selected (bottom or top 10).

 

 

 

# 4: Allowing the User Create and Select How the Dashboard Header Should Be Displayed

 

Again, this is really cool functionality and there are a lot of formulas and other things working in the background to get this thing to work.  But the user is able to hit the display options tab of the dashboard and setup the header in the most suitable way for their current presentation.

 

 

 

# 5: How to Notify the User that an Excel Dashboard Setting Has been Duplicated and Needs to Be Fixed

 

Being able to notify the user that a setting for your dashboard has been duplicated is an awesome way to maintain the integrity of the data being displayed in your final dashboard.  Chandoo has developed and incorporated conditional formatting to notify the user that a setting needs to be changed using the COUNTIF formula.  Have a look below.

 

# 6: How to Dynamically Identify Poorly Performing KPI Metrics on your Dashboard

 

Chandoo also decided to included an indicator to show the dashboard user which metrics on their dashboard were not performing up to par. Depending on the options selected by the user, there is an indicator on the left side of the dashboard that highlights metrics that are concerning based on the desired performance measure.

 

 

# 7: Using Table Slicers to Sort your Column/Field Data within your Dashboard Details

 

It always amazes me what you can do with table or pivot table slicers.  Chandoo does an awesome job at allowing the dashboard user to sort the KPI details by different column headings as well as in ascending or descending order.

 

 

 

# 8: Providing the User the Ability to Change the Date Setting to Be For The Most Recent Data or For Older Data

 

Providing the dashboard user with the flexibility to use more recent data versus older data is a great way to provide access to analyze older data versus the newest and more recent data.  The setting tab again comes through in allowing the user to select which month they want to display in the KPI metric table.

 

 

 

# 9: How to Setup a Table of Contents that Links to a Specific Area of an Excel Worksheet

 

When you have so many different settings for  a dashboard, you want to make sure that it is intuitive and easy to setup.  Adding a table of contents that links directly to the cells that are required to update the settings is a great feature that was added to the Excel dashboard template. Each setting has a link in the table of contents that can be quickly accessed by the user.

 

 

 

# 10: How to Use Shape Layering to Pretty Up Your Dashboard

 

This is a great concept that I have already been toying with but you will definitely appreciate this in Chandoo’s Excel Dashboard Template.  He’s added shape layering with shadowing to pretty up the dashboard.

 

 

 

# 11: How to Use Sparklines to Identify Trends with Key Performance Indicators

 

Using sparklines to identify trends is nothing new, however, Chandoo does an awesome job at putting together a massive dashboard template that analyzes all of the KPI metrics that are important to the business.  He has included them within a shape with the corresponding data to really provide the quick analysis benefit to the dashboard.

 

 

 Summary

 

The biggest point I want to make here is that I always find it amazing that you can reverse engineer nearly anything in Excel.  Being able to do this allows you to improve on your Excel skill set at your own pace and it also really helps you understand and grasp the concepts that are truly behind the functionality of the worksheet.  One of my favorite things is buying and analyzing how other people are building dashboards in Excel as it helps me grow my set of Excel tools to improve the products that I provide to you.  If you do feel compelled to have a look and purchase the product from Chandoo, I’ve include the button affiliate link below (at no extra cost to you of course :)) if you feel like these are some things that you want to learn.  Also, really take a look at Chandoo’s product page as he does also include some pretty cool ebooks and other goodies in the premium package that can help you learn some of the amazing things that he applies to his dashboards.

I hope you enjoyed the post and feel free to leave a comment letting me know which number you would like to learn the most of the points mentioned above!

 

CHECK OUT THE EXCEL DASHBOARD TEMPLATE