What visualization works for your analysis purpose?

Visualization? Who doesn’t create at least one Excel chart from undergrad, huh? Yet, when considering user experience and data story-telling, visualization is an art. In this post, I’ll write about what type of visualization works for your purpose, with examples and illustrations. All charts were created with Excel :)

this image is from the internet

Trends

Easy one. Line charts and Tables with sparklines work like a charm.

Case study: You are an FP&A and need to prepare a quick report on the sales trends of your company’s top 10 stores in the past years.

Figure 1: sparklines provide a quick trend overview across multiple categories at a time

Sparkline is the simplest way to quickly show trends over time. You can edit the axis so the visual can be used for comparison of sales across 10 stores on the same axis scale.

However, be careful with line charts in this case, as they can be distractive, like this:

Figure 2: a messy trended line chart

Figure 3: This looks better, clean and informative. But more than 5 categories, sparkline, please.

Pattern

Line charts and control charts will be your to-go.

Case study: You are the plant manager and want to reduce the defect rate. So, you go ahead and collect data. Now you’re examining the data you’ve collected. What type of visualization works for your purpose?

Figure 4: this image is from the internet — it’s painful to create a control chart with Excel…

Comparisons

A lot of options for you: Back-to-back bar charts, Slope charts, Bar-bell charts, Waterfall charts, Whisker box charts, and text boxes.

Case study: You are the HR of Amazing Grocery and building a new employee engagement program to reduce the employee absence rate. The absenteeism data was collected for you and now, you wonder what employee group you should start your analysis with.

Back-to-back bar chart

Figure 5: back to back bar chart to compare values between 2 groups

Back-to-back bar chart is a delicious graph. This one is made easy with the “Group” function in Excel, which means once you are done building the charts you can group them all and move the chart to .pptx for later presentation without any problem.

Figure 6: Another intuitive back-to-back bar chart

Barbell chart

Figure 7: barbell chart to compare values between 2 groups across multiple categories

This is an attractive and informative chart for the comparison of two groups (male vs female) across multiple categories (departments).

Column chart

Figure 8: column chart for quick simple comparison among categories

As human eyes are built to catch the difference in height and length well, this is a simple yet powerful chart for comparison.

One question you might have, do line charts make sense as my human eyes can see the difference implied by the line moving up and down? Unfortunately no for this case, as the data is discrete. Line charts create an illusion that the data is smoothly continuous.

Lollipop chart

Figure 9: A beautiful version of “column chart”

Whisker box chart

Figure 10: box plot for comparison among categories

This chart is a good one when the data has outliers that the mean measure turns out misleading.

Text box

Figure 11: text box is my favorite when I just want something simple and intuitive

Waterfall chart

Case study: You’re a Financial Analyst and preparing a sales report for your aluminum ingot products for the last quarter and first half of the year.

Figure 12: waterfall chart to visualize incremental changes to a metric

Slope chart

Case study: You’re a Customer Experience Manager of Central Park. Over the past few years, you implemented a new reconstruction program for your park. Now, that it’s the end of the program, you wonder how customer experience has improved.

Figure 13: slop chart to intuitively visualize trends

In this slope chart, I grayed out neutral and dissatisfied lines in purpose, making the chart more concise and clean. No important information was left out.

Some charts are just hard to read

It’s more difficult for human eyes to catch the differences in area and angle than length and height.

Figure 14: treemap chart is hard for human eyes to detect differences among categories

This treemap shows that the people with 4, 5, and 6 years of service seem to be so much different in the average hours they logged off work last year. However, looking back at the column chart above, there’s a very subtle difference in their average absence hours.

Figure 15: another hard-to-read chart

The same goes for donut charts.

Relationships

What’s a better visual to examine the relationship among 2 variables than a scatter chart?

Figure 16: scatter chart to visualize variable relationship

Performance

Column chart, Bar chart, Donut chart, and Conditionally formatted table all work.

Figure 17: effective types of visual for performance tracking

Summary

There are tons of ways to visualize data. You can create the best user experience by simply keeping the user in mind — asking such questions as what info they need, what details are important/what details are noise, does the visual show too much info, is the visual easy to interpret with human eyes, how the users want the visualization to be delivered (presentation or handover)? etc… And don’t forget to ask yourself if the users have any color vision deficiency. This aspect can be easily overlooked!

Thanks for reading and happy visualizing!