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

 

USA Immigration Infographic

A Dashboard Spy reader was working on the creation of an infographic and called me for help. He’s not ready to show his work on this blog yet, but I wanted to share some inspiration I shared with him. He was looking to do something cool with a bar chart and I showed him this example. It shows United States immigration statistics in a cool layout. The bar chart is done in the shape of the U.S. flag. It’s a few years old, but definitely worth studying as an example.

Take a look:

Immigration statistics infographic

You’ll definitely want to see the original here in its full size. Click on this link to view a larger version:

U.S. Immigration Infographic

Interesting copy that sets up the user expectations from the infographic itself:

Who is coming to America? Immigration may have taken a back seat during the financial crisis, but the issue still needs resolving. While illegal immigrants sneaking over the border is still a primary concern, it’s good to know who came to our country legally and from where. This is a look at the 20 countries from which the most people came to America in 2008, how many immigrants already had family here, and how many received asylum when they arrived on shores.

iDashboard Healthcare Dashboard

This excellent healthcare dashboard example demonstates how powerful an executive dashboard can be for healthcare management. Please pay close attention to the corner of this dashboard for an unusual twist.

What you see in the video is an executive scorecard for a hospital’s management team. Each chart displays KPIs from a different business unit in the healthcare facility and is aggregated within the dashboard’s single screen. Along with the usual display of graphs, charts, KPIs and images, this interactive dashboard also shows video (see the lower right corner of the dashboard for the embedded video – that’s the twist I mentioned earlier. Pretty cool, right?).

This health dashboard also features a “Metrics Ticker” that scrolls key performance indicator (KPI) info across the very top of the screen.

Business Intelligence: A Guide for Midsize Companies

This SAP whitepaper on business intelligence is highly recommended for Dashboard Spy readers. Request a copy at this link: Business Intelligence: A Guide for Midsize Companies

Business Intelligence - A Guide for Midsize Companies

Business Intelligence - A Guide for Midsize Companies

Here is the Table of Contents:

  • Executive Summary
  • Signs Your Company Needs a Business Intelligence Solution
  • What Is Business Intelligence?
  • How Business Intelligence Is Helping Midsize Organizations
  • BI Components Explained
  • Much More Than Just a Simple Query
  • The Benefits of BI
  • Approaches to Implementing BI
  • Make Business Users Self-Sufficient BI Consumers
  • Facilitate Analysis with a Data Warehouse
  • What to Look for in a BI Product
  • What to Look for in a BI Vendor
  • Conclusion

Business intelligence (BI) allows business users to analyze and better understand their organization’s plans and results. It provides insight into what’s working correctly while identifying potential
problem areas in time for corrective actions to be taken. Find out how BI can help keep your midsize company ahead of the competition and how to choose the right BI product from the right vendor.

Your company’s focus has been on streamlining operations, acquiring customers, increasing revenues and profitability, and outpacing the competition. And while your company has continued to improve its operating efficiencies (sometimes by quickly learning from past mistakes), you believe you should
be spending more time analyzing what’s going on and predicting and planning for the future – rather than having your employees constantly running around putting out fires and trying to solve operational
problems based on gut feel.

Furthermore, you need the ability to determine where to concentrate your efforts. You can’t check every detail, yet you would like to be able to monitor your operations and be alerted to potential issues when they occur, no matter where you happen to be. You need to be able to focus on quickly finding and resolving potential problems while identifying and leveraging new opportunities despite the current uncertainty of the world economy.

You want to ensure that employee and departmental metrics are aligned with your company’s strategic goals.
You realize that your company does not have the resources of a Fortune 500 enterprise, but you believe that – person for person – your company’s employees are more passionate about their jobs and more committed to the customers. Your company may be relatively small right now, but it’s on a planned growth path. You’ve heard the term “business intelligence” and know that large companies – and maybe even
your direct competitors – are using it to obtain a competitive advantage. But right now, your company’s primary analysis tool is a spreadsheet.

