A data visualization book for the… ugh… people

A chart is an interpretation of the data (and, by extension, of reality). In a broad sense, they all lie, just like most other forms of communication. But charts are a relatively new form, and still have an aura of objectivity and truth. Which means they have a lot of potential for deceiving in the hands of a masterful practitioner. Cairo is of the most well-known and brightest members of the data visualization communities. He could easily write the ultimate book on #dataviz potions and how to actually make them. Instead, he lost his time (and potentially huge sales) writing a dada book (D.A.D.A., as you know, is the silly notion that there can be a Defense Against the Dark Arts)

The prologue was promising, but when he writes “Good charts make us smarter” I start suspecting that he actually means it. The notion that this was a lost opportunity creeps in with each turn of the page. Cairo goes to excruciating detail explaining each concept, exposing each lie and providing context (like pointing out that some “lies” are in fact a visual expression of our psychological biases, some of which we are not even aware of). He even discusses ethics, which adds to the overall nuisance.

Nothing new to see here. Trust me.

So, nothing here is very new to the practitioner of the dark arts in data visualization. If you want to improve your skills for deceiving you should look elsewhere. The book does provide some clues on your opponent’s strengths, so I wouldn’t dismiss it completely. Other than that, it’s a waste of time.

OK, I concede that this is an excellent resource if you are mostly a consumer of data. This book was written specifically for you. Lucky you. It will gently make you aware that charts are meant to be read, not just seen. Although a chart can indeed help us learn more and faster, to take advantage of them you need to keep your internal lie detector turned on, and this book will give it a boost by showing how charts lie. And make my life a bit more difficult.

Hi! Let me just whisper a few words while my dark self is busy getting ready for Halloween: don’t pay attention to him. Knowing how to read a chart and being able to evaluate its trustworthiness are becoming citizenship life skills, not only professional skills. This book is accessible, well-written and provides you with good thinking tools to help you navigate this growing volume of visual data. Highly recommended, of course.

The original post is titled Alberto Cairo’s How Charts Lie: an Alt-Disappointed Book Review , and it came from The Excel Charts Blog .

I’ll exemplify with a simple data set. Here is a list of counties by state in the US (the data source), then the number of counties by state and finally the states sorted by number of counties:

I couldn’t use a pivot table, so I had to count them with the COUNTIF() function. Then the states are sorted by the number of counties. Here is the formula I’m using, a typical COUNTIF() function:

=COUNTIF($B$6:$B$3104,$D6)

The problem with Excel COUNTIF()

So far so good. But you know that, to follow best practices, you should keep the data source in a separate sheet, right? So, you point to the data source in the other sheet and point to the criteria in the current sheet.

=COUNTIF(Sheet2!$A$6:$B$3104,Sheet1!J6) 

Let’s see what happens:

Excel correctly counts the number of counties, but when you sort the states by number of counties but the list becomes utterly messed up. Let me show you how the formula looks like for Texas, currently in row 6:

=COUNTIF(Sheet2!$A$6:$B$3104,Sheet1!M49)

It points not to the the row Texas is in now, but to where it was before sorting (M49, instead of cell M6). I have no idea why Excel behaves like this, but it’s annoying and the error is easily overlooked.

Quick fix, but symptom of deeper issue

You can solve the problem by removing the reference to the current sheet (we don’t need it) before sorting the data. That’s a quick fix, but try to see beyond it. I suspect this is a common mistake and corroborates my long-held belief that you should avoid formulas in Excel whenever possible. When you can’t avoid them make them more resilient to Mondays.

The optimal solution is to use a pivot table to count the counties and sort the states. If you need the COUNTIF () function, turn both the data source and the analysis into tables first. When using it inside a table is much, much safer. Here is how COUNTIF() looks like in the unsorted table:

=COUNTIF(Table1,[@State])

And here is how it looks like after the table is sorted:

=COUNTIF(Table1,[@State])

No difference, and that’s exactly the point! And here are the tables:

As you can see, the formula is not changed when you sort the table, so now you can stop worrying about Excel returning the wrong results, or even worse, not being aware that could happen.

Takeaway

Whatever you do, your first step should be turning these two ranges (data source and client) into tables. Stop everything and do it now. A range that looks like a table and is expected to behave like a table should always be a table. Virtually no exceptions.

This post focus on an annoying issue, but I’d like you to go beyond that and see it the solution as an example of a more structured approach to data analysis and manipulation in Excel.

The original post is titled Excel: sort + COUNTIF() = utter mess , and it came from The Excel Charts Blog .

But many charts can be done in Excel using a scatter plot, and making a passable version of a horizon chart is no exception. Can you figure it out? I have been trying to make a reasonably flexible chart without VBA and today I’m finally adding it as a bonus file to my Wordless Instructions packages (no wordless instructions for this chart yet).

The advantage of creating a horizon chart in a single chart instead of multiple charts is that you have much more control over sorting, synchronizing and making the design consistent. I do prefer making horizon charts with area charts instead of bar charts, but I’m not sure if that’s possible.

So, here are a few examples. Monthly employment rate by US state compared to the US rate:

You can switch between names and codes to make more room for the chart.

It’s easy to switch between sorting keys:

Playing with bin width:

I like the idea of mixing the horizon chart with the cycle plot. It’s possible to do it but needs more work and better data (maybe there is a hidden image in this stereogram):

No significant editing needed when pasting new data (it adapts to a different number of entities):

The current options and legend:

It gets slow with more data (with 500 months x 50 states x 12 classes it becomes really slow, but the computer I’m using is slow for everything Excel). Other than that, I’m very pleased with the results. Area charts are more pleasing to the eye, but I was able to show that some interactivity is possible, something you can’t do with multiple individual charts. And not having to use VBA is great.

