Learn to Select Best Excel Charts for Data

Analysis and Reporting

Written by Himanshu Sharma, Founder of Optimize Smart

© Copyright 2020 Optimize Smart. All rights reserved.

No part of this publication can be reproduced or transmitted in any form or by any means, mechanical or
electronic including photocopying and recording or by any information storage and retrieval system, without
permission in writing from the author (except by a reviewer, who may quote brief passages and/or show brief
video clips in a review).

Disclaimer: Great efforts have been made to ensure the accuracy, timeliness and completeness of the contents
(written text, graphics) provided in this book. However neither the author nor Optimize Smart make any
guarantee /warranty of accuracy, timeliness and completeness of the information provided in this book or any
website this book link out to. No warranty/guarantee may be created or extended by sales representative or
written sales materials. Similarly neither author nor Optimize Smart make any guarantee/warranty that the
websites linked to this book are free from viruses, adware or other malicious programs. Websites listed in this
book may have changed or disappeared between when this book was written and when it is read. Neither the
author nor Optimize Smart will be liable for any losses or damages (including but not limited to: commercial,
special, incidental, consequential damages) arising from the use of the contents in this book. This book is for
information purpose only. The advice and strategies contained herein may not be suitable for every situation.
If professional assistance is required, the services of a competent consultant/agency should be sought. The fact
that an organization or website is referred to in this book, as a citation and/or potential source of further
information does not mean that the author or Optimize Smart endorses the information the organization or
website may provide and/or recommendations it may make.

Copyright Optimize Smart 2020 Page 2

About the author
Himanshu Sharma​ is the founder of​ ​Optimize Smart​, a UK based digital marketing
consultancy that specializes in​ ​analytics consultation​ and​ ​conversion optimization​.

Himanshu has more than ten years’ experience in SEO, PPC and web analytics. He holds a
bachelors’ degree in Internet Science and is a certified web analyst. He is both Google
Analytics and Google AdWords certified. He was nominated for a Digital Analytics
Association award for excellence. The Digital Analytics Association is a world-renowned
not-for-profit association that helps organizations overcome the challenges of data
acquisition and application.

Himanshu runs a popular blog on​ ​OptimizeSmart.com​ which gets more than a quarter of a
million visits a month from over one hundred countries. He is the author of four bestselling
books on conversion optimization, attribution modelling, and email analytics:

He runs a popular web analytics training course on optimizesmart.com. Through this

comprehensive course, you will learn and master all of the elements that go into extracting
insight from data and optimizing the online performance of your business for sales and
conversions.

Copyright Optimize Smart 2020 Page 3

Following are our most downloaded ebooks for career advancement:

#1​ ​62 Points Google Analytics Setup Checklist (50 pages)

WHAT'S INSIDE​: Learn to set up your Google Analytics account correctly and fast using this
62 point checklist. This is no ordinary checklist - it is a result of more than a decade of
experience in the analytics industry.

#2​ ​Google Tag Manager Data Layers (100+ pages)

WHAT'S INSIDE​: My step-by-step blueprint for getting started with data layers. Get the only
ebook on GTM data layers ever published. Learn the JavaScript behind it.

#3​ ​Learn to Read E-Commerce Reports in Google Analytics (100+ pages)

WHAT'S INSIDE:​ My step-by-step guide to reading both standard and enhanced

e-commerce reports in Google Analytics. E-commerce reports are the most valuable reports
in Google Analytics.

Copyright Optimize Smart 2020 Page 4

Index

#1 The importance of data visualization #11 When to use a number chart

#2 The anatomy of an Excel chart #12 When to use a gauge chart

#3 Most common data types #13 When to use a scatter chart

#4 When to use a clustered column chart #14 When to use a pie chart

#5 Breaking a clustered column chart #15 When to use a stacked area chart

#6 When to use a combination chart #16 When to use a histogram

#7 When to use a stacked column chart #17 When to use a Venn diagram

#8 When to use a 100% stacked column chart #18 Charts to avoid for reporting purposes

#19 Best practices for designing column and

#9 When to use a bar chart
line charts

#10 When to use a line chart

Copyright Optimize Smart 2020 Page 5

The importance of data visualization

Data visualization​ is the presentation of data (both qualitative and quantitative

data) in graphical format. Through data visualization you can easily:

1. Visualize data (make sense of data, especially big data)

