On misrepresenting data

By Chris Gemignani
September 27, 2006
Find more about: excel

Quick! What are three things wrong with this graph?

Troubled graph

Easy, right? First, nobody can eat that much broccoli. Second, the graduated shading makes it hard to see where the bar graphs end. Third, what's going on with those brussel sprouts? Zero pounds of sprouts were consumed, but the bar shows a value.

The brussel sprouts badness is based on Microsofts implementation of databars in the upcoming version of Excel. To quote the Excel 2007 blog:

The answer is that when we were doing usability testing of this area in Excel, we found that users preferred not to see blank data bars, so Excel’s default was set to a 10% minimum width.

Here's a picture showing Excel 12 databars.

Troubled databars

The databar for 170 shows that the minimum databar size is about 10% of the width of the cell. This makes the 170 value look like it's about a quarter of 170,000 rather than 1/1000th.

Misrepresenting data by default is like shipping Excel with broken statistical functions—it's something that should never have been considered. Hopefully these examples help illustrate just how misconcieved this idea is. Its discouraging to hear that this is justified by user preferences—gotta get some principles, guys.

On the other hand, the Excel 2007 team is doing a great job explaining the new features in Excel 2007 on their blog. If you use Excel a lot and expect to move to Excel 2007—one great reason to do so is the new Excel breaks the 65,000 row barrier—then you need to check out their blog. The Excel 2007 team is also reading and reacting to feedback, which is great. They've got plenty of time to fix this databar problem before release. In the meantime, the rest of us can use the in-cell graphing to do everything databars can do and more.

While on the topic of Microsoft blogs, if you're a typography geek (Jules/Jon, I'm talking to you), check out fontblog from the Microsoft typography team which has done excellent work (ClearType, web fonts, Consolas). Interesting, quirky and honest, a good read.

Finally, here's a picture of the graph with all three problems fixed.

Fixed graph

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.

11 comments | Show all comments only the last 5 are shown


September 28, 2006
mikael said:

It is also poorly labeled (or perhaps, just out of context). What is the period measured? What is the unit of consumer? It could be total weight of veggies consumed in the state of Texas during the month of July, or the average dinner plate in Boulder, Colorado. Without some additional description it is impossible to know if one thousand pounds of broccoli is unreasonable (Hey, man, I just really like broccoli!).

I am astonished that the excel team would even consider exagerating the scale of small numbers. I am also astonished that some users would actually prefer misrepresented data.


September 29, 2006
Henk said:

Well, showing a zero value isnt that strange. The dot and line we use in mathematics have no thickness, and still we show them..... But of course in data representation they distort the message, i.e. the feel for relative values.
Having said this, it might be good to suggest to Redmond to implement a thin line in a different color to show it is a DELIBERATE zero value, and not simply a forgotten value. It cld be used to discriminate to #NA() and real zero also.
As I am now in obnoxious mode: Chris, this beta carotene you want to show can be in the use of colours for the bars (hues of yellow/orange for different amounts of beta carotene), so another parameter in the chart. It can be defined in Excel as conditional formatting, so the colour automatically adjusts to the amount of beta carotene inserted in the parameter cell (Hint: how to do this can be a great next blog!). So no need to insist - you can have it both ways. Excel is awesome - don't we all agree?


February 11, 2007
Jonas Maurus’ maurus.net » The new Microsoft Excel misrepresents data by default said:

[...] The default graphs were hideous at best, but now, thanks to a focus-group-tested and user-centric decision probably made by marketing drones without a brain: Microsoft Excel deliberately misrepresents data, because it turns out, users didn’t like empty cells in bar-graphs… idiots :-). Update: to change this non-sensical idiotic default: select the range, open VBE and type “activecell.formatconditions(1).percentmin = 1” in the immediate window Permalink [...]


October 1, 2007
Don Parish said:

FYI, The Excel team blog(http://blogs.msdn.com/excel/) is seeking guidance on fixing some of the issues you raised here at: http://blogs.msdn.com/excel/archive/2007/10/01/data-bars-feedback-please.aspxpx


October 2, 2007
Chris Gemignani said:

Thanks, Don!

Your name

Email (optional, will not be shared)

Type the word "juice" (required to confuse the spammers)

Your comment


Add a comment




Excel Core Knowledge Wiki

By Zach Gemignani
September 25, 2006
Find more about: excel

We got great feedback from our readers on the recent post about core Excel skills for data analysts. A few folks asked if they could chip in and contribute to building out this knowledge. With that in mind, I put together a wiki using pbwiki ("make a free wiki as easily as a peanut butter sandwich").

For those not familiar with wikis (WICK-ees), they are a type of web site that lets visitors edit the content. Wikis allow people to work collaboratively to express the collective knowledge of the group.

We put together the Excel Core Knowledge wiki (password to make edits is "excel") to create a training resource for data analysts using Excel. Together with your input, we'd like to:

  1. Develop a short list of the most important Excel skills for data analysis. There is a huge array of Excel tips and tricks available on the Internet; we'd like to cut through this confusion by taking a stand about the things that matter most. (We have an area for "non-core" tips and tricks where we can park all the useful concepts that don't make the cut.)

  2. Gather teaching materials for each of the skills. Feel free to upload instructional files, add your thoughts on important skills, or incorporate links to valuable web sites.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.

3 comments


September 26, 2006
Henk said:

I will try to use this platform, although I like formatted texts as it structures my thoughts better than crude brain dumps. The standard approach in explainers is to take a command and explain its function, and how to use it. Hmmm, from a user perspective the order should be "what do I want to achieve" first, analyse into functional requirements and then look for the most appropriate command (then explaining, etc). This first step seems to be missing quite consistently in application documentation (not only for Excel) and may be the reason that it's so hard to learn an application with so many features. It is of course not easy to adopt such an approach, taking the user need as leading rather than pushing the application options as leading angle. But with the "lessons" serving as structuring the info it may be possible. For now, let's worry about the content and structure it later. I will just start with an example, as to test if this is of use. In this respect it's VITAL that the ones among the audience that are more on the use side than on the supply side give feedback on its useability (I am not a native-English speaker so my contributions shd be edited to begin with). I am looking forward to an interesting journey.


November 3, 2006
Alison said:

OK, I know all of these shortcuts EXCEPT the first two, and I'm going mad trying to find out about them! CTRL click WHAT? CTRL Enter WHAT? For being kind of a guru I am now feeling sooo less than. Can someone enlighten me? I'm googling and can't get up! Thanks.


November 3, 2006
Chris said:

Alison:

I can help you with one of them. If you select a range of cells then type something into one of the cells and hit Ctrl-Enter, whatever you typed gets entered into every cell in the range. This only works for straight text, not formulas. It's an excellent way to fill a big block of zeros for instance.

One of my compatriots had the Ctrl-Click on an object tip. I think what he means is if you Ctrl click on a chart for instance, that selects the chart as an "object". You then have access to different formatting commands with the object selected. For instance, you can then "Format Object" to set the exact chart sizes for a whole range of charts.

Hope this lets you get to sleep at night. Thanks for writing.

Chris

Your name

Email (optional, will not be shared)

Type the word "juice" (required to confuse the spammers)

Your comment


Add a comment




Essential Excel Skills...or What is the Core Knowledge Pack for Data Analysts?

By Zach Gemignani
September 19, 2006
Find more about: excel

We were recently invited to provide Excel training for a client—three one-hour sessions with an open-invitation to all employees. Like most organizations, they have people using Excel in a wide variety of situations (e.g. survey analysis, basic reporting, managing lists) all with different skill levels and needs. It seemed a perfect opportunity for us given: 1) our belief that Excel is the most important business intelligence tool in any organization, and 2) our interest in embracing constraints (three hours to teach all that is important in Excel?!)