This works with Excel 365 and probably Excel 2013 for Windows. I don’t think it works on a Mac.

I promised to add a few bonus files to the Wordless Instructions packages, and this is the first one. There are no wordless instructions for this chart yet, because I genuinely don’t know how detailed these instructions need to be. So, any feedback is welcome!

P.S. Last week, on my flight to the JMP Summit in Copenhagen I used the same table and could make the horizon chart in a few minutes. Haven’t tested it with Tableau yet, but I assume it should be as easy. Conclusions: how you structure your data matters; Excel is a pain when it comes to small multiples.

I should try it in PowerBI.

Kidding.

The original post is titled Horizon charts in Excel [bonus file] , and it came from The Excel Charts Blog .

And I was like, oh God, this is so easy it hurts. But I kept it to myself.

I am now more or less familiar with the interface of both Tableau and PowerBI, but I still need many hours to understand what makes them powerful (the languages behind calculations and metrics). I don’t know enough about the tools, but I tend to know exactly what type of visualization I want, and why. This helps.

The official truth

According to the Microsoft marketing machine, PowerBI is much better than Tableau on  the data side, and it is as good as Tableau on the visual side. The former is probably true, or will be in the near future. The latter is not, and it’s hard to imagine an alternative reality where that happens.

Let me be clear here. I don’t dismiss the data side. We need products that make connections, data preparation or statistical analysis easier and faster. Data and visuals complement each other and in most projects they share a common purpose: finding and communicating insights. Also, I’m sure in many cases PowerBI will fit the requirements perfectly.

The scenario

Here is the scenario: you need to display the distribution of a few hundred or thousand data points. You do have an aggregate view, but you’ll need to check for outliers, select data points that display an interesting behavior, etc. Perhaps it makes sense to split the distribution by the categories of a meaningful variable.

A business analyst could use this display to analyse sales territories or store performance. I don’t have business data that fits the bill, so I’m going to use population data instead. I’ll use population density in European countries at a regional level (NUTS3). A second chart splits the distribution by country. If you want to play with the data you can download the file here (xls).

The visual

This is a fairly straightforward chart. You use a scatterplot with a constant y and plot the data along the x-axis. Can’t be simpler.

There can be an issue, though. When you have many data points you risk overlap. To minimize the risk, you can use different markers, smaller markers, transparency, or add a bit of noise to the values on the y-axis, so that data points don’t overlap on the vertical scale (this is called jittering). There are other techniques to reduce overlap, like shorter scale range, increase chart width or use log scales.

Assume this is a proof-of-concept chart that would need a bit more design time.

Benchmark: the Excel version

Let’s start with an Excel version. I added a first series with a single y value, a second series where the value of y is the rank of population of each country, and a dummy series to display country codes. 

The most efficient marker to display data along the x-axis is a vertical line. Why most applications don’t offer this option of marker I beyond my understanding. Anyway, in Excel, you can use an image for a marker, so import a picture of 1×10 pixels and there you go, a vertical line. 

I prefer a a more flexible approach. Instead of a picture, I use error bars. Here is the result:

The Tableau version

Since Tableau doesn’t include a vertical marker, I imported a custom shape. No big deal. Next, I set opacity at 20% and used a log scale. Here is the result when all data points are plotted along a single line: 

I’m pretty happy with the chart. It’s easy to see the most common range of population density values, but there are variations that only the combination of transparency and the marker’s small graphical footprint allow me to see.

What if you wanted to split the distribution by country? Simple: You just need to drag the right field and it’s done. For this second chart I increase opacity a bit, since overlap is less problematic.

The PowerBI version

Let’s see if we can get the same results with PowerBI. 

I guess not. I can’t import a custom marker, the available markers are gigantic for this task, and there is no transparency. Removing fill color improves a bit, but clearly not enough. The result is a continuous black line that hides relevant detail.

Chart height is also a problem. This is the minimum before the axis title gets hidden. As you can see, the Tableau design is much more compact. This can potentially impact display on small screens. It’s also interesting to note that Tableau does a better job at defining and describing the scale. 

Now, what about the chart detailing the distribution by country? Here is the best I could come up with:

This is not one chart but two: I used a hidden bar chart to display country codes.

The process is slightly unintuitive, and before you can come up with something you get this message or similar a few hundred times:

I will not try to make sense of what I feel is a conceptual mess around “details”, “categorical” vs “continuous” and “Don’t summarize”. I do need to emphasize the most basic conceptual error: assuming that the x-axis and the y-axis have a different nature. This is a wrong assumption and now you’re basically doomed.

The Key Influencers visual

Some people noted that the new Key Influencers visual demonstrates how great the PowerBI visuals are. No. Actually it demonstrates the opposite. I’ll accept that the analysis behind the visual is basically magic. This makes the visual even sillier and unworthy of all that magic. It also shows that Microsoft is much better at the data side than at the visual side.

The custom visuals marketplace

Call me old-fashioned, but I believe a “self-service BI” tool should not have what feels like a half-baked chart engine. Not that PowerBI should have all the options for every imaginable chart, but the basic ideas should be right, and some consistency wouldn’t hurt, either (example: color option means transparency option, always.)

Microsoft recently decided to allow developers to monetize their custom visuals in the marketplace. That’s great, and I hope this improves the overall quality of the custom visuals. I know there are a few excellent premium visuals. If you expect to need more than a simple bar or pie chart, I advise you to factor in these extra costs.

Takeaways