2. Classify and categorize data
3. Find a relationship among data
4. Understand the composition of data
5. Understand the distribution of data
6. Understand the overlapping of data
7. Determine patterns and trends
8. Detect outliers and other anomalies in data
9. Predict future trends
10.Tell meaningful and engaging stories to decision-makers

Data presentation is a very important skill for an optimizer (marketer, analyst).

In fact, it is so valuable that LinkedIn lists it as one of the top skills that could
get you hired in 2017:

Copyright Optimize Smart 2020 Page 6

Source​: ​LinkedIn Unveils The Top Skills That Can Get You Hired In 2017

Excel charts are commonly used for data visualization and presentation. But
selecting the right Excel chart is always a challenge. If you use an incorrect
Excel chart for your analysis, you may misinterpret data and make the wrong
business and marketing decisions. If you use an incorrect Excel chart for your
presentation, then stakeholders may misinterpret your charts and take wrong
decisions.

Therefore selecting the right Excel chart is critically important.

The anatomy of an Excel chart

In order to read an Excel chart, it is important that you understand the various
components of the chart.

Consider the following data table in Excel:

Copyright Optimize Smart 2020 Page 7

This data table has got five ​variables​: ‘Month’, ‘Sales’, ‘Cost’, ‘Profit’ and ‘ROI’:

This data table is made up of ​categories​ and ​data series​:

Copyright Optimize Smart 2020 Page 8

Categories ​- Here the first category is ‘Jan’, the second category is ‘Feb’, the
third category is ‘Mar’ and so on.

Data series​ - A data series is a set of related data points.

Data point​ - A data point represents an individual unit of data. 10, 20, 30, 40,
etc are examples of data points. In the context of charts, a data point
represents a mark on a chart:

Copyright Optimize Smart 2020 Page 9

Consider the following Excel chart which is made from the data table
mentioned earlier:

This chart is made up of the following ​chart elements​:

Copyright Optimize Smart 2020 Page 10

 1. Primary Horizontal Axis:
2. Primary Vertical Axis
3. Secondary Vertical Axis
4. Primary Horizontal Axis Title
5. Primary Vertical Axis Title
6. Secondary Vertical Axis Title
7. Chart Title
8. Data Labels
9. Gridlines
10.Legend
11.Trendline

In Excel, categories are plotted on the horizontal axis and data series are
plotted on the vertical axis:

Copyright Optimize Smart 2020 Page 11

From the chart above, we can conclude the following:

● Months are plotted on the primary horizontal axis.

● Sales, cost and profit are plotted on the primary vertical axis.
● ROI is plotted on the secondary vertical axis.

In order to add, remove or edit a chart element in Excel (2013), follow the
steps below:

Step-1​: Open MS Excel and navigate to the spreadsheet which contains the
chart you want to edit.

Step-2​: Select the chart and then from the ''​Design'​ tab select '​Add Chart
Element​' drop-down menu:

Copyright Optimize Smart 2020 Page 12

You can also include '​Data table'​ in the chart above, by following the steps
below:

Step-1​: Click on the chart in Excel (2013)

Step-2​: Click on '​Add Chart Element​' > '​Data Table'​ > '​With legend Keys'​ :

Copyright Optimize Smart 2020 Page 13

You can now see your chart with data table:

Copyright Optimize Smart 2020 Page 14

The type of Excel chart you select for your analysis and reporting depends
upon the type of data you want to analyze and report and what you want to do
with data:

1. Visualize data (make sense of data especially big data)

2. Classify and categorize data
3. Find a relationship among data
4. Understand the composition of data
5. Understand the distribution of data
6. Understand the overlapping of data
7. Determine patterns and trends
8. Detect outliers and other anomalies in data
9. Predict future trends
10.Tell meaningful and engaging stories to decision-makers

Copyright Optimize Smart 2020 Page 15

Most common data types

Following are the most common data types that can be visualized:

● Quantitative data​ (also known as interval/ratio data) is the data that can
be measured. For example 10 customers, sales, ROI, weight, etc.
● Qualitative data​ is the data that can be classified/categorized but it can
not be measured. For example colors, satisfaction, rankings, etc.
● Discrete data​ – quantitative data with a finite number
of values/observations. For example 5 customers, 17 points, 12 steps,
etc.
● Continuous data​ - quantitative data with value / observation within a
range/interval. For example sales in the last year.
● Nominal data​ – qualitative data that can not be put into a meaningful
order (i.e. ranked). For example {Blue, Yellow, Green, Red, Black}
● Ordinal data​ – qualitative data that can be put into a meaningful order
(i.e. ranked). For example {Very Satisfied, Satisfied, Unsatisfied, very
unsatisfied} or {Strong dislike, dislike, neutral, like, strong like}