Which left me with the question: What is the core Excel knowledge required to be an effective data analyst?

We spent a few days trying to boil down the myriad of tips and tricks, functions and formulas to a short list of the most essential items. Before we finished building our teaching worksheets, I wanted to post our current "lesson plan" outline to see if our readers had suggestions on what they find most valuable. Tell us what features you find most important. Point us to online resources or tutorials that will help with parts of our lessons. I'll incorporate your suggestions and in a couple of weeks we will post our Excel training spreadsheets for download.

Lesson 1: Moving and manipulating data

a. Using keyboarding to work more efficiently, e.g.

  • Accessing menu commands with ALT letters

  • CTRL (SHIFT) arrows to move through (select) data ranges

  • CTRL click to format an object

  • CTRL enter to fill an entire highlighted region

  • ALT up/down arrow to access drop-down lists

  • F2 to toggle in and out of formula and spreadsheet

  • SHIFT mouse drag, CTRL mouse drag

b. Functions for data cleaning and manipulation, e.g.

  • VLOOKUP(), MATCH()

  • Text formulas: LEFT(), RIGHT(), MID(), string concatentation,

  • Sum, Ave, Count, etc. on lower left toolbar

  • Error checking, e.g. ISERROR()

  • Measuring your data: COUNT(), COUNTA(), COUNTIF(), SUBTOTAL()

  • Working with dates

  • Delimited files

  • Find/Replace, using wildcards

  • Sorting

c. Formatting data, e.g.

  • Cells contain either functions, strings, or numbers

  • Strings: Comprised of characters; digits not recognized as numbers

  • Numbers: Can be formatted differently to represent dates, phone numbers, time, etc.

  • Functions: How functions work and finding the right one (tip: Alt + Enter to break up formulas)

  • CTRL 1 takes you to the format menu

  • Custom number formats

  • Paste special

  • Conditional formatting

Lesson 2: Working with Raw Data

a. Flat data tables

  • Why this is important and how data tables can be constraining

  • Column headers on the first row

  • Block of data, no gaps in rows or columns

  • Freeze column and row labels

b. Data filters

  • ALT-D-F-F, ensure you have the full data list

  • Advanced data filtering

  • Custom sorting

  • Pulling out unique records

c. Names

  • Named fields

  • Named ranges

  • Dynamic ranges using OFFSET() and COUNTA()

d. PivotTables

  • How to make one; what is happening behind the scenes

  • Using dynamic ranges with PivotTables

  • Table options

  • Field options

e. Trimming file size

  • Pivot table caches

  • How formulas take up space

  • CTRL end, CTRL home

Lesson 3: Presenting Data

a. Charts

b. Other data presentation tricks

c. Creating dynamic reporting with D-T-P (Data-Transform-Present)

  • Separate data, transform, and present tabs to ensure flexibility (value to planning your workbook)

  • Using a master pivot table to drive other pivot tables (via a VBA macro)

d. Preparing for sharing

  • Improve legibility (gridlines, fonts, colors)

  • Protecting cells, sheets, workbooks

  • Hiding sheets

  • Formatting input/output cells

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.

28 comments | Show all comments only the last 5 are shown


January 9, 2007
We Can Fix That with Data / Excel Training said:

[...] Juice Analytics’ Excel training worksheet is available for download. It covers their previously identified core Excel skills. [...]


March 27, 2007
I believe things for you » said:

[...] 原始链接 第一课：移动和操纵数据 [...]


July 20, 2007
Melissa C said:

Hey! I just found your blog and attempted to download the EXCEL training informaiton. Could you repost it? Thanks!


November 22, 2007
Vijay said:

The link to "Excel Training Info..." essential skills information seems to be dead. Much appreciate if you could repost the worksheet. Thanks.


December 14, 2007
Paul H said:

Vijay - just hunted down the post with a link to the training sheet:

http://www.juiceanalytics.com/writing/2007/01/excel-training-worksheet/

Hope that helps!

Your name

Email (optional, will not be shared)

Type the word "juice" (required to confuse the spammers)

Your comment


Add a comment




Too Literal with Numbers

By Zach Gemignani
September 13, 2006
Find more about: analytics presentations

Data analysts always face the same white-knuckle fear when they present: Will someone derail my presentation by questioning the source of a data point, the quality of the data, statistical significance, or why two numbers don't align?

These types of inquires may appear innocent and within the field of play. I find them counter-productive. They implicitly undermine the analyst's credibility and worse, deny the rest of the audience the opportunity to hear the full analysis narrative.

I've suffered through enough of these train wrecks to wonder about the underlying cause of this disruptive behavior. Here's my theory:

People who have experience and comfort with numbers have the ability to abstract meaning from analysis for themselves—even when numbers don't line up, the data is unclear, or the analysis has minor flaws. They can ask themselves higher-level questions: What does this mean? What are the implications for the business? How else could these results be interpreted?

In contrast, people who are uncomfortable with analytics treat numbers literally. They are disturbed by surface level inconsistencies. They expect—even need&mdas;hthe numbers to line up in straightforward ways. The medium simply isn't familiar enough to abstract their own meaning.

Combine this uncertainty with the omnipresent pressure in business to express an opinion (any opinion) to appear smart—and you end up with wasted time spend discussing superficial irregularities.

Does this sound like the rantings of a data elitist? Fair enough, but I'd suggest that a similar phenomena is common across any field of expertise. Take art, for example. People who have little exposure to fine art are bound to ask for help in explaining the underlying meaning, remark on the superficial beauty, and find themselves attracted to the most obvious reflections of reality. Meanwhile, those who have experience can gather their own meaning, think creatively about what they are seeing and understand how it fits in context with other examples.

If this notion is accurate, where does that leave us? On the one hand, it highlights the need to understand your audience and anticipate and educate those people who are inclined to be disruptive. It also hints at some of the poorly understood realities of analysis:

  • Analysis is as much art as science. Analysis relies on the perspective and skill of the analyst. Managers might want an objective reality, but they should recognize that the "truth" won't tell an actionable story.

  • Analysis should strive for directionally-correct results, not precision and comprehensiveness. Good analysis impacts decisions—therefore speed matters.

  • Give people something tangible and incontrovertible to hang onto. This is one reason we really like the bottom's-up analysis approach. When you create visualizations of individual customers, people can't argue with this granular data—and it gives them something that they can fully understand and appreciate.

  • Finally, there is nothing quite as valuable as personal credibility to make an analysis valuable. If people trust the work you have done in the past, they are likely to avoid arguing the small stuff.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.

8 comments | Show all comments only the last 5 are shown


September 16, 2006
Paul Scaer said:

Could you explain more about the "train wreck" situations that seem to be so damaging? As a teacher, I find that the opposite is the case when students do power points: people rushing to conclude something for which they have no credible evidence. Do you let your audience think about your data?


September 16, 2006
Zach said:

The train wrecks usually go down like this: Someone starts by asking if the data being presented is "statistically significant" -- 1) not knowing what this means; 2) implying that the analyst is looking to pull a fast one with some sketchy data; and 3) redirecting the discussion away from a productive discussion of the meaning and results of the analysis. This question opens the door to a series of questions about survey design, how a particular metric is calculated, and speculation about edge case exceptions. Before you know it, half the meeting is over and no one has learned anything new. To me, these are questions that are best dealt with offline (before or after the presentation) -- not when you are playing with the time of the whole audience

"Do you let your audience think about your data" feels like a leading question. I grant you, there are going to be students and even analysts in professional environments that have done sloppy work or worse, are looking to deceive with data. Perhaps the better solution is to separate these discussions: set up a time to discuss data quality and analysis methodology that is separate from discussion of results and implications.