If you believe the built-in visuals in PowerBI are comparable to the ones found in Tableau, choose the option that best applies to you: 1) you’re too gullible and should search for resources beyond the Microsoft marketing machine; 2) you need to increase your data visualization literacy; 3) your needs are simple and you don’t need more than this (and that’s OK!).

The whole point of this exercise is to show that the built-in visuals and the visualization process in Tableau and PowerBI are fundamentally different. Tableau demonstrates a more consistent conceptual framework and that can be seen in practice, while PowerBI visuals are messy and outdated.

That said, it’s possible that the data side in PowerBI is in fact stronger that Tableau’s. If that is true, and you need that, having someone evaluating and, if needed, developing custom visuals could be your best bet.

It’s possible that I’m terribly wrong. Help me see the truth, but please don’t start with “use the custom visuals or R”, because that’s not the point.

The original post is titled Comparing Tableau and PowerBI visuals , and it came from The Excel Charts Blog .

Not Paris’ fault. This was the second leg of a trip that started in Prague, and I was still in a process of digesting the city’s overwhelming beauty. After a couple of days, I was able to enjoy Paris, not in full, but in what made it different from Prague. For full disclosure, I prefer a different kind of beauty I find in other places, like, you know, London.

I hope you’re starting to suspect that this has nothing to do with cities and tourist trips. It has everything to do with data visualization and the multiple approaches to it.

I was reading Why visual literacy is essential to good data visualization, an interesting article by Benjamin Cooley (Twitter handle @bendoesdata) where he discusses the difference between visual literacy and data literacy and why it matters. I was calmly nodding along, but then I reached this paragraph, which marks an unexpected turn of events:

Data literacy is an essential starting point. But let’s be real: most people don’t want to look at a page full of bar charts. With more data available than ever before, the world is suffering from insight-fatigue, staring at dashboards day after day of red and green indicators showing positive and negative performance.

It is followed by a stock photo captioned “Just look at this generic business guy. He’s been staring at 3d pie charts and bar graphs all day”. As a generic business user, I recognize myself in this picture as much as a graphic designer would, if it pictured a generic graphic user tired of staring at automated infographics all day.

What’s the cure for the poor generic business user? Color theory, “feelings of certain shapes”, reading patterns of web users. In other words, we need more beauty.

The Invisible – Visible continuum

If using information to support decision-making is how I get my job done, all I want is insights, not pretty pictures (Ben Shneiderman). I don’t understand this notion of “insight-fatigue”, unless it means a more general burn out consequence of badly designed dashboards.

I agree that probably most people don’t want to look at a page full of bar charts. But the answer is not finding something less boring than bar charts. The answer is to create visuals that get out of the way. In other words, make them invisible (that’s what good design is all about, so they say?).

Benjamin mentions a Truth – Beauty continuum. I don’t see it as a continuum, because beauty (aesthetics) is always there, and truth should always be there.

That’s why I would prefer, instead of a Truth – Beauty continuum, an Invisible – Visible continuum. A great dashboard should be invisible. This means removing barriers to getting the insights I need, and that includes the dashboard not calling attention for itself, for good or bad reasons. I don’t care if it is a screen filled with bar charts or some well-chosen xenographics.

Now, I have nothing against a dashboard or an infographic that was designed specifically to be aesthetically pleasing. Designing for the visible side of the spectrum increases attention and engagement, and make the object more memorable. And makes you the cool kid. There are some trade.offs, though. If you search for “insights fatigue” I’m not sure if you’ll find a lot of relevant results, but search for “beauty fatigue” and you’ll get a lot to choose from. My Prague – Paris trip was an obvious case. I also mentioned London to express the idea that beauty can be overwhelming, but the sense of beauty is not universal: a stronger attraction is coupled with stronger rejection.

There are, of course, more earthly matters: beautiful objects tend to be unique, and that means they cost more. Since this is business, I need a cost-benefit analysis: how much this beautiful dashboard will cost me to implement, maintain and update? If it uses non-conventional visuals, it means that users will need extra training. And will they be able to get better insights and faster?

So, I don’t think beauty is the answer. It certainly is one of many possible situation-dependent answers along the the full Invisible-Visible spectrum. And we should humbly recognize that our talents and skill set will not always fit the requirements. That’s OK. Believe me, I know. I’m a generic Excel user.

Photo by Benh LIEU SONG, Wikicommons

The original post is titled Data visualization: beautiful Paris? , and it came from The Excel Charts Blog .

The Data

Let’s start with the data. Most tools draw a clear distinction between data sources and data display. In Excel, this distinction is often blurred because users don’t follow basic best practices. This is the first of several (more than seven, I’m sure) deadly sins Excel users often commit: badly structured data.

There is nothing terribly wrong about the table above. But it is designed to display data, so it should never be used as a data source for making charts, because it will severely limit your freedom to explore the data, and increases update and maintenance costs. This is the first case of badly structured data.

Also, Excel allows you to enter the data wherever you want in a spreadsheet, or even in multiple spreadsheets. If you know what you are doing, that can be immensely useful. If you don’t, you’re going to find yourself in some form of spreadsheet hell.

In Tableau, the distinction between a) data sources, and b) the objects (display tables, charts) that use them is clear. Also, Tableau is a lot less forgiving when it comes to data structures.

Let’s get back to the first table. To add more data you’ll probably add more columns, and you’ll have to recreate the charts, adding the individual data series. In Tableau, the table structure (number of columns) will not change, which makes data exploration much easier. (This is not Tableau specific: you can, and should, use this table structure in Excel.)

Contrary to a popular believe among Excel users, this is not a detail. The flexibility you love in Excel sooner or later will turn against you, while hard-to-read tables will prove very flexible when exploring the data. Try to add multiple years and regions to the first table and watch how quickly it gets out of hand.

