Introduction to Data Visualisation

 Theory

Data visualisation workshop - Excel November 2016

PwC 1
Contents
Intro Individual Projects
 History of Visualisation
Why Visualise
 Display
 Analyse
 Communicate
Classifications of Visualisation
 Explanatory VS Exploratory VS Exhibitory
Visualisation Process
 Types of Data
 Visualisations Techniques
 Best practices
Hands On with Excel
 Dashboards in Excel
 Effective Communication
 Pivot Tables in Excel

Data visualisation workshop - Excel November 2016

PwC 2
Data Visualisation
Introduction

Topics
In the following theory section we will guide you through the following topics:
• Intro
• Why visualise?
• Classifications of Visualisations
• Visualisation process

The topics covered in this section are meant to give an general overview of data
visualisation and are by no means exhaustive lists or examples.

Data visualisation workshop - Excel November 2016

PwC 3
Introduction to Data Visualisation

 Intro

Data visualisation workshop - Excel November 2016

PwC 4
Data Visualisation
History of Visualisation
Charles Minard’s graph of Napoleon’s invasion
Drawn in 1869 one of the most cited examples of statistical graphics occurred when
Charles Minard mapped Napoleon’s invasion of Russia. The map depicted the size of the
army as well as the path of Napoleon’s retreat from Moscow – and tied that information
to temperature and time scales for a more in-depth understanding of the event.

Data visualisation workshop - Excel November 2016

PwC 5
Data Visualisation
History of Visualisation
John Snow’s cholera map
In 1854 doctor John Snow used a
map to chart the cases of cholera in
London’s Soho district. He
identified that the cases where
clustered around water pumps and
thus determined that the disease
was spread through the water
supply.
Snow's ground breaking study was
a major event in the history of
public health and geography. It is
regarded as the founding event of
the science of epidemiology.

Data visualisation workshop - Excel November 2016

PwC 6
Introduction to Data Visualisation

 Why visualise?

Data visualisation workshop - Excel November 2016

PwC 7
Data Visualisation
Why visualise?

What is it?
Data visualisation helps the end user to understand and get insights on the
visualised data. Visualisation focuses on techniques to present data in a visual
way in order to facilitate the discovery and understanding of underlying
patterns, whether it’s done for research, science or for decision makers, in order
to:

Display Analyse Communicate

Plots, trends, timelines, Develop and asses hypotheses Share and persuade
etc.
Discover errors in data Collaborate
Find patterns

Data visualisation workshop - Excel November 2016

PwC 8
Data Visualisation
Why visualise?

Advantages

Data visualisation takes advantage of the human brain’s highly evolved

visual system. Our visual cognitive abilities mean we can quickly
recognise patterns in an image.

Visualisation takes advantage of this ability to

identify, explore, interpret and understand patterns within large datasets.

This is becoming increasingly important as the amount of data involved in every

profession is growing exponentially by the day.

Data visualisation workshop - Excel November 2016

PwC 9
Data Visualisation
Why visualise?

To Display
There are a multitude of ways available to display data according to the needs of
each circumstance.

Bar Charts Scatter Plots Spark Lines Box Plots

Tree Maps Line Graphs Bullet Graphs Heat Maps
Pie Charts Flow Charts Org Charts etc.

Whether we seek to aid analysis of the data or simply to inform the viewer,
all of the above help us display data in a visual manner.

Data visualisation workshop - Excel November 2016

PwC 10
Data Visualisation
Why visualise?

To Analyse
The outcome of the above charts and graphs helps us detect:
• Trends
• Anomalies
• Correlations
• Patterns
and eventually make:
• Decisions

Data visualisation workshop - Excel November 2016

PwC 11
Data Visualisation
Why visualise?

To Analyse
If you need to find something buried inside 1,000,000,000 of data points
• Would you rather read through the data or visualise it?
• What if you don’t even know what you are looking for?

Data visualisation workshop - Excel November 2016

PwC 12
Data Visualisation
Why visualise?

To Analyse
A well-designed dashboard allows decision makers to analyse massive
datasets at a glance

Data visualisation workshop - Excel November 2016

PwC 13
Data Visualisation
Why visualise?

To Communicate
• The right visualisation can emphasize key points, provide context and
engage the audience.
• Great speakers use visualisations to support their ideas and make them
memorable.
• Sometimes the visualisation communicates all the information on its
own.

Data visualisation workshop - Excel November 2016

PwC 14
Data Visualisation
Why visualise?

Reasons We Visualise By Francesco D’Orazio

Francesco D’Orazio, a visualisation expert, gives a few convincing reasons why
visualisation is necessary to exploit the value of data:
• Visualisation acts as an external memory, allowing us to take into account a greater
number of variables and conduct reasoning on them.
• It allows us to objectify abstract information with shapes and colors to more
easily compare and classify large amounts of data.
• Visualisation is perfect for providing context and narrative to data, thus allowing
us to grasp a holistic view of a problem, not just a fraction of it.
• Visualisation allows us to represent process, thus we can incorporate time in spatial
terms and depict transformative processes in a visual way.

Presentation by Francesco d’Orazio:

http://www.slideshare.net/Facegroup/10-reasons-why-we-visualise-data?from_action=save

Data visualisation workshop - Excel November 2016

PwC 15
Introduction to Data Visualisation

 Classification of Visualisations

Data visualisation workshop - Excel November 2016

PwC 16
Data Visualisation
Classification of visualisations

Categorisation by Andy Kirk

