Example of a Hopelessly Complicated Dashboard

Dashboards are at their best when they are outrageously simple. Their “at-a-glance” nature takes advantage of universal human perception into order to bring instant understanding of data trends.

This example is outrageously complicated instead of simple. Sorry, but I would hate to have to attempt to understand this particular excel dashboard. See if you can make heads or tails out of it.

Conditional Formatting in Excel

I was working in Microsoft Excel and got a bit stuck on an issue with a formula, so I immediately went in search of the answer from my favorite Excel teaching resource, chandoo.org. I did find the answer, but while I was there, a very helpful article caught my eye so I thought I’d pass it along.

Conditional formatting in Excel is one of those foundational techniques that you must employ when creating business intelligence dashboards with excel. I’m talking about coloring certain cells a different color depending upon the value of that cell value.

For example:

Conditional Formatting Excel

Click here to read exactly how to accomplish this effect. It’s very important that you master it as you’ll use it over and over in your work.

Format cells with conditional formatting

And be sure to check out Chandoo’s School for Excel and his modules on Excel Dashboards.

If you are interested in using Excel for Dashboards, click on this banner to learn more about Excel School for Dashboards:

Excel School for Dashboards

 

Add Economic Data to Your Excel Dashboards

All Excel users should take note of this new product. It’s really, really cool. You can easily add data and calculations from more than 40 million data series about the economy and public companies.

Use this link to learn more: KydWeb

Watch this video to see how.


KydWeb represents several firsts…

It’s the first Excel add-in that powers Excel formulas to return web data for business analysis, forecasting, and reporting. Within minutes after a user discovers a web page with useful data, her KydWeb functions can display the data in a report. And when the data updates, so will the report the next time it’s loaded. (For performance reasons, web data is cached during a session of Excel.)

It’s the first product Charley Kyd created that directly addresses our “crazy economy.” As he says, “Since the economy causes so many business problems, why do your spreadsheets ignore it?” KydWeb exposes users to tens of millions of data series that could help their managers find solutions to business problems.

This product is an easy way to enhance Excel dashboards. Now it’s easy to create dashboards that show public data. One of the bonuses includes an IncSight DB report that’s been upgraded to display the revenue of public companies, key economic trends, and cost indexes for key expenses of a company.

Use this link to learn more: KydWeb

Excel Keyboard Shortcuts

Download this excellent free Excel resource from Charley Kyd. It’s an excel worksheet with a listing of keyboard shortcuts for Microsoft Excel.

excel keyboard shortcuts

You can sign up for this helpful resource at no cost here:

Excel Keyboard Shortcuts (a new browser window will open. Follow the instructions in the top yellow box. It will also lead you to a nice gift from ExcelUser.com)

I use this for reference a lot.

Excel-based Enterprise Dashboard – excel worksheet xls file download

Dashboard Spy Update: Several readers have recommended the Charley Kyd eBook on using Microsoft Excel for Business Dashboards. I have started reading it myself and it is excellent. There are also many excel dashboard templates available as well. Use this link: http://tinyurl.com/exceldashboard

The best way to get started in using Microsoft Excel for business dashboards is to grab an existing excel dashboard, take it apart and try it out on your own data. Here is a post that shows such a spreadsheet dashboard with a link to the xls file. 

I see that many of you Dashboard Spies out there are very interested in the tried and true excel spreadsheet approach to dashboards. Thanks to a download from javaplanetinc.com, there is this excel dashboard xls file for you to play with. Here are 2 screenshots. The first shows the graphic presentation of the data. There are 2 larger sections across the top of the screen. On the left is a pie chart showing salary by department and on the right side, we have a simple listing of outstanding balances. Below this section we have 4 smaller portlets (sales by region, budget by project, current output by plant, and sales by business unit). To show you that this is straight excel, I’ve taken a screenshot of the second sheet in the workbook. Try this with your own data and see what you come up with. Remember to send us your dashboard screenshots so that we can all learn how executive dashboards apply to your business.

Business dashboard screenshot

excel-based dashboard

Homework: Don’t ignore excel as a dashboard tool. It’s graphing ability is powerful and the number of users is unmatched. If you need a refresher on the tool, see these books on excel.

Reading Assignment: Continuing on our study of the Malik book on implementing dashboards, please read this passage and think about how your company would go about choosing a dashboard vendor:

VENDOR SELECTION

There were four key criteria and feature requirements during the vendorselection process:

1. Web based for ease of access, administration, upgrades, security, andso on

2. Industry standard to ensure that experienced resources could be hired

3. Recognized and well-respected leader in the online analytical process-ing field

4. Company that spends a better than average percentage of revenues onR&D to ensure that applications remain competitive

So what or who is The Dashboard Spy? As his about page states, The Dashboard Spy is just a guy interested in the design of enterprise dashboards. He could not find any executive dashboard design source books (or even screenshots of real business dashboards) and so set about creating his own. Finally convinced to post his extensive collection of dashboard screenshots online, he was amazed to find how popular it has become. If you have a nice screenshot of a digital dashboard, balanced scorecard, or any business intelligence graphic to share, please send an email to info _at_ dashboardspy.com. Also check out The Dashboard Spy’s favorite books.