﻿ Five Advanced Excel Charts and Graphs - Optimize Smart

# Five Advanced Excel Charts and Graphs

Last Updated: January 10, 2022

[show]

Following are the five advanced Excel charts and graphs:

1. Actual vs. target chart
2. Bullet chart
3. Funnel chart
4. Scatter chart
5. Histogram

## 1. Actual vs. target chart

The ‘Actual vs. target’ chart is a combination chart that requires some formatting. You can’t insert this chart straightaway into your Excel spreadsheet.

Use this chart when you have got multiple goals and you want to show progress towards each goal. The chart below shows whether target sales were achieved in each quarter:

This chart is based on the following data table:

## 2. Bullet chart

A Bullet chart is a combination chart (though it looks like a single bar chart) which is used to show progress towards a single goal using a range of predefined qualitative and quantitative parameters.

You can’t insert this chart straightaway into your Excel spreadsheet and it is also quite tricky to create. If you have multiple goals and you want to show progress towards each goal then use the ‘Actual vs. target’ chart.

But if you have only one goal and you want to show progress towards this goal (by using both qualitative and quantitative data) then use the bullet chart.

A bullet chart can be a vertical bar chart or horizontal bar chart. The choice of vertical or horizontal alignment depends on the space available to use for data visualization.

The chart below shows the performance of sales in Quarter 4:

This chart is based on the following data table:

If the actual sales are between \$0 to \$240,000 then sales performance is considered ‘Poor’.

If the actual sales are between \$240,000 to \$300,000 (\$240,000 + \$60,000) then it is considered ‘Fair’.

If the actual sales are between \$300,000 to \$360,000 (\$240,000 + \$60,000 + \$60,000) then it is considered ‘Good’.

If the actual sales are between \$360,000 to \$400,000 (\$240,000 + \$60,000 + \$60,000 + \$40,000) then it is considered ‘Excellent’.

## 3. Funnel chart

As the name suggests the funnel chart is used for funnel visualization. It is perfect for showing lead funnel and sales funnels. This chart is available in MS Excel (2016 and above). You just need to select your data table and then insert the ‘Funnel’ chart.

The chart below shows different stages of the purchase funnel and how user moved from one stage to the next:

This chart is based on the following data table:

## 4. Scatter chart

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’.

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.

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.

## 5. 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.

## "How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."

Here’s what we’re going to cover in this training…

#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.

#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.

​#7 How to learn and master digital analytics and conversion optimization in record time.

## My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

### Himanshu Sharma

• Founder, OptimizeSmart.com
• Over 15 years of experience in digital analytics and marketing
• Author of four best-selling books on digital analytics and conversion optimization
• Nominated for Digital Analytics Association Awards for Excellence
• Runs one of the most popular blogs in the world on digital analytics