Andy Kirk, a data visualisation specialist who has grown to become a guru of
the field proposes a categorisation of visualisations based on the intended
function of the visualisation, namely:

Exploratory

Explanatory

Exhibitory

However it is important to remember that often visualisations fall into more

than one of these categories.
Data visualisation workshop - Excel November 2016
PwC 17
Data Visualisation
Classification of visualisations

Exploratory visualisation
Exploratory visualisation is used when there is a big amount of data and we are
unsure of the information hidden within it. In order to get a sense of
what is hidden in the data we use a visual medium to help identify its features
such as patterns, trends and outliers.
Exploration is better to start at a high level of granularity. After detecting points
of interest, one may dig deeper to detect details.

Example:
Britain’s Diet: http://britains-diet.labs.theodi.org/?es_p=1359956
Data visualisation workshop - Excel November 2016
PwC 18
Data Visualisation
Classification of visualisations
Exploratory visualisation
Line Graphs

Data visualisation workshop - Excel November 2016

PwC 19
Data Visualisation
Classification of visualisations

Explanatory visualisation
Explanatory visualisation is used when we already know what is in the data and
need to convey and explain an insight to someone else, such as a decision maker
or the general public.

Examples:
Syrian Conflict:
http://www.slate.com/blogs/the_slatest/2015/10/06/syrian_conflict_relationships_explained.html
Vaccination effects: https://www.theguardian.com/society/ng-interactive/2015/feb/05/-sp-watch-
how-measles-outbreak-spreads-when-kids-get-vaccinated
Data visualisation workshop - Excel November 2016
PwC 20
Data Visualisation
Classification of visualisations
Explanatory visualisation
Infographic

Data visualisation workshop - Excel November 2016

PwC 21
Data Visualisation
Classification of visualisations
Explanatory visualisation
Simulation

Data visualisation workshop - Excel November 2016

PwC 22
Data Visualisation
Classification of visualisations
Explanatory visualisation
Map + Relations

Data visualisation workshop - Excel November 2016

PwC 23
Data Visualisation
Classification of visualisations

Exhibitory visualisation
Exhibitory visualisation is simply the displaying of data. It may be the case in
communication scenarios or in times where simply displaying the information
tells a story on its own.

Examples:
NatGeo offshore wind: http://www.nationalgeographic.com/climate-change/carbon-free-
world/index.html?source=carbon-free-america#map/offshoreWind/GRC
World Languages: http://www.densitydesign.org/ddfs13/afterbabylon/
Data visualisation workshop - Excel November 2016
PwC 24
Data Visualisation
Classification of visualisations
Exhibitory visualisation
Map

Data visualisation workshop - Excel November 2016

PwC 25
Data Visualisation
Classification of visualisations
Exhibitory visualisation
Map + Relations

Data visualisation workshop - Excel November 2016

PwC 26
Introduction to Data Visualisation

 Visualisation Process

Data visualisation workshop - Excel November 2016

PwC 27
Data Visualisation
Visualisation process

Stages of the visualisation process

In order to end up with a satisfying result in data visualisation, it is important
to follow a step-by-step process. There are four main stages of this process:
1. Question formulation
2. Data preparation
3. Considering the medium
4. Development of a visual representation

Data visualisation workshop - Excel November 2016

PwC 28
Data Visualisation
Visualisation process

Question Formulation

When creating a visualisation, the first step should always be to clearly state
the question to be answered.

By being conscious of the answer we need,

we can more effectively choose the data
required to answer it.
A common mistake is to dive head first into
all the available data and end up losing the
initial goal and over-complicating a rather
simple process.

Data visualisation workshop - Excel November 2016

PwC 29
Data Visualisation
Visualisation process

Data Preparation
In this step we ensure that we have all the data we need and the way we
need it. It is often the case that the most time consuming step of a visualisation
project is preparing the data to be used. Preparing the data involves, among
others, ensuring the required:

• Accessibility
• Validity
• Accuracy
• Relevancy
• Format
• Consistency
• Granularity

Data visualisation workshop - Excel November 2016

PwC 30
Data Visualisation
Visualisation process

Considering the Medium

Another thing to consider before moving on to create a wonderful visualisation,
one must consider the medium that will be used to display it. A couple of
examples could be:
• Will it be print or digital?
– In print we have a set space that we can predefine, while in digital we have to
take into account how it will look on different screen types and sizes.
– Furthermore in print the colour output can be tested and set, but in digital
colours can appear differently from screen to screen, and ruin the outcome.

• Will it be static or interactive?

– If it is static all we need to do is design a single layout. But if it’s interactive
we have to consider how elements change and how this affects the information
conveyed and the aesthetics of the bigger picture.

Data visualisation workshop - Excel November 2016

PwC 31
Data Visualisation
Visualisation process

Development of visual representation

After having identified our question and prepared our data we can move on to
creating the visualisation. Only now is it time to decide on any issue relating to
the appearance of the visualisation itself.

The optimal visualisation design depends

on two factors, primarily: the message
to be conveyed to the audience, meaning
the question to be answered, and the
variables to be shown.

Data visualisation workshop - Excel November 2016

PwC 32
Data Visualisation
Visualisation process

Good visualisation is about making good decisions

To make the best decisions you need to be familiar with all your options and
aware of the things that will influence your choices.

Data visualisation workshop - Excel November 2016

PwC 33
Data Visualisation
Visualisation process

Good data visualisations are trustworthy

Communicating with numbers is, in many ways, just like communicating

with words. You make decisions about what to emphasize and
what to downplay, and about how to convey a full
understanding of the subject at hand.