Encoding in Tableau

Encoding means that you associate a data point with a visual object (a bar, for example) and one or more of its properties (height). Because you do that for all data points in a field / series, you can compare bar heights, which is much easier than comparing the actual values in the table.

In Tableau, these visual objects are called marks. You let Tableau select a mark for you (depending how you structure your chart) or, preferably, you select the mark yourself.

Think of marks as words: even if they are similar they don’t mean exactly the same. Choosing different words will change your message and make it more or less effective. For the same data, bars will focus attention on pairwise comparisons, while lines will emphasize the overall pattern. This is an editorial choice, not something you should let the computer decide.

Now, suppose you decide (wisely) that bars are not the right mark/chart type, because change between 1986 and 2050 is not clear. Perhaps lines are better, or areas, or…  In Tableau, since you already have a structure, you simply switch marks until you find one that feels right.

Now that you selected a mark, the next step is to assign data to some of its properties. You do that by dragging fields into “shelves”, at the top and on the left (I’m oversimplifying).

The most important property is Position (along the x and the y axes). That’s what the shelves at the top are used for. Other properties, like color or size, are available on the left. Here is how the total population profile looks like in 1986:

So, for each data point you have two coordinates: a quantitative one (total population) varying along the x-axis (“Columns”), and a categorical coordinate (age group) varying along the y-axis (“Rows”)

Encoding in Excel

In Excel, things are more complex. First, it uses this outdated concept of “chart types”. They are OK for a casual conversation but not for such a massive tool like Excel.

The Excel charts library is a mess of visual objects and properties. What’s the point of having columns and bars, each with the same sub-types, when “Direction” could be defined as a property? This window is the Excel equivalent of Tableau shelves (never consciously realized Excel uses the concept of “Legend Entries” for series):

Because of this mess, when you click Add, you get one of three different windows, depending on the chart type: the X-Y window, for scatter plots, the X-Y-Size window, for bubble charts, and a third version for all the rest (I think):

Because Excel uses these “special cases” (and not a generic X-Y structure), it’s easy to find examples that don’t fit them. For example, there is no concept of vertical area chart. If you want to make a vertical line chart, sorry, but that’s also not possible, unless you use a connected scatter plot. But, because the y-axis needs to be numeric, in our case we must transform the age groups into a quantitative scale. We can do that by taking the lower limit of each age group (0, 5, 10…). Note that, while I was able to add copies of the same data to the top shelf in Tableau, in Excel the charts are independent of each other:

Design Data

Design data are data you add to your chart to help you achieve certain visual effects, improving the display of the real data. A good example of display data is jittering, whereby you add a small amount of random data to minimize point overlap.

I don’t like the traditional design of a population pyramid. Both sexes should be displayed to the right side of the y-axis, not male to the left and female to the right. It would be easier to compare them without breaking the axis logic (there is a positive and a negative side). But let’s assume it’s OK to display the series Male to the left side of the axis. How do you do that in Excel? I wanted to use two axis and reverse one of them and align them at the origin. So far, no luck. The alternative is to use a stacked bar with a fake first series that must remain hidden. The second option is to multiply the data for -1. Obviously you have to take care of the scale, because it is wrong in both of these charts. I did that in the third example below.

In Tableau, you don’t have to mess with the data. You simply select the series and reverse the scale:

Let’s see an example a bit more complex. Suppose you want your population pyramids to take the form of a lollipop plot (I’m not saying it’s a good idea). In Excel, you simply add error bars to a scatter plot:

In Tableau, you add each series twice, use the bar and circle marks and, very important, you need to set each horizontal scale to Dual Axis and Synchronize them. The result is similar.

The Dual Axis seems to be the most widely used technique to create visual effects in Tableau. Combine it with Sets, and the other visual properties and you will have more control over the visual details of your chart. But would you be able to replicate one of Playfair’s charts like this one I did with Excel? At the moment, I can’t do it with my limited Tableau skills. Can you?

Small multiples

So far, we’ve being playing with a single population. What if we want to compare two or more populations? We can use a single chart or create multiple identical charts, a technique called “small multiples”. In Excel, to add them all to a single chart, you keep adding series, one for each year/sex. Excel doesn’t offer the option to create small multiples, so you have to create an independent chart for each population, and then you have to make sure they are properly aligned and have identical scales.

In Tableau, we’ve being using all the data already, but applying a filter to include the year of 1986 only. To see both populations we simply have to remove the filter and drag the dimension Year to the Color property.

And if you want to create the small multiples you also add the dimension Year to the top shelf:

This doesn’t seem like much. What’s the problem of making two identical chart in Excel, right? Thing is, most of the time we don’t make two charts, we make dozens of them (US states, EU countries…). In Excel, you’ll have to manually assign the right series to each one of them. If you have 50 charts and missed a small detail, you have to change them all. What if you want to explore other dimensions? In Tableau it’s a matter of drag and drop a field, but in Excel you basically start from scratch.

Formatting

The way each tool manages chart formatting is of little interest, as far as I can see. Yes, there are differences, but they are to be expected. Much more important is the starting point and the amount of work ahead of you.

I like simple charts. This means that, when making charts, you have to spend time removing junk, reducing and changing useful objects like grid lines, and adding objects that can help reading the chart, like annotations. Remove, reduce, change and add: these are the four dimensions of my definition of simplicity.

Vendors should strive for simplicity when defining defaults. The other day we were discussing them on Twitter. That’s one of Xan’s responsibilities at JMP:

As a #dataviz vendor I’m always interested in recommendations for defaults. There are so many choices. Lately revisiting default axis scales (number of ticks, scale margins, …).— Xan Gregg (@xangregg) January 9, 2019

Most people don’t change defaults. That’s why they are so important. Unlike JMP or Tableau, Excel defaults emphasize glitter over simplicity and effectiveness. While recent versions are cleaner, Excel defaults still require too much work and time wasted on removing things that shouldn’t be there in the first place. Defaults in Tableau are not perfect, but there are fewer things to remove, reduce or change if you value simplicity and effectiveness over glitter.

I don thing Tableau goes too far from time to time and feels a bit nanny: taken literally, some options shouldn’t be used, but they can also be used creatively (in a good way).

Takeaways

While Tableau is a data visualization tool, Excel is a multi-purpose tool for all your numeric needs at the office. This distinction is often neglected, but it will define the whole chart-making experience. I wrote in my book that much of you need to learn about data visualization can be learned and practiced using Excel, but at some point you’ll out-grow it, and switch to a more specialized application.

Data visualization is a visual language. The way it is implemented in Tableau is far cleaner and consistent than in Excel. If you are a long-term Excel user, a few things will make you scratch your head, because it’s a completely different approach to data visualization. But they will soon make sense (except for the absence of error bars, that still puzzles me.)

I couldn’t find a simple way of adding strip plots to both x and y axis in a Tableau scatter plot, something that you can easily do in Excel. Is this a natural limit of the Dual Axis model? Can it be circumvented? Hope someone from the Tableau community can tell me that no, this is not a limitation and yes, there is a workaround.

If I had to choose one of the tools to make a single chart I would probably choose Excel (“probably” means that I can’t control for skill level, so I can’t have a definitive answer now). I believe Excel offers much more flexibility and control over small details. For everything else I would choose Tableau.

In my previous post, I wrote about creating wordless instructions to make charts in Tableau, and one of the things I was not expecting was to feel that some charts, which are actually complex to make in Excel, takes you seconds to make in Tableau. I suspect these charts are here to seduce you: the real work will need complex calculated fields to overcome the limitations of the basic model.

When learning a new tool, you are not a tabula rasa where new knowledge will be engraved. You bring with you expectations, knowledge and routines from other tools. If you use these tools to solve similar problems, abstracting from the how-to and focusing on tool-independent tasks should reduce the learning curve. When making charts, selecting the data, translating the data into visual objects and defining their properties are common tasks that you’ll have to perform in Excel, Tableau or any other tool, so try to see what fits where.

Don’t reduce the differences between Excel and Tableau to functionalities, features or UX: They see the world from different perspectives, and you’ll have to understand them both. If you do, you’ll be happier.

If you liked this post, please consider sharing it using the buttons below. If you own my book, please spend a few minutes writing a review. And you could also check if my most recent project, Wordless Instructions, fits your needs.

The original post is titled Excel user’s guide to make charts in Tableau , and it came from The Excel Charts Blog .

How to make a chart

To make a chart, you must select the data, encode the data into visual objects, format those objects, and add text (title, labels, annotations). When using point-and-click applications these tasks are essentially visual: select from menu items, click, drag and drop icons. So, when writing instructions for making charts using these tools, there is no reason not to use a visual language. If it works for traffic rules or IKEA’s assembly instructions, surely it can handle making charts! Also, using wordless instructions means that we can break much of the language barrier (potentially reaching a much wider audience), and unify instructions (to some extent) across tools.

My first wordless instructions experiment: Excel

In my previous post, I show that it’s possible to create wordless visual instructions to help making charts in Excel. I defined a structure shared across instructions pages: one chart per page, and up to eight cards per chart displaying intermediate steps. The data set (or a sample) is also displayed, as well as the formulas for calculated fields. The example below shows how to make a doughnut chart.

I’m not assuming these instructions can be read as easily as a plain English sentence. Many icons are self-explanatory, the meaning of others can be derived from context and some others must be learned. This handy multi-language dictionary (in English, Portuguese, French, and Spanish) can help you with that.

These instructions are not limited to making basic charts. Some of the 50 charts below are fairly complex for Excel, and they can all be made following wordless instructions.

Going further: wordless instructions for Tableau

I’m very familiar with Excel, so understanding these instructions isn’t hard for me, but it was encouraging to see how easily my test subjects (my 13yo kids) were able to follow them as well. Having somewhat proven that creating visual instructions can be done for a tool like Excel, the next question was: can it be done for other tools? So, I tested the same idea with Tableau.

Tableau is a different environment (understatement of the year?). A fundamental difference is that Tableau is a structured environment where you assign data to visual marks, instead of choosing a chart type from a chart library to display data scattered all over the sheet. If you’re coming from Excel, you’ll need some time to adjust.

I followed a similar set of constraints: one chart per page, cards to display the intermediate steps, the data set added to one of them. “Shelves” are an interesting concept in Tableau that you can’t find in Excel: they allow you to see the underlying structure and let you move things around by dragging and dropping, or using other visual marks. It made sense to include them, so for Tableau there are fewer and wider cards per page, to accommodate both the chart and the shelves.

I had no intention of forcing compatibility between the instructions for both tools. When possible, I used the same icons with similar meanings, but above else I wanted to respect the differences between the tools (more on this later.) Here is how to make a dot plot in Tableau.

Results

Since I already had instructions for making many charts in Excel, it made sense recreating them in Tableau by selecting a chart, and adapt the instructions for Tableau. I wanted to use this as a learning exercise, since my previous experience with the tool was limited. I got stuck plenty of times (often because I was thinking as an Excel user). But the Tableau community is really amazing, and I was able to find the right answers most of the time.

