Excel Dashboards & Reports For Dummies Cheat Sheet - dummies
Cheat Sheet

Excel Dashboards & Reports For Dummies Cheat Sheet

From Excel Dashboards and Reports for Dummies, 3rd Edition

By Michael Alexander

Companies and organizations are always interested in business intelligence — raw data that can be turned into actionable knowledge. This need for business intelligence manifests itself in many forms. Dashboards are reporting mechanisms that deliver business intelligence in a graphical form. Most data analysis benefits from a spreadsheet, so Excel is inherently part of any business-intelligence tool portfolio.

Adding Symbol Fonts to Your Excel Dashboards and Reports

A creative alternative to using the icon sets offered with conditional formatting is to use the various symbol fonts that come with Office. The symbol fonts are Wingdings, Wingdings2, Wingdings3, and Webdings. These fonts display symbols for each character instead of the standard numbers and letters.

image0.jpg

The idea here is simple. Make a formula that returns a character, then change the font so that the symbol for that character is shown based on the font you select. For example, imagine you want to test if values in column A are greater than 50. You can enter the formula =IF(A1>50,”P”,”O”). In a standard font like Arial, this formula would return either a P or an O. However, if you would change the font to Windings2, you would see a check mark or an X.

image1.jpg

Great Online Resources for Excel Dashboards and Reports

You can learn a lot from the approaches other have taken to design their Excel dashboards and reporting mechanisms. Here is a list of sites (in no particular order) dedicated to business intelligence and the presentation of data through dashboards. Visit these sites to get ideas and fresh new perspectives on Excel dashboards and reports:

  • Perceptual Edge: Visualization expert Stephen Few provides some fascinating insights on data visualization and dashboarding. A generous amount of articles and examples can be found at his site and his blog.

  • Edward Tufte’s website: Professor Edward Tufte is an icon in the field of information design and data visualization. He shares his thoughts around visual communication in a series of articles at his site. Although many of these ideas are academic in nature, they’ll get you thinking in new ways about how to best present data.

  • Chandoo’s blog: Chandoo (aka Purna Chandra) came out of nowhere around 2007 to amaze us all with his innovative Excel tips via his blog Chandoo.org. He has a knack for clean, simple visualization techniques — which he shares freely to all his readers.

  • Jon Peltier’s website: Although his is not a site dedicated to dashboarding, Jon Peltier offers over 200 pages of unique and effective Excel charting ideas. A quick look at the excellent tutorials posted on his site will have you reeling with new ideas on how to chart your Excel data.

  • ExcelCharts.com: Jorge Camoes’s blog and website is focused on helping his readers make sense of their business data through better data analysis and visualization techniques. With several years’ worth of articles, his site proves to be rich source dashboarding concepts.

  • The Dashboard Spy: The Dashboard Spy posts examples of business intelligence dashboards, pointing out examples of good and bad dashboard design. This is a virtual warehouse of dashboarding ideas.

  • Juice Analytics: Zach and Chris Gemignani of Juice Analytics use their site as a platform to critique charts and offer interesting ideas around reporting data.