Skip main navigation

Why Create Exploratory Visualisations in Excel?

This article is an introduction to the importance of visualising data in Excel and its role in revealing patterns and trends.
Visualising data is often the most effective way to describe, explore, and summarise a set of numbers, especially for very large data sets. Generally speaking, there are two categories of data visualisation: exploratory and explanatory.
In this topic we’ll focus on exploratory data visualisations. As you’d expect, exploratory visualisations are used in the exploration phase of your analysis, when you’re becoming familiar with the data. Translating the data into a visual medium can help you to quickly identify features, trends, or anomalies.
Explanatory data visualisations, on the other hand, are applied at the end of an analysis, when you want to communicate insights from your data. At this point you know what your data has to say and you’re trying to tell that story to someone else (eg, your colleagues, external stakeholders, or the general public).
Because these two types of visualisation serve different purposes, your approach to creating them will be different, as will the tooling (to an extent). In this activity we’ll use Excel to create exploratory visualisations.

The power of exploratory visualisations

To understand the power of exploratory visualisations, we can look at Anscombe’s Quartet[1] to counter the belief (common in 1973) that graphs were ‘rough’, and only numerical calculations offered a precise way to interpret data.
Anscombe constructed four data sets, each containing two sets of values: x and y. These are shown below, labelled I–IV.
IIIIIIIV
XYXYXYXY
10.08.0410.09.1410.07.468.06.58
8.06.958.08.148.06.778.05.76
13.07.5813.08.7413.012.748.07.71
9.08.819.08.779.07.118.08.84
11.08.3311.09.2611.07.818.08.47
14.09.9614.08.1014.08.848.07.04
6.07.246.06.136.06.088.05.25
4.04.264.03.104.05.3919.012.50
12.010.8412.09.1312.08.158.05.56
7.04.827.07.267.06.428.07.91
5.05.685.04.745.05.738.06.89
Although there are some patterns in the data (ie, the x values are the same for sets I–III), it’s difficult to extract any meaning for the data, even with a relatively small number of values.
If we calculate descriptive statistics for each of the data sets:
  • the mean of the x values is 9.0 for each set
  • the mean of y values is 7.5 for each set
  • the variance of x is 11 for each set
  • the variance of y is between 4.122 and 4.1228, depending on the set.
Further analysis, including correlation and linear regressions (you’ll learn about these later in the course) have nearly identical values. All these descriptive calculations suggest the data sets are virtually identical in nature.
What happens when we plot these data sets?
Visual representation of Anscombe's data sets showing stark difference in data (positive correlations, negative correlations and no correlations)
As you can see, the graphs appear very different. (Note: The pink line represents a simple linear regression on the values.) The graph for the first data set is as we might expect; however, the remaining sets show very different relationships in the data. As you can see, even elementary visualisations of data immediately reveal a lot more about the structure than numeric values do.

References

  1. Anscombe F. Graphs in statistical analysis. The American Statistician. 1973 Feb;27(1):17–21. Available from: https://www.jstor.org/stable/2682899?seq=1
This article is from the free online

Data Analytics for Business: Creating Databases

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education