September 25, 2006
Daniel said:

There's always the old trainer's trick of the "parking lot," an easel pad where you write down questions that are temporally inappropriate with a promise to address them before the presentation ends. Everybody gets to hear the presentation without a lot of distracting side-trips, and you get to answer when they've heard the entire presentation in context. Also, you'd be amazed at how often people decide that their questions is no longer important once the presentation is over.

DGF


September 25, 2006
Zach said:

Great point. I really like the parking lot trick.


February 25, 2007
» Truth in Advertising - Juice Analytics said:

[...] This isn’t easy, we all have people who can drive us crazy, who can derail a presentation with niggling questions or who ask for information they’ll never use. [...]

Your name

Email (optional, will not be shared)

Type the word "juice" (required to confuse the spammers)

Your comment


Add a comment




MicroCharts, A Different Take on Excel Charting

By Zach Gemignani
September 7, 2006
Find more about: excel reporting

Like an overcooked steak, reporting can be dry, bland, and not particularly easy to digest. A typical example delivers the goods in a simple table and shows trends as a percentage change from the previous month or year.

Report pre

"Sparklines" to the rescue! Edward Tufte describes sparklines as:

"data-intense, design-simple, word-sized graphics." Whereas the typical chart is designed to show as much data as possible, and is set off from the flow of text, sparklines are intended to be succinct, memorable, and located precisely where appropriate.

Check out how we can embed a tiny trending line into this same report:

Report post

Now with a quick scan, you can get a sense for the trend over the last 12 months. Bringing Sparklines from concept to reality is a Excel add-in put together by Andreas Flockermann of BonaVista Systems. His MicroCharts tool lets report-makers create these little graphics; any recipient can view the images as long as they have installed a free MicroCharts font. Here are a few example of the types of charts you can make:

Sparkline Gallery

I spent a little time playing with his beta version and found it fairly intuitive. There is a chart format dialog box that helps you set various dimension including scale, colors, and reference lines. Like all charting in Excel, it takes some playing around to really understand how to control the result.

Format Chart

Andreas says he started his new company to "help users to create better, richer reports with more information per square inch than integrates well into Microsoft technology." It is a worthy goal.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.

9 comments | Show all comments only the last 5 are shown


October 1, 2006
Zach said:

Here's a nice comparison of the two sparkline products: http://thinkingpictures.blogspot.com/2006/09/sparkline-generators-for-excel.html


October 28, 2006
Martin said:

This looks nice, the fact that recipients need to have a special font installed to view the charts would cause issues for me though.

I've tried creating some 'micro charts' in Excel 2007 using the standard chart features and it works pretty well. Just delete all the 'junk' and resize it to the size of a few cells (holding the ALT key works nicely when doing so).

It'll probably require more resources but the file will be viewable by anyone with 2007. Just a thought...


December 7, 2006
DBM Forum » Blog Archive » Tool Tip: MicroCharts en SparkMaker said:

[...] Al met al lijkt het me leuk speelgoed voor dashboard designers. Zie ook het artikel op Juice Analytics en een op Excel Geek. [...]


February 17, 2007
Jon Peltier said:

Here's another approach to sparklines by Fernando Cinquegrani:

http://www.prodomosua.eu/zips/sparklines.xls

It's pure Excel, with no need for VBA, special fonts, or any of the usual overhead associated with these techniques.


October 18, 2007
Jesper said:

As Marin pointed out it might be a drawback that users need to have a special font installed to view the charts, although there are some advantages to this approach as well.

It is fairly easy to setup a macro that creates sparklines-like graphs using the standard Excel charts. I've been testing some concepts around this (see http://www.bloggpro.com/nanocharts-enables-excel-in-cell-charts-with-sparklines-concept/)
and plan to create my own "Sparklines" add-in eventually as I like them alot for visualization.

Your name

Email (optional, will not be shared)

Type the word "juice" (required to confuse the spammers)

Your comment


Add a comment