Christopher Ingraham, The Washington Post

Data visualisation workshop - Excel November 2016

PwC 34
Data Visualisation
Visualisation process

Things to consider
The first things to consider when visualising data are:
• What do we want to present?
• Which variables?
• What types of variables?
• Which properties?
• Which aspects?
• What is the range of the variable values?
• What medium will display our visualisation?

Data visualisation workshop - Excel November 2016

PwC 35
Data Visualisation
Types of data
That is why we must understand the type of data at hand.
Quantitative Data Qualitative Data
• Data dealing with numbers • Data dealing with descriptions

• Data can be measured • Data can be observed but not measured

• Data can be categorical only
• Continuous data deriving from
measurements or processed data such • Consider quantifying the data
as means, variances etc.
• E.g. colors, mood, appearance, etc.
• Data can be categorical or continuous
• E.g.: length, volume, speed, etc.
• Sub-categories of numerical data are:
• Shape data

• Coordinate data

Data visualisation workshop - Excel November 2016

PwC 36
 Data Visualisation
Types of data– Example
Geospatial Data Network Data Textual Data
• Data that can be characterised • Represents hierarchical or • Data consisting of text
by a specific location in space non-hierarchical
and time relationships and • Usually analysed to
interactions between produce:
• Information about a physical entities, examples include: • Text categorisation;
object that can be represented
by numerical values in a • Graphs representing • Text clustering;
geographic coordinate system relationships between
entities (e.g., FB friends); • Concept and pattern
• Describes the location, size extraction;
• Interactions (e.g.,
and shape of an object communication traces in • document summaries,
social networks);
• And sentiment analysis etc.
• And hierarchies (e.g.
taxonomies). • Has to be “mined” before
analysis

E.g. Coordinates: E.g. LinkedIn data E.g. Poetry text

Lat: 51.0543, long: 3.7174 Shakespeare (sonnet 18)

N 51 3’16”, E 3 43’3” “Shall I compare thee to a summer’s

day? Thou art more..”
Data visualisation workshop - Excel November 2016
PwC 37
Data Visualisation
Types of visualisation by technique
After understanding our data, we need to decide on the most effective way to
visualise it for our needs.
Maps
Display data according to spatial relations, representations showing how data is
distributed spatially. Select data -> Insert -> Bing Maps
Religion Religion Not
Country
Important Important
Estonia 16% 84%
Sweden 17% 82%
Denmark 19% 80%
Norway 21% 78%
Czech Republic 21% 75%
United Kingdom 27% 73%
Finland 28% 70%
France 30% 69%
The netherlands 33% 67%
Belgium 33% 58%
Bulgaria 34% 62%
Russia 34% 60%
Belarus 34% 56%
Luxembourg 39% 59%

Data visualisation workshop - Excel November 2016

PwC 38
Data Visualisation
Types of visualisation by technique

Relations and dependencies

Relationships and dependencies between entities can be represented by using
nodes (representing data elements) and links (representing relationships or
dependencies).

Some of the content on this slide may

not be done natively in Excel
Data visualisation workshop - Excel November 2016
PwC 39
Data Visualisation
Types of visualisation by technique

Line Graphs
Represent the relation between two or more variables as a single line or area.

Select Data -> Insert -> Charts -> …

LINE CHART AREA CHART

80000 40000

60000 30000

40000 20000

20000 10000

0 0

Data visualisation workshop - Excel November 2016

PwC 40
Data Visualisation
Types of visualisation by technique

Dots or Bubble graphs

Are a graphical display of data, representing the relation between two or more
variables using dots or bubbles.

Select Data -> Insert -> Charts -> …

SCATTER PLOT BUBBLE CHART

60000 140
50000 120 120
105 106 110
109
40000 100
94 90
80
30000 69
60 59 63
63
57 57
20000 43
40 36 40
32
29
10000 20 242323
0 0
1995 2000 2005 2010 2015 2020 2025 0 5000 10000 15000 20000 25000

Data visualisation workshop - Excel November 2016

PwC 41
Data Visualisation
Types of visualisation by technique

Bar graphs
Are visualisations representing the relation between a categorical variable and a
continuous variable.

Select Data -> Insert -> Charts -> …

STACKED BAR GRAPH STOCK CHART

Aug-10 300
Jun-10
250
Apr-10
200
Feb-10
150
Dec-09

Oct-09 100

Aug-09 50

Jun-09 0
Jun-10
Jul-10

Jan-11

Jun-11
Jul-11
Mar-11
Feb-11
Aug-10
Sep-10

Nov-10
Dec-10

Aug-11
Sep-11
Oct-10

Apr-11
May-11

Oct-11
Apr-09
-250 -200 -150 -100 -50 0 50 100 150

Data visualisation workshop - Excel November 2016

PwC 42
Data Visualisation
Types of visualisation by technique

Grids
Representing entities in a grid to map them according to two or more axes.

Select Data -> Insert -> Charts -> …

RADAR CHART BUBBLE CHART
25000
20000
Inormation Tech 15000
10
Project 8 10000
6 Administration
Management
4 5000
2 0
0 -20000 -10000 0 10000 20000 30000
Customer -5000
Marketing
Support
-10000
Bob
-15000
Development Sale
-20000
Mary
-25000

Data visualisation workshop - Excel November 2016

PwC 43
Data Visualisation
Types of visualisation by technique

Shape and proportions

Show proportions without reference to a coordinate system.

Select Data -> Insert -> Charts -> …

DOUGHNUT CHART
Parafin
3%
STACKED BARS 100%
Crude
Natural Gas 10%
15%