When to use a clustered column chart

#1 Use a ​clustered column chart​ when you want to compare two to four data
series. In other words, avoid using column charts if you have just one data
series to plot:

Copyright Optimize Smart 2020 Page 16

Alternatively, avoid creating a column chart that has got more than four data
series. For example, the following chart contains just five data series and it has
already started looking cluttered:

The chart below contains 11 data series and is very difficult to read and
understand:

Copyright Optimize Smart 2020 Page 17

If you want to create a column chart which contains a lot of data series then
you can ​try switching 'row' and 'column' of the chart​ ​and see whether it
makes any difference:

For example, after switching the row and column of the chart (with 11 data
series), it looks like the one below:

Copyright Optimize Smart 2020 Page 18

Now this chart, though still look cluttered, is much easier to read and
understand.

#2 Use a ​clustered column chart​ when the data series you want to compare
have the same unit of measurement. So avoid using column charts that
compare data series with a different unit of measurements. For example in the
chart below 'Sales' and 'ROI' have a different unit of measurement. The data
series 'Sales' is of type number. Whereas the data series 'ROI' is of type
percentage:

Copyright Optimize Smart 2020 Page 19

#3 Use a ​clustered column chart​ when the data series you want to compare
are of comparable sizes. So if the values of one data series dwarf the values of
the other data series then do not use the column chart. For example in the
chart below the values of the data series 'Website Traffic' completely dwarf the
values of the data series named 'Transactions':

Copyright Optimize Smart 2020 Page 20

#4 Use a clustered column chart when you want to show the maximum and
minimum values of each data series you want to compare.

#5 Use a clustered column chart when you want to focus on short term trends
(i.e. changes over days or weeks) and/or the order of categories is not
important.

Breaking a clustered column chart

The chart below contains 11 data series and is very difficult to read and
understand:

One method of making this chart easier to read and understand is by breaking
it into several smaller clustered column charts. For example, you can create
one column chart which just compares the sales performance of various

Copyright Optimize Smart 2020 Page 21

countries in January. Create another column chart which just compares the
sales performance of various countries in Feb and so on:

The rule of thumb is to ​avoid presenting too much data in one chart,
regardless of the chart type you use.

When to use a combination chart

A combination chart is simply a combination of two or more charts. For

example the combination of a column chart with a line chart. I use
combination charts a lot and I think you must know how to create them as they
are very useful. Following is a short video on creating a combination chart in
Excel:

#1 Use a combination chart when you want to compare two or more data
series that have different units of measurement:

Copyright Optimize Smart 2020 Page 22

#2 Use a combination chart when you want to compare two or more data
series that are not of comparable sizes:

Copyright Optimize Smart 2020 Page 23

When to use a stacked column chart

Use a stacked column chart when you want to compare data series along with
their composition and the overall size of each data series is important:

When to use a 100% stacked column chart

Use a ​100% stacked column chart​ when you want to compare data series
along with their composition but the overall size of each data series is not
important:

Copyright Optimize Smart 2020 Page 24

When to use a bar chart

#1 Use a bar chart whenever the axis labels are too long to fit in a column
chart:

Copyright Optimize Smart 2020 Page 25

When to use a line chart

#1 Use ​line charts​ when you want to show/focus on data trends (uptrend,
downtrend, short term trend, sideways trend, long term) especially long term
trends (i.e. changes over several months or years) between the values of the
data series:

#2 Use ​line charts​ when you have too many data points to plot and the use of
column or bar chart clutters the chart.

#3 Use a line chart instead of a clustered column chart if the order of

categories is important:

Copyright Optimize Smart 2020 Page 26

Copyright Optimize Smart 2020 Page 27
When to use a number chart?

If you want to visualize just one type of data and it contains a numeric value
which does not fall in any range/interval then use the number chart:

When to use a gauge chart (also known as

speedometer chart)

If you want to visualize just one type of data and it contains a numeric value
which falls in a range/interval then use the gauge chart (also known as
speedometer chart):