Now, if you are familiar with Tableau, and took a moment to think about the original list, you know how silly this idea of replicating the charts was. Some charts are simple variations in Tableau (change an option), but require a lot of work in Excel. The step chart is a good example: in Tableau, you change an option to switch between a regular line chart and a step chart, while in Excel you have to start from scratch and play with error bars to get the same result.

While some charts are simple to make in Tableau, circular charts tend to be harder: as far as I know, you can’t rotate a pie chart to start at a desired angle, for example. And then there are the error bars. How I missed them! I don’t often use them to show, well, errors, but I use them in Excel for everything else, from fake bar charts to fake grid lines, to fake sticks in a lollipop chart. You can imagine how flabbergasted I was when I discovered that Tableau users have been living all these years without true error bars!

Tableau vs. Excel

I don’t want to turn this into another Tableau vs. Excel post, but this exercise made a few things clearer to me:

• If your needs are simple and you can/want to stick to the Excel chart library I don’t see much benefits from switching to Tableau.
• Except for circular charts, many of the more advanced Excel charts felt pretty basic when made in Tableau (I hated that feeling!).
• For similar charts, you need to take more micro steps in Excel than in Tableau.
• In Excel, my kingdom for a horse small multiples.
• Encoding data into variables (color, size…) is way easier in Tableau.
• Combining multiple marks is easier and more flexible in Excel than in Tableau.
• That comes at a price: in Excel, all those steps need to be recreated to change perspective, while exploring and moving things around is at the heart of Tableau.

What’s next?

Compared to Excel, I believe the instructions for Tableau are more consistent and use a few new ideas. This needs to be cared first. My ultimate goal is to have a generic visual language that can be applied to any point-and-click tool. I’m still unsure if this is feasible, but it’s my reference point. Using both Excel and Tableau gave me the opportunity to compare in more detail the process of making charts. However, using then raised a number of challenging questions:

• Is it really possible to unify them into a single set of icons and grammar rules?
• How much of it is tool-specific?
• What’s the right balance between more abstract, tool-agnostic icons, and concrete, easier to recognize icons that tend to be associated with a single tool?
• How much of this can be automated?
• How easy is to adapt it to other tools?

So, next steps: improve compatibility between instructions for both tools and make more complex charts, especially in Tableau.

The e-books

Do you want to learn how to make the charts above? Do you think these visual instructions can help you? You can get the Excel or the Tableau e-book for USD $27 each, or a bundle for only USD $37. With your purchase you get access to any updates until the end of 2019.

The original post is titled Wordless instructions for making charts: Tableau Edition , and it came from The Excel Charts Blog .

Data visualization is a young discipline, and we use a lot of words to discuss and try to figure out what works, why, and when. I do think experts could/should use more visuals in these exchanges, among them or when communicating with a general audience (to be honest, my own data visualization book would probably benefit from such advice.)

When I started this project, I wanted to focus on the how-to part, and the end product should be a dry step-by-step guide on how to make charts in Excel. But, because I was using words, leaving the why and the what-for parts out was harder than expected. Words have a will of their own, and they multiply like rabbits if you’re not paying attention. Having to weed them out when you’re not even using your mother tongue can be exhausting. And then one day I realized I could get rid of them, all of them. “Wordless” became my favorite word.

We need words to explain or justify, but written instructions can range from confusing to useless (can you imagine written instructions in aircraft safety cards or traffic signs?) Even if they require some learning, visuals tend to be more universal and immediate.

There is another good reason to go wordless. Most people can communicate in English, but it doesn’t mean they are comfortable with it to fully understand an argument or even to follow instructions. Living in a small, poor, and non-English speaking country (Portugal), I’m keenly aware of this. Small countries also mean small or non-existent markets, especially for niche products. There are no translated data visualization books here, and only one book was published by a local author (in 2006!).

Going wordless, IKEA style

A few years ago, I realized that I tend to gravitate towards a low-cost, ephemeral, and functional type of data visualization or, in other words, IKEA-style data visualization (I suspect most people wouldn’t take this as a compliment, but I would). This project made me aware that there is more to learn from IKEA.

For many of us, assembling a bed or a table is a complex task. That’s why simple and clear instructions are essential for the success of a knock-down furniture business. IKEA’s wordless instructions manuals send a unified message than can be understood by most people across cultures and languages (easier said than done, though).

If you can assemble a BILLY bookcase, I’m sure you can use wordless instructions to make a chart in Excel.

The sign system

We can agree that aircraft safety cards and IKEA instructions display a real-world situation that is easily recognizable, while many traffic signs are abstract and you have to learn what they mean. We’ll use this one as our model.

The image below provides instructions on how to make a doughnut chart. If you follow the steps you should get a similar chart. Create a small table like the example displayed and try it out!

In general, to make a chart you need to select an object, perform some kind of action and apply formatting options. Colors identify each category.

Try to read these instructions. For example, step 2 means “select the chart area and do not apply Fill or Line”. Step 7 means “select the labels and display percentage and category name”.

I tried to use self-explanatory icons whenever possible, but their meaning is also available in multiple languages. The ebook comes with English, Portuguese, French and Spanish translations, but more can be added to a table in a shared Google sheet (the file is open access and unprotected, so anyone can add a new language).

There are few simple charts that most people can make without instructions. Think of them as training wheels the will help you familiarize with the icons and their meaning. This is a first set of 50 charts (hopefully there will be more soon!), so no chart is particularly complex.

Where to go from here?