10 25 15 13 8 11 15 3
LPG mazut
11% 25%

Gasoline
8%
0% 20% 40% 60% 80% 100%
Diesel Kerosine
13% 15% Crude mazut Kerosine Diesel Gasoline LPG Natural Gas Parafin

Data visualisation workshop - Excel November 2016

PwC 44
Data Visualisation
Best practices

Keep these in mind

 Identify your goal
Always start with a question
 Know your audience
Tailor your visualisation to the intended viewer’s expertise and expectations
 Provide context
Provide the supporting context which makes your visualisation meaningful
 Keep it simple
Don’t include any unnecessary information and avoid clutter
 Keep it engaging
Nobody pays attention to a boring visualisation
 Consider the colour blind
Colour blindness is not uncommon and can render a visualisation useless
Data visualisation workshop - Excel November 2016
PwC 45
Introduction to Data Visualisation

 In practice

Data visualisation workshop - Excel November 2016

PwC 46
Contents
Microsoft Excel Hands On
 Creating Dashboards in Excel
Effective Visualisation
 Effective communication
 Choosing the correct graph
Pivot tables and other visualisation
features
 Pivot tables
 Creating a Pivot table
 Refining the Pivot Table
 Creating a Pivot Chart
 Data Analysis Toolpak
 Power Map
Individual Projects

Data visualisation workshop - Excel November 2016

PwC 47
Introduction to Data Visualisation

 Microsoft Excel hands on

Data visualisation workshop - Excel November 2016

PwC 48
Visualising in MS Excel
Creating a dashboard in Excel

Loan repayment dashboard

Using interactively calculated data, we will create a dashboard visualising
different metrics about a loan repayment.

Data visualisation workshop - Excel November 2016

PwC 49
Visualising in MS Excel
Creating a dashboard in Excel

Loan repayment dashboard

On the “Payments” sheet we have all the data about the repayment of the loan,
including:
• “Month”;
• “Outstanding Amount”;
• “Monthly Instalment” and its breakdown into “Capital” and “Interest”;
• “Percent of Ownership”;
• and the amount of “Capital Owned” and “Interest Paid”.

Data visualisation workshop - Excel November 2016

PwC 50
Visualising in MS Excel
Creating a dashboard in Excel

Loan repayment dashboard

Furthermore on the “Payments” sheet we have an assortment of cells that we
will need to create our dashboard, these include:
• “Number of Months”;
• “Borrowed Amount”;
• “Annual interest”;
• “Monthly Installment”;
• “Capital Owned”;
• “Interest Paid”;
• “Number of Payments”;
• “Remaining Payments”;
• “Amount Paid”;
• “Amount remaining”;
• and “Percentage Paid”.
Data visualisation workshop - Excel November 2016
PwC 51
Visualising in MS Excel
Creating a dashboard in Excel

Loan repayment dashboard

On the “Dashboard” sheet, we have a convenient input box, where we submit:

• the “Duration”, in months, of the loan;

• the “Amount” of the loan;
• the “Interest” rate;
• the date of the “First Payment”;
• and the current “DATE”.

Any changes done to the “Input Data” automatically update the data on the
“Payments” sheet.

Data visualisation workshop - Excel November 2016

PwC 52
Visualising in MS Excel
Creating a dashboard in Excel

Loan repayment dashboard

Let’s create a chart showing the amount of Capital we acquire relative to the
amount of interest we pay back with each instalment through time. Furthermore
let’s add a marker to show us where we are on that time series.

Data visualisation workshop - Excel November 2016

PwC 53
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Select columns A, C, D and E. Open up the “all charts” menu, and select
“Clustered Column” from the “Columns” section.

Data visualisation workshop - Excel November 2016

PwC 54
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
The result is a chart that very conveniently displays the amount of Capital
compared to the amount of Interest covered with each instalment.
Can you think of a different way this chart could be formatted?

Data visualisation workshop - Excel November 2016

PwC 55
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Right click on the chart and click on “Change Chart Type”, from the menu that
opens select “Stacked Column” or “Stacked Area”.

Data visualisation workshop - Excel November 2016

PwC 56
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
The chart now changes to show how capital and interest compose the total of
each instalment. Discuss which way could be best and why.

Data visualisation workshop - Excel November 2016

PwC 57
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Now cut and paste you chart into the “Dashboard” sheet. Remove the legends
and titles, add in a cell above the amount of each instalment by referencing cell
“M6” from the “Payments” sheet and add below two cells displaying the current
composition of Capital and Interest in each payment by referencing the necessary
cells.

Data visualisation workshop - Excel November 2016

PwC 58
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Now select the cells containing the “Number of Payments” and “Remaining
Payments”. Open the charts menu and select “Stacked Bar” from “Bars” . Cut and
paste the resulting chart into the “Dashboard” sheet.

Data visualisation workshop - Excel November 2016

PwC 59
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Format the chart accordingly to look like the one below. Keep in mind that the
label “16 out of 300” is actually a cell, referencing the “Number of Payments” and
“Remaining Payments” values from the “Payments” sheet.

Data visualisation workshop - Excel November 2016

PwC 60
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Select the cells containing the “Amount Paid” and “Amount Remaining”. Open
the charts menu and select “Doughnut Chart”. Cut and paste the chart onto the
“Dashboard” sheet.

Data visualisation workshop - Excel November 2016

PwC 61
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Sometimes visualising requires imagination. In your dashboard create a cell with
a reference to the “Percentage Paid” from your “Payments” sheet and drag your
doughnut graph ON TOP of that. Now doesn’t that look good?

