We've been a fan of the dashbroad approach for a long time because dashboard graphics can give readers a quick snapshot of multiple sets of dynamic data.  Charley Kyd, who studied journalism some years back, has developed a nifty plug-and-play package — Dashbroad Kit #1 — to generate these.  And below is a recent and relevant posting from Jorge Camoes that gives us some good tips on the topic.

10 tips to improve your Excel dashboard

Posted: 26 Jan 2008 06:42 PM CST

Excel is a great (but underrated) BI tool. Several BI vendors gave up fighting it and offer Excel add-ins as front-ends for their BI solutions. So, if you want to create a dashboard you should consider Excel, since it really offers better functionalities than many other applications for a fraction of the cost and development time. I know that Excel is not a one-size-fits-all solution, but first you should be sure that your requirements are not met by Excel. Let me share with you some random tips from my experience with the Demographic Dashboard.

But, shouldn’t I just ask my IT to create the dashboard?

This is a fact: many IT departments hate Excel. The IT spends millions in BI solutions and users keep using Excel. Why? Because they know it, they like it, they feel in control and can do what ever they want with the data. Ask your BI manager to replicate the image above using an expensive BI solution and he’ll come back six month later with something you didn’t ask for, to answer a need you don’t have anymore (I know, I’m oversimplifying…). Do you know Master Foo Defines Enterprise Data?

1. Go to the point, solve a business need

So, you have your idea for a dashboard, you’ve discuss the project it with the users (right?) and you are ready. But where to start? Remember this: a graph, a table, the entire dashboard, are merely instrumental to solve a business need. It’s about insights, not about data, not about design.

2. Don’t use formulas

Yes, I know, this is Excel, and it is supposed to have formulas. What I am telling you is that you should aim at minimizing the number of independent formulas, and this should be a fundamental constraint to your global strategy. Too often I see Excel used as a database application. It is not, it is a spreadsheet (not everyone finds this obvious).

Over the years I had my share of “spreadsheet hell”: a lookup formula in the middle of nowhere would reference a wrong range for no apparent reason. An update cycle adds a new column and suddenly there are errors all over the place. You leave the project for a week and when you come back you don’t know what all those formulas mean. Even if everything goes smoothly the auditing dep wants to trace every single result.

But how do you minimize the use of formulas? If your data table resides in an Excel sheet you’ll have to rely heavily on lookup formulas, and that’s one of the highways to spreadsheet hell. Instead, get the data from an external source (access, OLAP cube…) and bring data into Excel. Calculations should be performed at the source. After removing all the formulas you can, the remaining should be as clear as possible.

3. Abuse Pivot Tables

Every object (graph, table) in the Demographic Dashboard is linked to a pivot table. Let me give you an example. One of the charts shows population growth over the years, using 1996 as reference. Pivot tables can calculate that directly, I don’t need to add a new layer of complexity by using formulas (to calculate the actual values and look up formulas to get them).

The population table has 200,000 records, so I coundn’d fit into the Excel limit of 65 thousand rows (yes, that’s changed in Excel 2007, but it is debatable if a table with a million rows in a spreadsheet application can be considered good practice). By using a pivot table I can overcome that limit.

4. Use named ranges

To be able to use self-document formulas (”=sales-costs” is much simpler to understand than “=$D$42-$F$55″) is one of several uses of named ranges. But they are also the building blocks of interaction with the user and they make your Excel dashboard more robust.

5. Use as many sheets as you need, or more

You don’t have to pay for each additional sheet you use in a workbook, so use as many as you need. Each cell in your dashboard report sheet should point to some other sheet where you actually perform the calculations. You should have at least three groups of sheets: a sheet with the dashboard report itself, sheets with the base data and other group with supporting data, definitions, parameters, etc. Add also a glossary sheet and a help sheet.

6. Use autoshapes as placeholders

Once you know what you need, start playing with the dashboard sheet. Use autoshapes to test alternative layouts or, better yet, use real objects (charts, tables…) linked to some dummy data.

7. Get rid of junk

There are two ways to wow your users: by designing a dashboard that actually answer needs, or by planting gauges and pie charts all over the place (this one can guarantee you a promotion in some dubious workplaces, but it will not help you in the long run). In the series on Xcelsius Dashboards you can see how difficult is to create something beyond the most basic and irrelevant charts.

So, get rid of Excel defaults (take a look at this before/after example) and just try to make your dashboard as clean and clear as possible. You’ll find many tips around here to improve your charts, so I’ll not repeat myself.

8. Do you really need that extra-large chart?

Charts are usually larger than they should. What it really matters in a chart is the pattern, not the individual values, and that can be seen even with a very small chart.

9. Implement some level of interaction

A dashboard is not an exploratory tool, is something that should give you a clear picture of what is going on. But I believe that at least a basic level of interactions should be provided. User like to play with the tools and can they learn a lot more than just looking at some static image.

10. Document your work