You were there when your company’s first location was its founder’s garage. Now that your operations have graduated from the garage to real offices, isn’t it time your analysis capabilities graduated from spreadsheets to more powerful tools as well?

This paper from SAP offers guidance on how business intelligence can give your company a competitive edge.

Dundas Dashboard Samples

If you haven’t tried these interactive dashboard examples, please do take the time to try them out. They are highly interactive and give you a sense as to what can be accomplished in terms of “active” dashboard interfaces. These dashboards do require that you have Microsoft Silverlight installed.

You can visit these dashboard demos at the Dundas Interactive Dashboard Samples and Gallery.

I particularly like the Microsoft Incident system dashboard:

This Microsoft system center dashboard involves fictitious incidents and displays associated KPIs such as Time to Resolution by Day, On-Time Completion, TTR by Month, etc. Emphasis is on performance, and visualizations consist of various charts, scorecards and gauges.

Also, the Retail Operations Executive Dashboard is worth studying in depth:

This retail executive dashboard emphasizes operations analysis, and tracks KPIs such as earnings before interest, taxes, depreciation and amortization (EBITDA) by Department and per Employee. Visualizations consist of various charts, gauges, and scorecards.

Google Analytics Easy Dashboard

google analytics easy dashboard

Google Analytics Dashboards have become push button simple to produce. Instead of needing to program against the Google Analytics API and having to learn its intricacies, developers can now log into the Analytics Easy Dashboard Library, register to access the Google APIs console, then generate and copy and paste a prebuilt chunk of Javascript code. This code can then be tweaked to produce the type of data desired as well as the graph or chart they want it charted in.

So, yes, the GA Dashboard is called the Analytics Easy Dashboard. And indeed it is easy.

Take a look at the screenshot above for an example.

This from the web:

“So now you can create custom Google Analytics dashboards very quickly, with minimal code,” wrote Jeetendra Soneja and Nick Mihailovski, from the Google Analytics API Team, in a blog post.

Google built the tool in conjunction with a team of University of California at Irvine students, and the company is now working with another group of students at the university to simplify the tool further.

Google Analytics is a cloud-based application that webmasters use to monitor traffic on their websites and analyze usage in order to determine the effectiveness of different content sections and ads.

Trends in Business Analytics

Watch this video to see how Colin White explores the impact that trends such as analytic RDBMSes, Hadoop and MapReduce, the NoSQL movement, smarter and real-time analytics, and self-service business intelligence will have on businesses. These trends can be used in various ways to extend existing business analytics infrastructure to enable businesses to reach new levels of innovation.

Business Dashboards: A Visual Catalog For Design and Deployment

Dashboard Spy readers have been asking me to refresh my list of recommended books about Business Intelligence Dashboards. I’ve got a couple of new book reviews coming out soon, but wanted to quickly mention a favorite one. Titled “Business Dashboards: A Visual Catalog For Design and Deployment”, this book is a collection of over 120 screenshots of various dashboard examples.

It’s very much like a field guide to dashboard design. Gee, that kind of sounds like the various Dashboard Spy blogs, doesn’t it?

It retails at many book seller for about $90 but is available at amazon for $60. Use this link:

Here’s info from the publisher:

Focusing on designing the right dashboards for use in an organization, this timely, full color book reveals how to successfully deploy dashboards by building the optimal software architecture and dashboard design. In addition, it describes the value of this popular technology to a business and how it can have a significant impact on performance improvement. A unique collection of more than 120 dashboard images are organized by category. One of the chapters provides a step-by-step description of the key performance indicator (KPIs) design process. One of the appendices contains more than 1,000 examples of KPIs to help design the content of dashboards. The book also describes all the steps in a dashboard implementation and offers related advice.

Nils Rasmussen (West Hollywood, CA) is cofounder and Principal of Solver, Inc. Claire Y. Chen (Long Beach, CA) is a Senior Business Intelligence Architect at Solver, Inc. Manish Bansal (Irvine, CA) is Vice President of Sales at Solver, Inc.

Here is the table of contents:

Preface.
Book Summary.

Part 1. Introduction to Dashboards.

Chapter 1. Dashboards Definition.

Chapter 2. Dashboards’ Role in a Business Intelligence Solution.

Chapter 3. Why Dashboards Have Become so Popular.

Chapter 4. Types of Dashboards.

Chapter 5. Designing Metrics and Key Performance Indicators.

Chapter 6. Dashboard Scenario: Use Case.

Chapter 7. Getting Ready for the Implementation.

Part 2. Summary and Readiness Checklist.

Part 2. Creating the Right Business Intelligence Architecture for Dashboards.

Chapter 8. Why a Good Architecture Is Important.

Chapter 9. Data Warehouse Techniques.

Chapter 10. Data Replication and Delivery.

Chapter 11. Query Language and Interactivity.

Chapter 12. Data Warehouse and Business Intelligence Dashboard Paradigm.

Chapter 13. Mobile Business Intelligence.

Part Two. Summary and Readiness Checklist.

Part 3. Dashboard Design.

Chapter 14. Design Tips.

Chapter 15. Dashboard Examples.

Part Three. Summary and Readiness Checklist.

Part 4. Managing a Dashboard Project.

Chapter 16. Planning the Project.

Chapter 17. Engaging the Users.

Chapter 18. Project Tips.

Chapter 19. Training and Deployment.

Part Four. Summary and Readiness Checklist.

Appendix A. Software Selection Tips.

Appendix B. Installation.

Appendix C. Hardware Recommendations.

Appendix D. Security Authentication Techniques.

Appendix E. Metrics and Key Performance Indicator Examples.

About the Authors.

Index

IT Operations Management Whitepapers Pack

Available only for a few more days, this free bundle of IT Operations Management Whitepapers is a handy collection of “must think about” topics in the area of Information Technology Operations Management.

Here’s the link:

IT Operations Management Whitepapers

The core of this whitepaper bundle is a free eBook on IT Operations Management that normally sells for $11.95. Tailored for CEOs, CIOs, Managers and Consultants (and, of course, all of us business intelligence dashboard folks!), it includes:

– The Essentials of IT Operations Management Kit – This 136-page eBook focuses on actionable ideas to manage the challenges of IT infrastructure such as how to improve availability, performance and efficiency.

– The Cloud: Promises and Realities – This white paper explores the early drivers of cloud services, how they’ve influenced current expectations and the realities of cloud computing as they relate to data control, performance, security and ROI.

– Key Strategies for Deploying Enterprise Mobility Applications – This step-by-step guide for CIOs discusses how and where to mobilize applications in their enterprise.

– Data Quality Dashboards in Support of Data Governance – Exploring data management and data quality, this white paper describes the emerging technology of data dashboards and how they are instrumental to organizations formulating data governance strategies.

Here’s the link again:

IT Operations Management Whitepapers

Is Business Analytics Replacing Business Intelligence?

What’s in a name? Or should I ask, “What’s in an acronym?”. We all know and love “BI” or “Business Intelligence”. But have you noticed the movement from the major vendors in the industry shift to the use of “Business Analytics” rather than “Business Intelligence”?

I believe that business analytics is meant to imply more than business intelligence, but beyond that functional distinction, I think that the marketers are having more fun with “analytics” as a concept.

The recent Gartner BI Summit 2012 held in London had a category name change. Gartner now calls the category “Business Analytics” rather than the previous “Business Intelligence and Performance Management”.

Here’s a video from 2 of the speakers talking about how the interest in business intelligence is stronger than ever. They discuss the rising use of the term “business analytics”.

Other take-aways from the show:

  • Mobile presentation of business analytics dashboards is now a “must-have”.
  • “In-memory” data warehousing strategies are getting “hot”.
  • Interest in Business Intelligence is greater than ever.
  • It’s all about the ability to make business decisions.
  • Business Intelligence Competency Centers (BICC) are being renamed Business Analytics Teams (BAT)
  • Business Analytics is about innovation and prediction.