Data visualisation workshop - Excel November 2016

PwC 62
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Select the two rows containing the “Total Interest” and “Total Capital” from the
Interest variance table and select “Stacked Column Chart”

Data visualisation workshop - Excel November 2016

PwC 63
Visualising in MS Excel
Creating a dashboard in Excel
Loan repayment dashboard
Right click in the chart, “Select Data…”, and “Edit” “Series1”. Select the “Rates”
and click “OK”. Copy paste the chart on your “Dashboards” sheet.

Data visualisation workshop - Excel November 2016

PwC 64
Visualising in MS Excel
Getting the data

Behold your creation

Arrange your graphs as it follows. Now isn’t that a great looking dashboard?
The best part is the interactivity. Whenever you change a value in the input, the
whole dashboard updates live. Great for exploring your options!

Data visualisation workshop - Excel November 2016

PwC 65
Visualising in MS Excel
Getting the data

Loan repayment dashboard

Let’s add one last chart that will:
• visualise three scenarios for the future value of the house;
• and based on that calculate the profit from selling the house at different
moments in time.

Data visualisation workshop - Excel November 2016

PwC 66
Visualising in MS Excel
Getting the data

Loan repayment
dashboard
Select the “Date” , “Value” and
“Profit” columns from the “Sales
forecast detail” sheet.
Open the charts menu and select
“Combo” chart.
From the customisation box
configure the “Value” series as
“Line” and the “Profit” series as
“Area”.

Data visualisation workshop - Excel November 2016

PwC 67
Visualising in MS Excel
Getting the data

Loan repayment dashboard

Right click on any of the “Area” series and select “Format Data Series”. From
the menu change the fill to “Solid” and add “Transparency”. Do this for all three
of the “Area” series until you can see all of them.

Data visualisation workshop - Excel November 2016

PwC 68
Visualising in MS Excel
Getting the data

Loan repayment dashboard

Now right click on the graph select “Select data”, click on “Add”, select the “Date
line” column from the “Forecast” sheet, click “Ok”. Right click again on the
graph, select “Change Chart Type” and configure the “Date line” as a “Column”.

Data visualisation workshop - Excel November 2016

PwC 69
Visualising in MS Excel
Getting the data

Loan repayment dashboard

Cut and paste your chart into the “Dashboard” sheet and format the colours
until all the series are clear.

Data visualisation workshop - Excel November 2016

PwC 70
Visualising in MS Excel
Getting the data

Trace Precedent and Dependent cells in Excel

Things can get pretty complicated in such projects so don’t forget to use the
“Trace Precedents and Dependents” buttons.

Data visualisation workshop - Excel November 2016

PwC 71
Visualising in MS Excel
Creating a dashboard in Excel

Importance of data preparation

The exercise we just completed truly illustrated the importance of
proper data preparation.

Imagine how much longer it would have taken if you didn’t have the
variables we needed pre-thought out and conveniently calculated in
prepared cells.

Data visualisation workshop - Excel November 2016

PwC 72
Introduction to Data Visualisation

 Effective Visualisation

Data visualisation workshop - Excel November 2016

PwC 73
Effective visualisation
Effective Communication

Sending a message VS Displaying information

Effective communication is getting messages across. This means getting the
audience to understand something.
A message differs from raw information in that it presents “intelligent added
value”, that is, something to understand about the information.
A message interprets the information for a specific audience and for a specific
purpose. It conveys the so what, whereas information merely conveys the
what. Because it makes a statement, it requires a complete sentence.

Data visualisation workshop - Excel November 2016

PwC 74
Effective visualisation
Effective Communication

“Total greenhouse gas emissions are calculated at

53,526,302 kT”

Select Data -> Line Chart with Markers…

Data visualisation workshop - Excel November 2016
PwC 75
Effective visualisation
Effective Communication

“Total greenhouse gas emissions (53,526 Mt) are

dangerously high!”

Data visualisation workshop - Excel November 2016

PwC 76
Effective visualisation
Effective Communication

“What”

Data visualisation workshop - Excel November 2016

PwC 77
Effective visualisation
Effective Communication

“So what”

Data visualisation workshop - Excel November 2016

PwC 78
Effective visualisation
Effective Communication

Know your audience

Information “A backup of the IT system is taken

once per week”

Message To a banking employee:

“When your system fails,
you risk losing one week of work.”
To the Legal Director:
“The backup schedule
does not comply with National Bank regulations”
To the CIO:
“The backup interval
should be increased to once per day.”

Data visualisation workshop - Excel November 2016

PwC 79
Effective visualisation
Effective Communication

Adapt to your message!

System Viruses %
Windows 5 8%
Linux 25 37%
Mainframe 20 30%
HP 17 25%

Data visualisation workshop - Excel November 2016

PwC 80
Effective visualisation
Effective Communication

Adapt to your message!

Right click on Column -> Column width…

System Viruses %
Windows 5 8%
Linux 25 37%
Mainframe 20 30%
HP 17 25%

Data visualisation workshop - Excel November 2016

PwC 81
Effective visualisation
Effective Communication

Adapt to your message!

Select Columns -> Align Right…

System Viruses %
Windows 5 8%
Linux 25 37%
Mainframe 20 30%
HP 17 25%

Data visualisation workshop - Excel November 2016

PwC 82
Effective visualisation
Effective Communication

Adapt to your message!

Select Cells -> Right click -> Format Cells -> Remove Borders & Configure Font

