______________________

Post from: Excel Charts Blog.

Data Visualization for Excel Users – How to Make a Chart, Part I: Asking Questions

Other

You may also be interested in:

1. Data Visualization for Excel Users: Gestalt Laws
2. The Unbearable Lightness of Pie Charts [Data Visualization for Excel Users]
3. 12 Data Visualization Questions I Have No Answers For

The original post is titled Data Visualization for Excel Users – How to Make a Chart, Part I: Asking Questions , and it came from The Excel Charts Blog .

Are they?

Let me tell you about a little experiment. I call it the scatterplot pie just for fun, and the idea is to display proportions using a scatterplot.

A traditional pie chart with two data points can be reduced to an angle:

The same message, no fat. And because there are no textures and arcs to deal with we can now superimpose many pies.

These are percentages of the age group 65+ in 1996 (left) and 2050 (right) for 220 countries (data from the US Census Bureau). This comparison clearly shows that the World is getting older.

One of the problems with pie charts is that you can compare proportions but you can’t compare wholes. In the images above we are comparing very different country sizes (Tuvalu and China?). With the scatterplot pie we can add this dimension:

China and India are not helping resolution, but it would happen to any other chart. We can focus on a detail:

Other things we could do:

• Group series (in this example, color-coding by continent would show us the significant differences between Europe and Africa);
• Add axis and circular grid lines to improve readability;
• Set line transparency to 50%;
• Remove the vertical line or make it look like a grid line;
• Label the more significant data points.

I actually like this idea and I’ll test it a bit further. I’ll try to decide if it is a good alternative to stacked bar charts. It should also work with three or more slices, but just because it works it doesn’t mean we should use it (like most chart options in Excel…).

So, what do you think? Would you use it?

This is not entirely mine: I was inspired by this comment. And I’m sure someone must have though about this first. If it rings a bell please let me know…)

______________________

Post from: Excel Charts Blog.

The best of two worlds: the scatterplot pie

Other

You may also be interested in:

1. Geo-scatterplot or the poor man’s GIS
2. Design and information visualization: two worlds apart

The original post is titled The best of two worlds: the scatterplot pie , and it came from The Excel Charts Blog .

(…)

On the top of the mountain was a plain. There stood an old stone house, and in front of the house lay a great fish-pond, but behind it was a dark forest. He saw neither men nor animals, everything was quiet; only the wind rustled among the trees, and the clouds moved by quite close above his head.

He went to the door and knocked. When he had knocked for the third time, an old woman with a brown face and red eyes opened the door. She had spectacles on her long nose, and looked sharply at him; then she asked what he wanted. “Entrance, food, and a bed for the night,” replied the drummer. “That thou shalt have,” said the old woman, “if thou wilt perform three services in return.” “Why not?” he answered, “I am not afraid of any kind of work, however hard it may be.”

The old woman let him go in, and gave him some food and a good bed at night. The next morning when he had had his sleep out, she took a thimble from her wrinkled finger, reached it to the drummer, and said, “Go to work now, and empty out the pond with this thimble; but thou must have it done before night, and must have sought out all the fishes which are in the water and laid them side by side, according to their kind and size.” “That is strange work,” said the drummer, but he went to the pond, and began to empty it.

He bailed the whole morning; but what can any one do to a great lake with a thimble, even if he were to bail for a thousand years? When it was noon, he thought, “It is all useless, and whether I work or not it will come to the same thing.” So he gave it up and sat down.

(…)

Stay here, said the maiden, I will help you out of your difficulty. You are tired, lay your head in my lap, and sleep. When you awake again, your work will be done. The drummer did not need to be told that twice. As soon as his eyes were shut, she turned a wishing-ring and said, rise, water. Fishes, come out. Instantly the water rose on high like a white mist, and moved away with the other clouds, and the fishes sprang on the shore and laid themselves side by side each according to his size and kind. When the drummer awoke, he saw with amazement that all was done.

(…)

This was one of my favorite Brothers Grimm bedtime stories when I was a kid. I still like it. And the drummer looks a lot like you and me. Like him, we have our great fish-ponds of data. Like him, we have clueless managers, impossible deadlines, the wrong tools and the wrong training. We try hard and sometimes we fail. And yes, I like to see data visualization as a beautiful maiden that will help me seek out all the fishes in my pounds of data.

nite-nite.

______________________

Post from: Excel Charts Blog.

The drummer – a data visualization bedtime story

Other

You may also be interested in:

1. Stephen Few, Data Visualization, Eye Candy and the Pie
2. Data Visualization: Who Needs a Reality Check?
3. Sub-Prime Charts: Should Data Visualization Be Boring?

The original post is titled The drummer – a data visualization bedtime story , and it came from The Excel Charts Blog .

Enjoy and comment, please…

______________________

Post from: Excel Charts Blog.

The Unbearable Lightness of Pie Charts [Data Visualization for Excel Users]