Please, please, structure and document your workbook. Excel is a very flexible environment, but with flexibility comes responsibility… I am not a very organized person myself, but from time to time I try the tourist point of view: I pretend I never saw that file in my life and I’ll try to understand it. If I can’t or takes me too long, either I must redesign it or write a document that explains the basic structure and flow.

Bonus tip: there is always something missing…

Once you have a prototype, user will come up with new ideas. Some of them can be implemented, others will ruin your project and if you accept them you’ll have to restart from scratch. So, make sure the specifications are understood and approved and the consequences of a radical change are clear.

This is far too incomplete, but I’ll try to improve it. Will you help? Do you have good tips specific to the design of Excel dashboards? Please share them in the comments.

And this just in from CNet via O'Reilly Radar….

 Google To Release Embeddable Maps

Posted: 15 Aug 2007 08:38 AM CDT

By Brady Forrest

Over on CNet they have the scoop on an upcoming cool Google Maps feature.

Google will be releasing a new feature next week that will enable people to easily embed a Google Map into their Web site or blog, just like you can do with a YouTube video. No coding or programming required; just copying and pasting a snippet of HTML, a Google spokeswoman says.

Google Maps

“To embed a Google Map, users will simply pull up the map they want to embed–it can be a location, a business, series of driving directions, or a My Map they have created–and then click 'Link to this page' and copy and paste the HTML into their Web site or blog,” the spokeswoman said.

Given how smart of a feature this is I can't believe its taken this long for one of the major providers to release a feature like. Yahoo, Google, Live – they've all had the ability to get permalinks to a map for easy inclusion in a website. They've also all had APIs, but now a fully featured map, even those that have Mapplet data, will be fully embeddable on a person's website with cut-n-paste. Google Maps already dominate on third-party websites; this will increase that margin substantially.

We have long admired the code underlying the visual data site, TheyRule.net.  TheyRule was among the pioneering sites to link data — in this case, corporatate board members of American's largest public companies and their shared networks with other board members.  So it is that we are pleased that Greenpeace has taken the concept, and maybe even a lot of the code, to visualize the networks of organizations and people tied to Exxon.  According to the site:

This website is the first chapter of a larger Greenpeace project provide a research database of information on the corporate funded anti-environmental movement.

The database compiles Exxon Foundation funding to a series of institutions who have worked to undermine solutions to global warming  and climate change in recent years.  Individuals working with these organizations and their global warming quotes and deeds are detailed.  There are downloadable source documents or links to sources are provided throughout. 

We've long been intrigued with Benford's Law and its potential for Analytic Journalism.  Today we ran across a new post by Charley Kyd that explains both the Law and presents some clear formulas for its application.

An Excel 97-2003 Tutorial:

Use Benford's Law with Excel

To Improve Business Planning

Benford's Law addresses an amazing characteristic of data. Not only does his formula help to identify fraud, it could help you to improve your budgets and forecasts.

by Charley Kyd

July, 2007

(Email Comments)

(Follow this link for the Excel 2007 version.)

Unless you're a public accountant, you probably haven't experimented with Benford's Law.

Auditors sometimes use this fascinating statistical insight to uncover fraudulent accounting data. But it might reveal a useful strategy for investing in the stock market. And it might help you to improve the accuracy of your budgets and forecasts.

This article will explain Benford's Law, show you how to calculate it with Excel, and suggest ways that you could put it to good use.

From a hands-on-Excel point of view, the article describes new uses for the SUMPRODUCT function and discusses the use of local and global range names.  [Read more…]

Assoc. Prof. Paul M. Torrens, at Arizona State University's School of Geographical Sciences (torrens at geosimulation dot com) continues to turn out interesting simulation models. Most recently they are about crowd movement, but the methods are applicable to many venues. See his work at Geosimulation.org
   

Yet another interesting innovation of mapping.  Imagine what this might mean for analysis of tourism sites or crowd control?

From O'Reilly Radar (http://radar.oreilly.com/)

Bio Mapping Project in Stockport This Weekend

Posted: 20 Jul 2007 01:04 PM CDT

By Brady Forrest

The Bio Mapping project sponsors people to walk around an area with a GPS and a Galvanic Skin Response sensor and logger. The emotional responses of the participants are then mapped. The map of San Francisco (pdf) was recently completely. They had previously developed a beautiful map of Greenwich (viewable via Flash viewer or Google Earth).

The project has been run by Christian Nold for several years now. Here's how he describes the project in an interview:

You ask people to go out into the streets and take an emotion walk. Can you explain?

Bio Mapping is a participatory methodology for people to talk about their immediate environment, locality and communal space. I'm trying to use 3D visualisation as a way of talking about the space. It's not representational. As part of this method I have developed a device, which can be used by lots of people. It consists of a lie detector connected to a GPS (Global Positioning System) unit, which measures your location and your physiological arousal at the same time. By combining the two I can talk about physiological arousal in certain locations. A Galvanic Skin Response sensor in the form of finger cuffs measures the sweat level. Fitted out with this device, people go for a walk and when they return their data is visualised and annotated.

How?