System Viruses %
Windows 5 8%
Linux 25 37%
Mainframe 20 30%
HP 17 25%

Data visualisation workshop - Excel November 2016

PwC 83
Effective visualisation
Effective Communication

Adapt to your message!

Select Cells -> Select cells -> Conditional Formatting -> More Rules…

System Viruses %
Windows 5 8%
Linux 25 37%
Mainframe 20 30%
HP 17 25%

Data visualisation workshop - Excel November 2016

PwC 84
Effective visualisation
Effective Communication

Proper Labelling
Avoid legends. Label all your charts by placing the necessary words next to
the items they describe. This makes reading the graph much more intuitive.
Select Data -> Pie Chart -> Right click -> Format Plot Area…

Rest of Asia

North
Europe America

Japan
North America Japan Europe Rest of Asia

Data visualisation workshop - Excel November 2016

PwC 85
Effective visualisation
Effective Communication

Matching human intuition

Truly visual representations are in essence intuitive: they require no new
interpretation rules, no verbal steps. Instead they are based on intuitive rules
interpreting proximity, similarity, prominence, and sequence.

What do you see? Rows or Columns?

Data visualisation workshop - Excel November 2016

PwC 86
Effective visualisation
Effective Communication

Matching human intuition

Truly visual representations are in essence intuitive: they require no new
interpretation rules, no verbal steps. Instead they are based on intuitive rules
interpreting proximity, similarity, prominence, and sequence.

How about now?

Data visualisation workshop - Excel November 2016

PwC 87
Effective visualisation
Effective Communication

Matching human intuition

Truly visual representations are in essence intuitive: they require no new
interpretation rules, no verbal steps. Instead they are based on intuitive rules
interpreting proximity, similarity, prominence, and sequence.

Or maybe now?

Data visualisation workshop - Excel November 2016

PwC 88
Effective visualisation
Effective Communication

Matching human intuition

A position representation need not start from zero, but one starting
close to zero can mislead viewers.
Select Data -> Insert Chart -> X Y (Scatter)…
25

23

21

19

17

15

13

11

5
0 5 10 15 20 25

Data visualisation workshop - Excel November 2016

PwC 89
Effective visualisation
Effective Communication

Matching human intuition

It is best to extend the axis to zero for a more intuitive display.

Right click on legend -> Format Plot Area-> Axis options…

25

20

15

10

0
0 5 10 15 20 25

Data visualisation workshop - Excel November 2016

PwC 90
Effective visualisation
Effective Communication

Matching human intuition

A concurrent variation in two (or more) directions results in a hard-
to-compare area representation.
Select Data -> Area Chart & Line Chart…

x y x*y

2001 2002 2003 2004 2001 2002 2003 2004 2001 2002 2003 2004

Data visualisation workshop - Excel November 2016

PwC 91
Effective visualisation
Effective Communication

Maximise the Signal-to-Noise ratio

A poor graph
The graph exhibits a very low signal-to-noise ratio, with excessive tick marks
and uncalled-for grid lines, and very little emphasis on the data.
Select Data -> Line with Markers…

Data visualisation workshop - Excel November 2016

PwC 92
Effective visualisation
Effective Communication

Maximise the Signal-to-Noise ratio

A good graph
The graph is plainer and better contrasted. The background no longer interferes
with the data, yet it provides sufficient information about them.
The labels are intuitive by being placed where they are needed, next to the data.
Right click-> Format Chart…

Data visualisation workshop - Excel November 2016

PwC 93
Effective visualisation
Effective Communication

Maximise the Signal-to-Noise ratio

A better graph
The graph shows the data and nothing but the data.
• Tick marks are relevant, not arbitrarily equidistant;
• and non-data lines are grey, to make the data prominent.

Data visualisation workshop - Excel November 2016

PwC 94
Effective visualisation
Effective Communication

Maximise the Signal-to-Noise ratio

Always avoid clutter. Keep your scales as simple as possible. Any scale
is fully defined with just two tick marks. Any other tick mark should
indicate a point of interest.
After reducing or eliminating the noise in the display, increase the
signal by making the data more prominent.
Right click on legend -> Format Chart -> Axis Options...

Data visualisation workshop - Excel November 2016

PwC 95
Effective visualisation
Choosing the correct graph

Comparing data
A straightforward way to compare numerical data is to represent them by lines
or bars of proportional length aligned at one end. To respect the proportion,
bars must start from zero.

Select Data -> Bar Chart + Right click -> Format Chart

Belgium 82

Netherlands 60

UK 55

France 25

Germany 10

Data visualisation workshop - Excel November 2016

PwC 96
Effective visualisation
Choosing the correct graph

Comparing data
Close data values, poorly resolved by a length representation, are best encoded
as positions along a scale , marked by dots. These do not need to run from zero
to be meaningful

Data visualisation workshop - Excel November 2016

PwC 97
Effective visualisation
Choosing the correct graph

Avoid pie charts

Pie charts, a common way to represent fractions, are intuitive but are not very
accurate, they fail to reveal small differences. They are best replaced by bar or
dot charts.
Select Data -> Pie Chart…
Germany
4% France
11%

Belgium
35%

UK
24%

Netherlands
26%

Data visualisation workshop - Excel November 2016

PwC 98
Effective visualisation
Choosing the correct graph

Displaying distribution
Showing the entire dataset as points along a scale is probably the most accurate
way to convey its distribution. The resulting display is simple and truthful to
individual data. For large datasets, however, it quickly becomes impractical.
Histograms reduce the dataset somewhat by grouping data n equivalent
intervals. For an easy and intuitive interpretation of the fraction of total data in
each interval, the bars must touch.
Select Data -> X Y (Scatter) + Format Chart & Column Chart + Format Chart
25

