Which chart type provides the best visual display of the relationship between two numeric variables Excel?

Its an excellent way to represent large amounts of information into readable image formats, which

• Clearly highlight the points like to make
• Decision making
• Make Actionable
• Shows direction

How many types are there to represent above?

• Comparison
• Composition 
• Relationship 
• Distribution 
• Trends

Type of Charts to use for representation?

There are many different chart types available, and sometimes the hardest part is deciding which chart type is best for the need.

1. Bar Graph 
2. Line Graph
3. Area Chart
4. Mekko Chart
5. Pie Chart
6. Scatter Plot Chart
7. Bubble Chart
8. Waterfall Chart
9. Funnel Chart
10. Combo Chart
11. Heat Map
12. Waterfall

Out of above mostly used charts are: Bar, Line, Pie, Scatter

Graphs make it easier to see patterns in data, where Tables are good for looking up information.

Most Often Used for Time Series: - Column (Regular, Clustered, and Stacked),  Line, Area

Most Often Used for a Single Point in Time:  

• Bar (Regular, Clustered, and Stacked),
• Pie (Regular, Exploded, and Bar of Pie), Scatter, Bubble

To Show Lot of Information in a Very Small Space: Sparkline, Bullet

Each Chart Desc beyond above list :

1. Bar chart – This is the most basic chart type. Each x-axis value corresponds to a bar. The bar height corresponds to its numerical y-axis value.

• Vertical Bar Charts: best for comparing means or percentages between 2 to 7 different groups,making its cross analysis with happiness perfect for a vertical bar chart. when comparing categories that are mutually exclusive.
• Horizontal Bar Charts: when comparing categories that are mutually exclusive. more than 7 categories of candy were measured independently and are being compared to one another.

2. Pie chart – Shows the relation between a single (primary dimension) and a single expression. A variant chart type is drawn when a secondary dimension is introduced. If more expressions than one are enabled in the Chart properties: Expressions page, the first in the expression list will be displayed. To switch expression use the Promote/Demote buttons in the Expressions property page. Normally best used to illustrate a sample break down in a single dimension, i.e. To show differences within groups based on one variable

3. Combo chart – The combo chart allows the combination of the features of the bar chart with those of the line chart. One expression will be displayed by lines and/or symbols, the other as bars.

4. Scatter chart – The scatter chart plots data points representing combinations of expressions, iterated over one or several dimensions. Both axes are continuous, representing one expression each. Normally used to depict how different objects settle around a mean based on 2 to 3 different dimensions. Allows for quick and easy comparisons between competing variables.

• X-Y Plot: Used to determine relationships between the two different things. The x-axis is used to measure one event (or variable) and the y-axis is used to measure the other. If both variables increase at the same time, they have a positive relationship. If one variable decreases while the other increases, they have a negative relationship. Sometimes the variables don't follow any pattern and have no relationship.

5. Line chart – The line chart is essentially defined in the same way as the bar chart. Instead of using bars the data can be presented as lines between value points, as value points only or as both lines and value points. Normally used to illustrate trends over time, to measure the long term progression of sales. also be used to compare two different variables over time. Also used to track changes over short and long periods of time. When smaller changes exist, line graphs are better to use than bar graphs. Line graphs can also be used to compare changes over the same period of time for more than one group.

• Area Graph: Good to use when you are tracking the changes in two or more related groups that make up one whole category

6. Radar chart – The radar chart is a variant of the line chart where the x-axis is plotted in a circle around the chart, resulting in a projection reminiscent of a radar screen or a spider’s web.

7. Grid chart – The grid chart is a variant of the scatter chart that plots dimension values on the axes and uses an expression to determine the plot symbol. It can also show a third dimension in the form of small pie charts as plot symbols.

8. Gauge chart – Gauge charts are used to display the value of a single expression, lacking dimensions.

9. Block chart – The block chart shows the relation between expression values as blocks of varying area. It uses a single expression and up to three dimensions, with each dimension block further divided into sub-blocks. The total area of the block chart always equals 100% of the possible expression values.

10. Funnel chart – The funnel chart is typically used for showing data in flows and processes. From a display standpoint it is related to the pie chart. The chart may be shown with either segment height/width or segment area proportional to data. It is also possible to draw the chart with equal segment heights/widths without regards to data points.

11. Pivot table – The pivot table presents dimensions and expressions in table form. There is no formal limit to the number of dimensions or expressions possible. A pivot table can be defined without expressions, generating a tree view for navigating the dimension levels.

