# How to select best Excel Charts for Data Analysis & Reporting

**Index**

#1 Type of data (Qualitative and Quantitative) |
#11 Composition of Data |

#2 How you can summarize Qualitative data? |
#12 Pie Chart |

#3 How you can’t summarize Qualitative data? |
#13 Stacked Column Chart |

#4 How you can summarize Quantitative data? |
#14 Stacked Area Charts |

#5 Relationship among data |
#15 Distribution of Data |

#6 Comparison of Data |
#16 Overlapping of Data |

#7 Column Charts |
#17 Anatomy of an Excel Chart |

#8 Bar Charts |
#18 Add context to your Chart |

#9 Line Charts |
#19 Charts to Avoid for Reporting Purpose |

#10 Combination Charts |

Do you always struggle with the type of Excel charts to use in your analytics reports?

The type of excel chart you select for your analysis and reporting depends upon what you are going to analyse and report.

We create charts to display qualitative and quantitative data in a graphical format and to make it easy to understand either one, any or all of the following:

**Type of data (Qualitative and Quantitative)****Relationship among data****Comparison of data****Composition of data****Distribution of data****Overlapping of data**

## Type of data (Qualitative and Quantitative)

Qualitative datais the data that can be classified/categorized but it can’t be measured. For example: colours, satisfaction, rankings etc.

Quantitative data(also known as interval/ratio data) is the data that can be measured. For example 5 customers, 12 steps, conversion rate , height etc

**Types of qualitative data** – Nominal data and ordinal data

Nominal data– qualitative data that can’t 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}

**Types of quantitative data** – discrete data and Continuous Data

Discrete Data– quantitative data with distinct values / observations. For example: 5 customers, 17 points, 12 steps etc.

Continuous Data– quantitative data with any value / observation within a finite or infinite interval. For example: conversion rate, visits, pageviews, bounce rate, height, weight etc.

## How you can summarize Qualitative data?

Through frequency table:

Through Pie Chart:

Through Column chart

**Quick announcement about my new book:**

**Read first few chapters of this book for free here.**

## How you can’t summarize Qualitative data?

You can’t summarize qualitative data by calculating mean (or average).

For example;

- Very Satisfied: 40%
- Satisfied: 30%
- Unsatisfied: 20%
- very unsatisfied: 10%

If you calculate the average here than average satisfaction is 25% which doesn’t make any sense.

Look at this calculation this way, when you are calculating the average of a qualitative data, you are actually trying to compute this:

Average satisfaction = (Very Satisfied + Satisfied + Unsatisfied + Very Unsatisfied)/4

Similarly, you can’t summarize qualitative data through line charts and histogram.

## How you can summarize Quantitative data?

You can summarize quantitative data through mean, median, mode, standard deviation etc. You can summarize quantitative data through column chart, bar chart, line chart, Histogram etc.

## Relationship among data

To understand relationship, you need to understand connection/correlation between two or more **data points**.

- A data point/category is a mark on a graph/chart which corresponds to a piece of data.
- The data that the mark represents is also called a
data point.- The value associated with a data point is called
data value.- A variable can store one or more data values which we can represent graphically via a chart.
- A set of related data points is known as
data series.

For example following chart has got 15 data points and two data series:

Here we are trying to understand the relationship between two variables named ‘average order value’ and ‘sales’.

One of the best excel chart to represent this relationship is **Scatter Chart**.

We can see from the chart that the relationship between the two variables is linear which means as the value of AOV increases there is a corresponding increase in the value of sales.

Related Post:Introducing Predictive Marketing – The next stage of Business Optimization

**Consider using Scatter Chart when:**

1. Analysing and reporting relationship/correlation between two variables.

2. When you want to show ‘

why’. For example: why revenue is correlated with average order value or why conversion rate is correlated with number of transactions.3. When there are more than 10 data points on the horizontal axis. More the data points the better it is for a scatter chart. Conversely few data points (like 5 or 6 data points) are not good enough for creating a scatter chart.

4. There are two variables that depend on each other.

## Comparison of Data

For comparing two or more variables the best charts are **column charts**, **bar charts**, **line chart **and **combination chart.**

## Column Charts

Column charts are one of the most widely used charts and are a greatly used to compare variables.

In column charts categories are plotted along the horizontal axis and values are plotted along the vertical axis.

For example following column chart compares the performance of number of branded and non-branded keywords which generated traffic on the website between July and November.

Here the categories (number of branded keywords, number of non-branded keywords) are plotted on horizontal axis and their values are plotted on the vertical axis.

Following are the examples of some columns charts that you should avoid creating:

**Consider using Column charts when:**

1. You are comparing two or more variables which have same unit of measurement and are of comparable sizes. So that the values of one variable does not dwarf the values of the other variables.

2. When you want to show ‘

how much’. For example how much organic visits have changed over three months. So column charts can be used to show data changes over a period of time.3. The number of categories to plot is less than 5

4. You want to show maximum and minimum values.

## Bar Charts

Bar charts are similar to column charts except that in the bar charts values are plotted horizontally and categories are plotted vertically.

For example following bar chart shows the social media performance of various insurance websites:

As you can see here the categories (website names) are plotted on the vertical axis and values are plotted on the horizontal axis.

**Consider using Bar charts when**:

1. The axis labels are too long to fit in a column chart

2. The number of categories to plot is between 5 and 8.

3. You are comparing two or more variables which have

same unit of measurement and are of comparable sizes.4. When you want to show ‘

how much’.5. You want to show maximum and minimum values.

## Line Charts

Line charts are best to show data trends esp. over a long period of time. For example following line chart shows the performance of organic and PPC traffic between July and March.

Another Example:

Here I am visualizing the link growth/velocity of BBC and CNN website in the last one year through Majestic Back Links History Tool. I have used the line chart because they are too many data points to plot and because I want to show data trend over a long period of time.

Another example:

Line charts are best to show data trends: uptrend, downtrend, short term trend, sideways trend and long term trend.

**Consider using Line charts when**:

1. You want to show data trends over a long period of time.

2. The number of categories to plot is more than 8.

3. You have too many data points to plot and the column or bar chart clutters the data.

4. When you want to show ‘

how much’has changed over a period of time.

## Combination Charts

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

For example combination of column chart with line chart.

Consider using combination chart when you are comparing two or more variables that have different unit of measurement and/or are of different sizes. For example:

Here both of the combination charts are presenting the same data.

The only difference is that,in the first chart I combined the column chart with a line chart. In the second chart I just plotted the ‘bounce rate’ on ‘secondary axes’.

I use combination charts a lot in my report and you must know how to create them as they are very useful.

**Following is a short video on creating a combination chart in excel**:

**Note:** By using combination chart you can ensure that the value of one variable does not dwarf the value of other variable(s).

## Composition of Data

If you want to show the breakdown of data into its constituents then consider using Pie Chart, Stacked Column Chart or Stacked Area Chart.

## Pie Chart

Pie charts are most useful when you have only one data series, less than 5 categories/data points to plot and you want to show composition of data.

For example following pie chart shows the breakdown of my website traffic sources in the last one month:

Here I have got only 4 categories (search traffic, referral traffic, direct traffic and campaigns) to plot. So pie chart is ideal to show the breakdown.

If there were more than 4 categories to plot like 8 or 10 categories then pie chart becomes cluttered and hard to read.

Another example:

Here we have got only two categories (new visitors, returning visitors) to plot. So the use of Pie chart is ideal here to show data composition.

Following are some pie charts that you should avoid creating:

**Consider using Pie charts when**:

1. You want to show the breakdown of data into its constituents.

2. You have only one data series.

3. You have less than 5 data points to plot.

4. The data points represent the parts of the whole pie.

5. The constituents are of comparable sizes. So that value of one constituent does not dwarf the values of other constituents.

## Stacked Column Chart

Stacked Column charts are most useful when you have 5 to 8 categories/data points to plot and you want to show composition of data.

For example following stacked column chart shows the breakdown of website traffic in terms of new and returning visits in the last one month:

**Consider using Stacked Column charts when**:

- You want to show the breakdown of data into its constituents.
- You have between 5 to 8 data points to plot.
- The data points represent the parts of the whole composition.
- The constituents are of comparable sizes. So that value of one constituent does not dwarf the values of other constituents.

## Stacked Area Charts

Stacked Area charts are most useful when you have more than 8 categories/data points to plot and you want to show the **trend of composition**.

For example following stacked area chart shows the breakdown of website traffic:

**Consider using Stacked Area charts when**:

**Other Posts you may find useful**:

## Quick announcement about my new book on Web Analytics and Conversion Optimization

The role of maths and statistics in the world of web analytics is not clear to many marketers and web analysts. Not many talk or write about the usage of statistics and data science in conversion optimization. That is why I have written this book Maths and Stats for Web Analytics and Conversion Optimization to fill this knowledge gap.This expert guide will teach you, how to leverage the knowledge of maths and statistics in order to accurately interpret the web analytics data and take business and marketing decisions which can quickly improve the bottom-line of your online business. Read first few chapters of this book for free here.

Follow @analyticsnerd