20

15

0 5 10 15 20 25 30 10

0
5 20 35

Data visualisation workshop - Excel November 2016

PwC 99
Effective visualisation
Choosing the correct graph

Revealing correlations
Correlation between two or more variables, especially when the variables are
not sequenced in time, is revealed clearly by a scatter plot.
Select Data -> X Y (Scatter)…
4

0
-4 -2 0 2 4
-1

-2

-3

-4
Data visualisation workshop - Excel November 2016
PwC 100
Effective visualisation
Choosing the correct graph

Displaying evolution
Bar charts although great for displaying univariate data, are a suboptimal
display for multivariate data.
Switching from bars to dots connected by lines is the best way to display
relation between two related variables, such as in evolutions through
time.
Select Data -> Column Chart & Line Chart with Markers…
250

200

150

100

50

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Data visualisation workshop - Excel November 2016

PwC 101
Effective visualisation
Choosing the correct graph

Displaying Evolution
• Column charts for univariate data
Select Data -> Column Chart

30

25

20

15

10

0
2013 2014 2015 2016
Windows Linux Mainframe

Data visualisation workshop - Excel November 2016

PwC 102
Effective visualisation
Choosing the correct graph

Displaying Evolution
• Line charts with Markers to indicate an evolution
Select Data -> Column Chart & Line Chart with Markers…

30

20

10

0
2013 2014 2015 2016
Windows Linux Mainframe
Data visualisation workshop - Excel November 2016
PwC 103
Introduction to Data Visualisation

 Pivot tables and other visualisation

features

Data visualisation workshop - Excel November 2016

PwC 104
Visualising in MS Excel
Pivot tables in Excel

Massive datasets
An often overlooked form of visualisation is tables. Yes tables!
When dealing with massive datasets the so called, Pivot Tables, can seriously
assist in aggregating and filtering the dataset in appropriate ways to facilitate
understanding of what is hidden within.
Furthermore the table itself can be used to display the data in different ways, or
to create interactive charts relative to the table.

Data visualisation workshop - Excel November 2016

PwC 105
Visualising in MS Excel
Obtaining the data

H2020 funding data from the EU ODP

From the EU ODP we will obtain data containing the organisations funded by
the European Union under the Horizon 2020 framework programme for
research and innovation “H2020” from 2014 to 2020. The data includes
institutions, countries, projects and amounts among other things.

Head to this link and download the file indicated below:

https://data.europa.eu/euodp/data/dataset/cordisH2020projects

Data visualisation workshop - Excel November 2016

PwC 106
Visualising in MS Excel
Pivot tables in Excel

Creating the pivot table

1. Select the whole dataset by clicking on the top-left corner
2. Click “Pivot Table”
3. Leave default options and click “OK”

Data visualisation workshop - Excel November 2016

PwC 107
Visualising in MS Excel
Pivot tables in Excel

Creating the pivot table

The Pivot Table field list appears. Here we
select and drag the fields we want included
into the different parts of the table.

Go ahead and select:

1. “country” for the Row
2. “Count of ecContribution” for Values
3. and “projectAcronym” for Filter

Data visualisation workshop - Excel November 2016

PwC 108
Visualising in MS Excel
Pivot tables in Excel

Refining the pivot table

The resulting table should look like this.
The problem here is that the table is
displaying the “Count” of contributions for
each country and not the total amount.

Data visualisation workshop - Excel November 2016

PwC 109
Visualising in MS Excel
Pivot tables in Excel

Refining the pivot table

Right click on any of the cells under “Count of ecContribution” and then
click “Value Field Settings” from the drop down menu. In the window that
appears select “Sum” and click “ok”.

Data visualisation workshop - Excel November 2016

PwC 110
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
Because the countries displayed are too many, lets limit our table to the 30 most funded
countries. Click on the arrow next to “Row Labels”, from the dropdown select “Value
Filters” and then click on “Top 10”. In the appearing menu select 30 and click “OK”.

Data visualisation workshop - Excel November 2016

PwC 111
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
That’s more like it! Now select all the data and format it as “Euro” to align it
and enhance readability.

Data visualisation workshop - Excel November 2016

PwC 112
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
That’s more like it! Now select all the data and format it as “Euro” to align it
and enhance readability.

Data visualisation workshop - Excel November 2016

PwC 113
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
Now let’s find out which of these 30 countries are funded more than the
average! Select all the data, click on “Conditional Formatting” , then
“Highlight Cells Rules” and click on “Greater Than”.

Data visualisation workshop - Excel November 2016

PwC 114
Visualising in MS Excel
Pivot tables in Excel

Refining the pivot table

In the menu that appears activate the first field and click on the cell containing
the value of the
“Grand Total” and
divide by 30 by typing
“/30” after it. In the
“with” field select
“Light Red Fill” and
click “OK”.

Data visualisation workshop - Excel November 2016

PwC 115
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
Now do the same again but click on “Conditional Formatting” , then
“Top/Bottom Cells Rules” and click on “Top 10 Items”. Customise the
fields so that the Top 1 item has a red border and a bold red typeface.

Data visualisation workshop - Excel November 2016

PwC 116
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
Hmm… It looks like a handful of countries are
getting funded above average.
Lets take a closer look on the difference with the
others!

Data visualisation workshop - Excel November 2016