By design, this ebook focus on the how-to, and excludes explicit data visualization guidelines. But the examples stay clear from Excel chart defaults and formatting options. There are a few minor concessions, but no capital sin. I like to expose readers to these implicit guidelines while using the instructions.

This is my first attempt to create a sign system, so a few inconsistencies remain. I’d like to iron them out, optimize the design and automate a few steps. I’m curious about using these visual instructions to create more complex charts or evaluate their flexibility when applied to other point-and-click tools.

Buy the ebook!

You can buy the ebook here.

The original post is titled [New ebook] Wordless instructions for making charts in Excel , and it came from The Excel Charts Blog .

Data tweeps: Help! I need to become a competent data viz thinker, well, immediately. Are there “must-read” sources that y’all can suggest?

— Lindsey Leininger (@lindsleininger) September 27, 2017

In spite of being a notorious Excel Brute Forcer (thanks, Elijah!), I was invited for a presentation at JMP and was working on it (and answering 5 interesting questions for them). This tweet felt like a great starting point because, as I said to Lindsey, “becoming a data viz thinker” is not a common formulation. I ended up structuring my presentation around 12 ideas that could be relevant for this goal.

The presentation was yesterday, 26 October, and it was recorded, so I’ll add a link here as soon as it becomes available.  Meanwhile, let me summarize those 12 ideas, many of them can be found in my book, but not all. Please note that JMP users are mostly scientists, engineers and similar fauna.

1. I couldn’t care less about data visualization. Starting with a bang but I really mean it: not everything needs to be visualized. Often there are other methods of data exploration and communication and they complement each other. That’s why in the Anscombe Quartet you need both the charts and the statistical metrics. If you have to make a chart, make it count. Don’t replace information overload with chart overload.
2. Data matters. The expression “data visualization” was carefully designed to make you think that (counting the letters) you’ll spend more than 70% of your time designing cool “visualizations”, while in reality the opposite is true: you’ll spend most of your time minimizing errors, structuring the data, making sure the concepts are the right ones, and much more. Often, managers or clients fail to understand the resource-intensive nature of the task. They think it magically happens.
3. Perception and society matter. Being aware of internal mechanisms (the eye-brain system) and external mechanisms (social rules, corporate culture, peer pressure, audience profile) should impact how we communicate visually.
4. Data mapping and design. Creating new chart types is easy because we basically map data points to a 2D plane and after that everything is design. Thinking at that level of abstraction is interesting not only because your communication can become more flexible but also helps when moving between tools.
5. Data is interpretation. From the moment you collect the data to the moment you read someone else’s chart interpretation is always present. Torture the data to come up with multiple interpretations and points of view. Even Minard’s Napoleon March, in spite of all variables, is an interpretation (that the Russians will probably disagree with). What makes a good chart is how good it is at saying what what it wants to say. Among other things, this means that it should be a good data pre-processing system that allows the brain to focus on higher level tasks. But data visualization is not enough: you have to have the contextual knowledge to detect and interpret patterns.
6. Data visualization is a process. Not a linear one. Be aware of the questions you ask. They often reveal not only what you want to know but also what you actually know. Better questions mean better understanding. It’s interesting to have a classification of questions and see how they can be paired to chart types (better: chart designs). A pie chart with 50 slices is not necessarily bad: usually a visualization fails not because there are too many data points but because the author doesn’t understand the data or doesn’t care about the message.
7. Rules of engagement. Attracting people’s attention with decoration is lazy. There are other effective methods that should be considered first (the data itself, chart titles, avoiding defaults, self-interest…)
8. Aesthetics and emotions. Stephen Few and David McCandless. Nuff said.
9. Emotional tone. Define a subdued emotional framework for multiple charts, never The Crying Boy style. Match tone and data (fun with the Titanic data set?). Be aware of the addiction to sugary data visualization.
10. Complex simplicity. Simplicity is not minimalism or removing junk. Remove the irrelevant, minimize the accessory, adjust the necessary and add the useful.
11. Using color. Avoid cliches like the plague and color to prettify. Think of it as stimuli that should be managed (intensity, function, symbolic meaning). The aesthetic dimension of color is an afterthought for non-designers. Use a professionally designed color palette and never the default one.
12. Go beyond the single graph. Structured, matrix style visualizations: small multiples, trellis displays. Animation as stacked small multiples. For free-form visualizations (dashboards, infographics) find a coherent narrative or visual landscape. Use Ben Schnidermans’ Visual Information-Seeking Mantra. For the overview, use gateway charts (simple, perhaps playful charts like pies or gauges that can lead to more addictive and complex charts). Never use gateway charts by themselves. When exploring, often focus + context is often better than filtering.

So, this is a summary of my presentation in 26 October at SAS/JMP in London. I did have a great time there and people were very nice. I had no previous contact with JMP and the people behind it, except Xan Gregg, with whom I talk from time to time on Twitter.

Full disclosure: I was payed for this presentation. At no time I was asked to talk about the product and I have no financial motivation to do so. I will probably write about it in the future, just like I talk about Excel, Tableau or PowerBI. If there is any change I’ll disclose it as well.

The original post is titled 12 ideas to become a competent data visualization thinker , and it came from The Excel Charts Blog .

Core message

When we have a small table, it’s OK to use hard numbers to communicate, and perhaps we can use a chart or two to illustrate them. When our table grows, we have to shift our analysis and communication from the individual data points to their relationships. For that to happen, charts need to move to center stage, and their design must change, along with their nature. Tables and charts switch roles: now we communicate with charts, and use a few hard numbers to illustrate.

The problem with most charts in publications from the Eurostat and from national statistical institutes is that, at their heart, they remain illustrations. Because more data was added, and their nature, purpose and design didn’t change much, they became both less effective and less efficient. I present several examples of this chart-as-illustration perspective and possible alternatives.