Copyright Optimize Smart 2020 Page 28

When to use a scatter chart

#1 Consider using a scatter chart when you want to analyze and report the
relationship/correlation​ between two variables:

From this chart, we can conclude that the relationship between the two
variables ('x' and 'y') is linear. What that means, as the value of the variable 'x'
increases there is a corresponding increase in the value of the variable 'y'.

#2 Create a scatter chart only when there are ten or more data points on the
horizontal axis. The more data points the better it is for a scatter chart.
Conversely, just a few data points (like five or six data points) are not good
enough for creating a scatter chart.

Copyright Optimize Smart 2020 Page 29

#3 Use a scatter chart when you want to show ‘why’. For example: why
revenue is correlated with average order value or why conversion rate is
correlated with the number of transactions.

When to use a pie chart

#1 Use a ​pie chart​ when you want to show a 100% composition of data. In
other words, the various pie slices you use must add up to 100%. What that
means is, do not create a pie chart where the various pie slices do not
represent parts of the whole pie. For example, the following pie chart is not a
good representation of data composition as the two pie slices add up to 82%
and not 100%:

#2 Use a pie chart to show the composition of data only when you have got
one data series and less than five categories to plot. For example, the following
pie chart shows the breakdown of website traffic sources in the last month:

Copyright Optimize Smart 2020 Page 30

Here I have got only four categories (search traffic, referral traffic, direct traffic,
and campaigns) to plot. So a pie chart is ideal to show the breakdown.
However, if there were more than four categories to plot, like eight or ten
categories, then the pie chart would have become cluttered and hard to read.
For example, the following pie chart looks cluttered because it has got too
many categories:

Copyright Optimize Smart 2020 Page 31

#3 Use a pie chart to show data composition only when the pie slices are of
comparable sizes. In other words, do not use a pie chart if the size of one pie
slice completely dwarfs the size of the other pie slice(s):

#4 Order your pie slices in such a way that as you look clockwise from top to
bottom, the biggest pie slice comes first followed by the second biggest pie
slice and so on. This makes the pie chart easy to read:

These pie charts are made from the following data:

Copyright Optimize Smart 2020 Page 32

In order to create a pie chart where the biggest pie slice comes first followed
by the second biggest pie slice and so on, I have sorted the data in decreasing
order (from largest to smallest).

When to use a stacked area chart

Use a stacked area chart when you want to show the trend of composition and
emphasize the magnitude of change over time. For example following stacked
area chart shows the breakdown of website traffic:

Copyright Optimize Smart 2020 Page 33

When to use a histogram

Use a histogram to show frequency distribution for quantitative data:

Note:​ You would need to install the ​Analysis ToolPak​ in order to create a
histogram in Excel.

Copyright Optimize Smart 2020 Page 34

When to use a Venn diagram

Use a Venn diagram to show the overlapping of data. The multi-channel

conversion visualizer chart used in Google Analytics to visualize ​multi-channel
attribution​ is actually a Venn diagram:

In the context of web analytics, we can use a Venn diagram to determine

whether or not a website has got an attribution problem. If there is little to no
overlap between two or more marketing channels then the website doesn't
have attribution issues.

If there is a good amount of overlap then the website has got attribution issues
and you should seriously consider taking multi-channel attribution into account
while analyzing and interpreting the performance of marketing campaigns.

Copyright Optimize Smart 2020 Page 35

To learn more about attribution modelling read this article: ​Beginners Guide to
Google Analytics Attribution Modelling

Another great use of Venn diagrams is in ​visualizing the back link​ overlaps
between websites:

The tool that I have used to create this Venn diagram is known as ​Venny​.

Note​: You can create a Venn diagram in Excel. Check out this tutorial on the
Microsoft Office website: ​Create a Venn diagram

Copyright Optimize Smart 2020 Page 36

Charts to avoid for reporting purposes

Throughout this book, I have talked about the charts that should be used. But
there are some charts which should be avoided for reporting purposes
unless your target audience is as data-savvy as you.

Following are those charts:

Treemap

Copyright Optimize Smart 2020 Page 37

Waterfall chart

Radar chart

Copyright Optimize Smart 2020 Page 38

Bubble chart

The reason you should be avoiding reporting data via these charts to your
clients is simple. The majority of people have no idea what you are trying to
communicate via these charts. Use these charts only when your target
audience is as data-savvy as you.

​Best practices for designing column and line