Other

You may also be interested in:

1. Data Visualization for Excel Users: Gestalt Laws
2. A course for the invisible data visualization users
3. Will you Help Me Write a Data Visualization E-Book (and Win a Free Copy)?

The original post is titled The Unbearable Lightness of Pie Charts [Data Visualization for Excel Users] , and it came from The Excel Charts Blog .

I started writing my Data Visualization for Excel Users series because I have an unhealthy number of open questions. I’d like to go beyond it depends but there are no right answers, only my answers (and, with your help, our answers). These are practical questions (from a business user perspective) that, once answered, will surely trigger new ones. Here is a sample, with a few starting points:

1. Reason or Emotion?

This is the big one. We’ve been discussing reason vs. emotion over the last 2,500 years, and we still disagree. The right answer would be reason and emotion (easier said than done). When applied to data visualization, this translates into charts that respect the data, attracts readers attention because they are beautiful and keeps it because they are interesting and insightful. And contain no cheap emotional tricks. This filters out 99,9% of all  infographics published nowadays.

2. Pies or No Pies?

No self-respecting data visualization expert likes pie charts. And there seems to be a daily contest to find the “worst pie chart ever”. And people keep making them, relentlessly… Why?

3. Are there interchangeable charts?

Everyone tells you that you should use bar charts instead of pies. Well, I think you should. Not because pie charts are bad, but because you’ll have to ask better questions. But that’s not the point. The point is that there are no interchangeable charts. Different charts tell us different stories. You cannot tell a part-of-a-whole story with a bar chart. You have to have a pie.

4. Should I use logarithmic scales?

I’m still trying to find a simple way to implement a logarithmic perspective in my eyes… Logarithmic scales are important to look at growth from a different perspective, but if you can’t find a way to tell the readers how to read the chart you should consider not to use them.

5. Should origin start at zero?

Yes, by default. If you need to improve resolution set the scale to 20% below minimum and 20% above maximum values . Don’t do it with bar charts (origin should always be set to zero).

I would consider this rule: to improve resolution you must have more than one series (see this example). In a line chart, comparing slopes is often more interesting than the slope itself.

6. What kind of data visualization skills the information worker needs?

Above all, know your business. Learn how to use a database (table structures, basic SQL). Refresh what you know about descriptive statistics. Choose a tool and be an advanced user or hire someone who is. Learn the basics on how human perception works. Understand how perception impacts design choices and the other ways around. Learn what each chart type is used for. Find your charting style. Spread this knowledge across your organization. Know your business (again).

7. What is the role of design in data visualization?

We saw that a chart is a visual representation of distances between data points. Everything else is design. The first role of design in data visualization is to improve cognition. The second role is to provide aesthetic consistency. The third role is to grab users attention.

8. How to design a dashboard?

I think we owe Tufte and Bertin a consistent approach to data visualization, at the chart level. We still need something similar for dashboard design.

9. How to sell data visualization?