Effectiveness and efficiency

How good a chart is at making invisible relationships visible defines its effectiveness (more on that later). How well it manages finite resources (page/screen real estate, color constraints) defines its efficiency. While we discuss effectiveness all the time, efficiency is often overlooked (because most of the time we have enough space to display a single chart?). But now we have to take small screens into account, and designing “graphic landscapes” (infographics, dashboards) requires a better management of the available resources. Effectiveness and efficiency are closely connected, and in many cases when you improve one you’ll notice a positive impact on the other.

Aesthetics…

Making a chart easy to read, making it relevant to me or displaying unexpected patterns: grabbing audience’s attention doesn’t have to always be about aesthetics. Problem is, adding makeup (by way of canned visual effects) is a simpler path. Vendors take advantage of that to add a few bells and whistles to “make your chart look professional and memorable”, code for “silly effects not found in Excel or PowerPoint”. It’s possible that they do grab your attention once, or even twice, but a third time will put you off for good.

If you can’t use canned effects, if most defaults are ugly or ineffective, and if a statistician is not required to possess artistic talent or graphic design skills, how do you make charts that are both effective and pleasing to the eye?

I too had to find a way to create more pleasing charts without this apparently basic talent/skill (you can’t imagine how painful is for me to draw a recognizable sticky figure). Much of my book is devoted to this.

… for mere mortals

Here is what works for me. All design choices when making a chart have an aesthetic and a functional dimension (form and function). Understanding and managing the functional dimension is much easier than the aesthetic dimension: if you want to emphasize a series in a line chart you can use a saturated color, then use pale colors to encode the remaining series and gray for axis and grid lines. You can read this as “managing stimuli intensity”, no aesthetics involved. Functional choices impact the aesthetic result (and the other way around), but my own experience tells me that, when I put aesthetics first, the end result will be ugly.

When you put function first you can play with ideas and concepts without feeling you are losing control to vague and contradictory sensations of beauty and aesthetics. The chart below is an example of a hobby of mine: trying to salvage apparently hopeless chart types, like the gauge / speedometer. It displays three pointers instead of one, and each pointer is actually a time series. The jury is still out on this chart, but it could be used in very specific cases. Except for the chart type itself, all design choices can be justified rationally.

Left brain, right brain. Really?

Later this month I’ll be in Pamplona, Spain, for the Malofiej, infographic summit and awards. On the surface, NTTS and Malofiej can hardly be more distant from each other. Most people at NTTS come from statistical institutes or similar organizations, while at Malofiej most people are graphic designers, artists, journalists. Kind of left brain vs. right brain.

I know several people attending both conferences, so maybe this is not about brain hemispheres. Maybe at a not-so-fundamental level they are more similar than expected. We can easily see this in a recent article, where Stephen Few proposes seven criteria to evaluate a data visualization effectiveness profile, grouped into two categories, informative (usefulness, completeness, perceptibility, truthfulness, intuitiveness) and emotive (aesthetics, engagement).

These criteria can be applied to a beautiful infographic or to a terribly distorted 3D pie chart. Both are instances of visual communication, and their effectiveness profile can be compared. That said, some criteria are valued differently from field to field, aesthetics being the obvious example. A graphic designer is supposed to be able to create a visualization that is pleasing to the eye and, in some cases, unique. At a statistical office these skills are not required or expected.

If you use data visualization to communicate, you should keep experimenting the effectiveness of your visualizations, and that applies to everyone.

Color

Color is a difficult subject for everyone, with or without the right skills. Apparently, when left unattended, people tend to cram as many saturated colors into a chart as possible. I would need to take a closer look, but my feeling is that national publications where no color constraints seem to be in place have more color issues than the ones following the Eurostat guidelines or similar.

The real issue in both cases (with or without guidelines) is that color is not used effectively from a data visualization point of view. Again, if you identify the functional tasks of color, using it becomes much easier (or less difficult). I identify six tasks: categorize (using colors/hues), group (using colors and tints), emphasize (using color and saturation), sequence (using tints), diverge (colors and tints) and alert (color). You also need to manage gray.

If you try to use color effectively, you’ll probably discover two interesting things: first, we often use color more often (and more colors) than we need; second,  if you remove color you’ll have to change other design options that will probably improve your chart.

Tools

There is no shortage of data visualization tools, from the so-called self-service BI tools (PowerBI, Qlik, Tableau) to a vast array of programming languages and libraries (R, Python, D3). And then you have Excel.

Of all the charts published by the Eurostat and the national statistical offices, I’m not aware of a single one that couldn’t be made in Excel, and then some. There are several reasons why Excel is the right tool to make charts for these publications, and also a tool to experiment and go beyond its poor chart library. Excel charts don’t have to look the same, here is one that looks a bit different:

If you think Excel has no place in a conference titled New Techniques and Technologies for Statistics here is a quick reply before you fall into your fake Excel-induced narcoleptic state: you’re wrong. You can use Excel to support new data visualization practices and explore new ways of doing so. And don’t worry, I believe there should be a place for you to explore new tools and cool data visualization gadgets.

You can download the presentation here and the extended abstract here. [I updated the presentation and exported it to PDF. You can find it here.]

Comments, suggestions? Leave them below. Don’t forget to follow me on Twitter (@camoesjo) and the NTTS hashtag (#NTTS2017)

[Update: You can watch the entire session on visualization in the video below. Click start to jump to my presentation]

The original post is titled A companion post to my NTTS2017 presentation , and it came from The Excel Charts Blog .