charts

#1 Start the 'Y' axis value at zero

When you do not start the 'Y' axis value of a chart at zero, the chart does not
accurately reflect the size of the variables (in case of column charts) and trend
(in case of line charts).

Copyright Optimize Smart 2020 Page 39

For example, the following column chart amplify changes because the 'y' axis
value starts at 440 instead of 0:

Following is the correct column chart:

Copyright Optimize Smart 2020 Page 40

Another example. The following line chart amplifies the growth of Facebook
fans because the 'y' axis value start at 2500 instead of 0:

Following is the correct line chart:

Copyright Optimize Smart 2020 Page 41

#2 Do not use line chart (to create trends) if you have less
than eight data points
When you create a line chart with a few data points, the trend that you see can
be very misleading. For example, the following line chart just contain two data
points and as a result, it makes the growth look phenomenal:

Copyright Optimize Smart 2020 Page 42

For a line chart, the more data points the better.

#3 Do not hide the scale on the 'y' axis of a column/line

chart

When you hide the scale of the 'y' axis, your chart won't accurately reflect the
size of the variables (in case of column charts) and trend (in case of line

Copyright Optimize Smart 2020 Page 43

charts). Without any scale on the y-axis, there is no way of knowing where the
y-axis starts. When you use such charts it creates doubt on your analysis.

#4 Add context to your chart

Different people analyze and interpret the same chart differently. It all
depends upon the ​context​ in which they analyze and interpret the chart. No
matter what chart you select, some people will always find a way to
misinterpret your chart. Therefore it is critical that you provide context with
your chart in the form of written commentary and describe exactly the intent
of your chart.

First present the context, then the insight and then the chart to support your
insight. In this way, you are giving clues to your chart reader regarding how to
read your chart. For example:

Copyright Optimize Smart 2020 Page 44

Copyright Optimize Smart 2020 Page 45
Do you know the difference between
Digital Analytics and Google
Analytics?
99.99% of course creators themselves don’t know the difference
between digital analytics, Google Analytics (GA) and Google Tag
Manager (GTM).

So they are teaching GA and GTM in the name of teaching digital analytics.

They just copy each other. Monkey see, monkey do.

But digital analytics is not about GA, GTM.

It is about analyzing and interpreting data, setting up goals, strategies, and KPIs.

It’s about creating a strategic roadmap for your business.

Digital analytics is the core skill. Google Analytics is just a tool used
to implement digital analytics.

You can also implement digital analytics via other tools like Adobe Analytics,
Kissmetrics, etc.

Using Google Analytics without a good understanding of digital analytics is like

driving around in a car, in a big city, without understanding the traffic rules and road
signs.

You are either likely to end up somewhere other than your destination or you get
involved in an accident.

Copyright Optimize Smart 2020 Page 46

You learn data analysis and interpretation from digital analytics and
not from Google Analytics.

The direction in which your analysis will move will determine the direction in which
your marketing campaigns and eventually your company will move to get the highest
possible return on investment.

You get that direction from digital analytics and not from Google Analytics.

You learn to set up KPIs, strategies and measurement framework for

your business from digital analytics and not from Google Analytics.

So if you are taking a course only on Google Analytics, you are learning to use one
of the tools of digital analytics. You are not learning digital analytics itself.

Since any person can learn to use Google Analytics in a couple of weeks, you do not
get any competitive advantage in the marketplace just by knowing GA.

You need to know a lot more than GA in order to work in digital

analytics and marketing field.

So what I have done, if you are interested, is put together a completely free training
that will teach you exactly how I have been able to leverage digital analytics to
generate floods of news sales and customers and how you can literally copy what I
have done to get similar results.

Copyright Optimize Smart 2020 Page 47

Here’s What We’re Going To Cover…
#1 - Why digital analytics is the key to online business success.

#2 - The number 1 reason why most marketers are not able to scale
their advertising and maximize sales.

#3 - Why Google and Facebook ads don’t work for most businesses
& how to make them work.

#4 - Why you won’t get any competitive advantage in the

marketplace just by knowing Google Analytics.

#5 - The number 1 reason why conversion optimization is not

working for your business.

#6 - How to advertise on any marketing platform for FREE with an

unlimited budget.

#7 - How to learn and master digital analytics and conversion

optimization in record time.

Reserve my seat now >>

Copyright Optimize Smart 2020 Page 48