By downloading data onto my laptop data it is then transformed by my software and then projected onto Google Earth. The Galvanic Skin Response sensor measures the amount of skin conductivity. I'm suggesting that a change in skin conductivity not only tells something about your body, but also suggests an emotive event. I'm plotting the amount of change in the skin resistance level versus location. There are various technical transformations and averaging I have to do to the data. I'm sampling once every four seconds, because I found this optimal for this kind of spatial representation.

This weekend they are biomapping Stockport. If you happen to be in the UK you can participate — I know that I would. I would love to set this up at one of our conferences to watch the emotional response of attendees throughout the day and at different sessions. Did the keynote speaker or product launch really get people excited? What about that debate?

---

 

Our long-time friend Marylaine Block has again served up some good librarian-centric material on her blog/newsletter “Neat New Stuff” and “Exlibrius”. This time it's a fine essay — with links to pertinent sites — on one of our favorite topics, visualization.
Here's the top, but go to
ExLibris #301
Archive:
for the complete package.

VISUALIZING INFORMATION
by Marylaine Block

On several occasions librarians have asked me to speak about the future
of reference service – if, indeed, there IS a future for reference
service. I think librarians are worried that the simple delivery of
information is not a growth area for libraries because that's where our
primary competitor, the internet, excels, with its search engines and
resources like Wikipedia.
Helping people make sense of the information they've retrieved is
something else again, and that, I believe, is where the future of
reference service lies. After all, who is dying to compete with
librarians in explaining to people how to fill out online FAFSA and FEMA
applications? Who is fighting librarians for the opportunity to show
people how to select, combine, and chart a variety of data points in
government data sets? Who else wants to help students analyze and
retrieve the kinds of information needed to solve a problem or research a
topic? Who else worries about making sure the information retrieved
matches the user's purposes and level of knowledge and sophistication?
Who else is interested in providing context for the information?
One of the most effective tools we can use to help people make sense of
information is visualization.

An interesting conference just completed in the UK.  Be sure to scroll down to listen to the presentations and, in some cases, see the slides.  Go to  http://wiki.openstreetmap.org/index.php/State_of_the_map

The State Of The Map

From OpenStreetMap

State Of The Map

The State Of The Map is the name of our OpenStreetMap conference(s). However, as there has only been one so far, all the information below refers to the State of the Map conference in 2007, held at the University of Manchester, UK.

OpenStreetMap's first conference was at the centre of the geographical industry universe on the weekend of 14/15 July 2007, supported by Manchester University's School of Environment and Development.

• 1 What happened?
  • 1.1 Talks & Panel debates
• 2 Who Attended?
• 3 Location

---


 

FYI: From the O'Reilly Radar

Unveiling the Beauty of Statistics

Posted: 11 Jul 2007 03:01 AM CDT

By Jesse Robbins

I presented last week at the OECD World Forum in Istanbul along with Professor Hans Rosling, Mike Arrington, John Gage and teams from MappingWorlds, Swivel (disclosure: I am an adviser to Swivel) and Many Eyes. We were the “Web2.0 Delegation” and it was an incredible experience.

The Istanbul Declaration signed at the conference calls for governments to make their statistical data freely available online as a “public good.” The declaration also calls for new measures of happiness and well-being, going beyond just economic output and GDP. This requires the creation of new tools, which the OECD envisions will be “wiki for progress.” Expect to hear more about these initiatives soon.

This data combined with new tools like Swivel and MappingWorlds is powerful. Previously this information was hard to acquire and the tools to analyze it were expensive and hard to use, which limited it's usefulness. Now, regular people can access, visualize and discuss this data. Creating an environment where knowledge can be shared and explored.

H.G. Wells predicted that “Statistical thinking will one day be as necessary for efficient citizenship as the ability to read or write.” Proponents of specific public policies often use statistics to support their view. They have the ability to select the data to fit with the policy. Democratization of statistics allows citizens to see the data that doesn't fit the policy, giving the public the power to challenge policymakers with new interpretations.

I highly recommend you watch Professor Rosling's exceptional summary of these exciting changes (where I got the title for this post), as well as his talks at TED.”

---

 

From the Directions Magazine “All Points Blog” …..

Monday, July 9. 2007

How to Cite Maps Used in School/Journalism

Village Soup/Knox County (ME) Times has an article about a proposed plan for Rockland Harbor. The article includes a map, apparently taken from MapQuest, and enhanced (it's a screen capture; it does not use the API). Below is this caption:

A map of O'Hara Corporation's proposed project site in Rockland. (Photo by MapQuest.com)

I'm sure that was a cut and paste error, but I do have a point. As more citizen journalism and more students use the Web to search for and use content, they (and others) need guidance in how to cite such references. Let's assume the map was used under “fair use” (I'm not sure it was). How is it to be cited? Must it include the copyright info for the data? (It does not.) Must it include a link? What do Manuals of Style say? What does MapQuest say?

Posted by Adena Schutzberg in Oops at 06:00 | Comment (1)