Find a sponsor and make a lot of bad chart/(good chart comparisons. Compress a 100-slide presentation into 50 slides. They will get it, sooner or later.

10. Should I use animation?

You probably shouldn’t. Try small multiples first. Try animation if you have too many series, if the animation defines clear patterns and if it is consistent with the law of common fate. But remember: are no interchangeable charts.

11. Do I need to code?

No, but you should at least be able to understand what a recorded macro is and how to improve it with simple changes.

12. What tools should I use?

Learn everything in Excel then switch to Tableau/Qlikview/Spotfire.

How would you respond to these questions? Please share your thoughts in the comments below…

______________________

Post from: Excel Charts Blog.

12 Data Visualization Questions I Have No Answers For

Other

You may also be interested in:

1. You want answers, but do you have questions?
2. Information visualization: frequently asked questions
3. Data Visualization Hierarchy of Needs

The original post is titled 12 Data Visualization Questions I Have No Answers For , and it came from The Excel Charts Blog .

______________________

Post from: Excel Charts Blog.

Data Visualization for Excel Users: Gestalt Laws

Other

You may also be interested in:

1. The Unbearable Lightness of Pie Charts [Data Visualization for Excel Users]
2. A course for the invisible data visualization users
3. 12 Data Visualization Questions I Have No Answers For

The original post is titled Data Visualization for Excel Users: Gestalt Laws , and it came from The Excel Charts Blog .

souce: Skinnyo.com.

Here is an interesting technique: using two y-axis to display the same data at different resolutions. Yellow (BMI) and blue (Weight) lines should overlap (there is a one-to-one correspondence between BMI and Weight for a given height), but they don’t because they are using a higher resolution for Y2. That’s clever. We can choose to see more detail (BMI) or less detail (kg).

Unless…

Unless they don’t know what they are doing. There are many reasons why you shouldn’t use a secondary y-axis in your charts and this chart proves it. You can use the Y2 axis:

• to replicate Y1 (it helps reading the chart);
• to show one-to-one correspondences (Celsius/Fahrenheit, km/miles)
• Everything else is potentially manipulative.

You must make sure those correspondences are preserved. That’s not the case here: 26.5 is not the right BMI for 74 kg (for this height). If I remove the green line, for the same BMI I get 82.3 kg. So, different correspondences for different resolutions. Hence the title of this post.

I wanted to focus on this, but there are other  issues (terrible smoothing algorithm; is the green line (Goal)  assigned to Y1 or Y2? You can never know).

How do you use the secondary y-axis?

______________________

Post from: Excel Charts Blog.

How many meters in a mile? Depends on resolution

Other

No related posts.

The original post is titled How many meters in a mile? Depends on resolution , and it came from The Excel Charts Blog .

Then we have the highly visible data visualization: the sexy kingdom of infographics, where marketeers, bloggers and graphic designers fight for a scarce good: your attention.

I have nothing much to had to the visible side. There are many good books by graphic designers for graphic designers. I own several of them and they are both beautiful and useful.

What about all those on the invisible side? They have different needs, different skills, different practices. Shouldn’t there be books for them too? Or are the publishers too busy with yet another Excel handbook?

You can always rely on Stephen Few’s books, but I believe we need something for Excel. Because everyone uses or have access to Excel. And, I’ll say it again, 90% of all corporate users need to know about data visualization can be practiced using Excel. Before switching for a better tool…

So, what can I do about it?

My New Year’s resolution for 2012 is to make this blog a consistent and structured data visualization resource for corporate users. I’ve outlined a “book” that I’ll be publishing here using pages, not posts (this allows for better structure). I’ll publish those pages more or less randomly but, like pieces in a jigsaw puzzle, structure is there, and each piece reveals a little more.

When can we start?

Right now. Here is the first page: What is a chart?

______________________

Post from: Excel Charts Blog.

A course for the invisible data visualization users

Other

You may also be interested in:

1. The Unbearable Lightness of Pie Charts [Data Visualization for Excel Users]
2. Data Visualization for Excel Users: Gestalt Laws
3. Data Visualization Bimbos

The original post is titled A course for the invisible data visualization users , and it came from The Excel Charts Blog .

Is this person a friend of yours? If you are a photographer, what do you think about this photo? If you’re a doctor, what can you tell us about this man’s health? If you are a marketer, do you think he’ll buy your product?

Now, let me ask you this: if you know nothing about photography, can you tell us if this is a good photo? That’s harder, isn’t it? You can tell us how it makes you feel, but that’s not enough.

We all are experts at something. And being an expert is just a different way of saying that we are better than most people at spotting patterns and taking them into account in a research or a decision-making process. The more complex patterns you can identify, the more knowledgeable you are. To some extend, knowledge-building equals pattern-and-context-discovery.

I see a pattern in this ECG, but I’m not a doctor. This person could be dying, and I can’t see it. A meaning may be obvious for you, but not for me. Help me. Forget silly decorations. Add context patterns and I’ll make the connections and we can have an epiphany together.

Epiphany. That’s the name of the game in data visualization, not “wow”. Remember that.

(OK, “wow, an epiphany” is also acceptable…)

______________________

Post from: Excel Charts Blog.

Patterns of Knowledge

Other

No related posts.

The original post is titled Patterns of Knowledge , and it came from The Excel Charts Blog .

There are several objects in Excel you can make a map with. If you want to use shapes, you can follow my my tutorial. You can also use a scatter plot. The map above uses cells and conditional formatting.

So, here is how to do it (this example used population data):

1. Get a table from the US Census Bureau with latitude, longitude and population data by county.
2. In a new sheet, calculate longitude intervals and use them as column headers (I’m using a 0.25 interval: lower means more resolution but more columns);
3. Calculate latitude intervals and use them as row headers;
4. With the SUMPRODUCT() function calculate total population, total area and density for each latitude and longitude class;
5. Set column width to 5 or 6 pixels and do the same with row height;
6. Select the entire table and add conditional formatting as you like;

Since you can’t add visible text to such small cells, you can merge them and add titles, legend, source, etc. A better option is to enter them in a separate sheet and use the camera tool to add an image to the map.

When you select a cell a small macro identifies the county and displays the data in the table below. At this resolution level, a cell usually contains a single county, but in densely populated areas there may be more than one county in a cell, as shown in the table (I’m also using the camera tool to display the table below the map).

I actually prefer to use a scatter plot but this is fun, and you can always ask your mother to cross stitch the map into your pillow!

______________________

Post from: Excel Charts Blog.

Making maps in Excel: cross-stitching edition

Other

You may also be interested in:

1. Performance vs flexibility in Excel: Demographic Dashboard Lookup Edition
2. A Secret Message Hidden in an Excel Chart

The original post is titled Making maps in Excel: cross-stitching edition , and it came from The Excel Charts Blog .