PwC 117
Visualising in MS Excel
Pivot tables in Excel
Making a chart
Let’s make a chart to investigate. Click on the “See all charts” button at the
bottom right of “Charts” tab. Select “Column” and from there “Clustered
Column”.

Data visualisation workshop - Excel November 2016

PwC 118
Visualising in MS Excel
Pivot tables in Excel
Making a chart
Ok nice. Now lets give the graph a meaningful label, let’s widen it so the names
of all the countries fit in the horizontal axis and let’s remove the legend as all it
does is clutter the graph.

Data visualisation workshop - Excel November 2016

PwC 119
Visualising in MS Excel
Pivot tables in Excel
Making a chart
Now let’s sort our chart in descending order to get clearer picture. Click on the
filter symbol next to “Row Labels” in the pivot table, select “More Sort
Options”, configure the field to sort descending by “Sum of
ecContribution” and click “OK”.

Data visualisation workshop - Excel November 2016

PwC 120
Visualising in MS Excel
Pivot tables in Excel
Making a chart
Whoah! By sorting the pivot table our chart also got sorted. When a chart is
based on a pivot table any changes done to the table are transferred to the
chart as well.

Data visualisation workshop - Excel November 2016

PwC 121
Visualising in MS Excel
Pivot tables in Excel
Making a chart
For example, try using the filter we have added. Select a few
“projectAcronyms” and see how the chart updates to reflect the funding
among the countries, for those projects.

Data visualisation workshop - Excel November 2016

PwC 122
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
Now lets say we want to see the Top 3 projects for which each country gets the
most funding. Any ideas how we can do this?

Right click in the table

and click “Show Field
List”. From the menu
drag “projectAcronym”
from “Filter” to
“Rows”.

Data visualisation workshop - Excel November 2016

PwC 123
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
Now our pivot table is populated
with ALL the projects for the
countries displayed.
Click on the filter icon next to
“Row Labels” , go to “Value
Filters” and select “Top 10”.
From the menu that appears select
the top 3, like we did before.

Make sure you opened the “Value

Filters” menu for
“projectAcronym” and not for
“Country”

Data visualisation workshop - Excel November 2016

PwC 124
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
Don’t forget to remove the “Conditional formatting” as it makes no sense
anymore.

Data visualisation workshop - Excel November 2016

PwC 125
Visualising in MS Excel
Pivot tables in Excel
Refining the pivot table
Exercise:
The table below displays the “Countries” that take up 50% of the funding for
each of the Top 10 funded “Projects”. Furthermore it highlights each row
according to the “percentage” of each project’s funding each country receives.

Data visualisation workshop - Excel November 2016

PwC 126
Visualising in MS Excel
Statistical package and visualisation

Excel Data Analysis Toolpak

The Data Analysis toolpak allows the user to easily conduct descriptive
and exploratory statistics jobs on their data. It also offers a number of
relevant visualisations such as histograms, scatter plots and regression
charts.
Examples:
Moving average Correlation Matrix

Data visualisation workshop - Excel November 2016

PwC 127
Visualising in MS Excel
Visualising on maps

Excel Power Map

Power Map is a very easy to use add-on for Excel that offers integration
with Bing maps. It is usually pre-installed and only needs to be
activated from the options.
Power map takes data, that includes an area variable (Countries, Cities,
Regions, etc.), from a spreadsheet and automatically detects the
geolocation details of the places and applies the data on a map.

Example:
https://www.youtube.com/watch?v=_NPpISageUU

Data visualisation workshop - Excel November 2016

PwC 128
Visualising in MS Excel
Visualising on maps

Exporting your Visualisations

Remember that if you want to add your graph to another office document
(Word, PowerPoint) you can simply copy paste it and you retain all formatting
options in the new file.

Data visualisation workshop - Excel November 2016

PwC 129
Introduction to Data Visualisation

 Individual Projects

Data visualisation workshop - Excel November 2016

PwC 130
Visualising in MS Excel
Individual Projects

Project Instructions
For the next part we will be using the datasets you all brought to create
individual visualisations.

• Each participant will have to find an interesting insight hidden in the data
and visualise it accordingly to reveal it.
• We will be going around helping everyone with their project.
• In the end each participant will present their findings to the rest of us.
• Those of you who haven’t brought your own dataset, can head to the EU ODP
and search for one now.

Data visualisation workshop - Excel November 2016

PwC 131
Visualising in MS Excel
Choosing Datasets

EU ODP
Head through the ODP and select a
dataset.
https://data.europa.eu/euodp/en/data/
• Available as .xls or CSV
• Contains tabular data
• Contains numerical data
• Contains more than 2 variables
• Contains various granularity levels
• Contains breakdowns (e.g. country)
• Can show evolution (e.g. years)
• Can be combined with other data
(e.g. employment vs. population)
Data visualisation workshop - Excel November 2016
PwC 132
Disclaimers

This presentation has been carefully compiled by PwC, but no representation is made or warranty given
(either express or implied) as to the completeness or accuracy of the information it contains. PwC is
not liable for the information in this presentation or any decision or consequence based on the use of it.
PwC will not be liable for any damages arising from the use of the information contained in this
presentation. The information contained in this presentation is of a general nature and is solely for
guidance on matters of general interest. This presentation is not a substitute for professional advice on
any particular matter. No reader should act on the basis of any matter contained in this publication
without considering appropriate professional advice.

© 2016 PricewaterhouseCoopers. All rights reserved. “PricewaterhouseCoopers” refers to the

network of member firms of PricewaterhouseCoopers International Limited, each of which is a
separate and independent legal entity.