12. Straight table – The straight table differs from the pivot table in that it cannot display subtotals and that the grouping of dimensions is shown in record form so that each row of the table contains field and expression values.

13. Mekko chart – Mekko charts present data using variable width bars. They can display up to three levels of data in a two-dimensional chart. Mekko charts are useful in such areas as market analysis.

14. Waterfall charts - used to explain changes in Performance/Revenue. i.e. explain the change in earnings from one year to the next. One can use for many other uses. For example

• Explaining how operating costs changed from one time period to a next.
• Steps from Cost to Company to Revenue
• Units in stock to Salable unit

When to use which chart? Chart usage:

This chapter is from the book 

Several chart types in Excel lend themselves beautifully to the visual representation of numeric variables. This book relies heavily on charts of that type because most of us find statistical concepts that are difficult to grasp in the abstract are much clearer when they’re illustrated in charts.

Charting Two Variables

Earlier this chapter briefly discussed two chart types that use a category variable on one axis and a numeric variable on the other: Column charts and Bar charts. There are other, similar types of charts, such as Line charts, that are useful for analyzing a numeric variable in terms of different categories—especially time categories such as months, quarters, and years. However, one particular type of Excel chart, called an XY (Scatter) chart, shows the relationship between exactly two numeric variables. Figure 1.8 provides an example.

Figure 1.8 In an XY (Scatter) chart, both the horizontal and vertical axes are value axes.

The markers in an XY chart show where a particular person or object falls on each of two numeric variables. The overall pattern of the markers can tell you quite a bit about the relationship between the variables, as expressed in each record’s measurement. Chapter 4, “How Variables Move Jointly: Correlation,” goes into considerable detail about this sort of relationship.

In Figure 1.8, for example, you can see the relationship between a person’s height and weight: Generally, the greater the height, the greater the weight. The relationship between the two variables differs fundamentally from those discussed earlier in this chapter, where the emphasis is placed on the sum or average of a numeric variable, such as number of vehicles, according to the category of a nominal variable, such as make of car.

However, when you are interested in the way that two numeric variables are related, you are asking a different sort of question, and you use a different sort of statistical analysis. How are height and weight related, and how strong is the relationship? Does the amount of time spent on a cell phone correspond in some way to the likelihood of contracting cancer? Do people who spend more years in school eventually make more money? (And if so, does that relationship hold all the way from elementary school to post-graduate degrees?) This is another major class of empirical research and statistical analysis: the investigation of how different variables change together—or, in statistical jargon, how they covary.

Excel’s XY charts can tell you a considerable amount about how two numeric variables are related. Figure 1.9 adds a trendline to the XY chart in Figure 1.8.

Figure 1.9 A trendline graphs a numeric relationship, which is almost never an accurate way to depict reality.

The diagonal line you see in Figure 1.9 is a trendline. It is an idealized representation of the relationship between men’s height and weight, at least as determined from the sample of 17 men whose measures are charted in the figure. The trendline is based on this formula:

• Weight = 5.2 * Height – 152

Excel calculates the formula based on what’s called the least squares criterion. You’ll see much more about this in Chapter 4.

Suppose that you picked several—say, 20—different values for height in inches, plugged them into that formula, and then used the formula to calculate the resulting weight. If you now created an Excel XY chart that shows those values of height and weight, you would get a chart that shows a straight line similar to the trendline you see in Figure 1.9.

That’s because arithmetic is nice and clean and doesn’t involve errors. The formula applies arithmetic which results in a set of predicted weights that, plotted against height on a chart, describe a straight line. Reality, though, is seldom free from errors. Some people weigh more than a formula thinks they should, given their height. Other people weigh less. (Statistical analysis terms these discrepancies errors or deviations.) The result is that if you chart the measures you get from actual people instead of from a mechanical formula, you’re going to get a set of data that looks like the somewhat scattered markers in Figures 1.8 and 1.9.

Reality is messy, and the statistician’s approach to cleaning it up is to seek to identify regular patterns lurking behind the real-world measures. If those real-world measures don’t precisely fit the pattern that has been identified, there are several explanations, including these (and they’re not mutually exclusive):

• People and things just don’t always conform to ideal mathematical patterns. Deal with it.
• There may be some problem with the way the measures were taken. Get better yardsticks.
• Some other, unexamined variable may cause the deviations from the underlying pattern. Come up with some more theory, and then carry out more research.