Statistical Analysis With Excel For Dummies (Notes: Bar Charts & Scatter Plots)

Bar Charts:  Excel's bar chart is a column chart laid on it's side.  This is the one that reverses the horizontal-vertical convention.  Here the vertical axis hold the independent variable, and it's referred to as the x-axis.  The horizontal axis is the y-axis and tracks the dependent variable.

Bar charts are used when you want to make a point about reaching a goal or about the inequities in attaining one.

The table below shows data on children's use of the internet at home for the year 2000. Percent means the percentage of children in each income group.

To put this data into a bar chart, follow these steps:

1.  Select the data that go into the sheet (A1 through B8)
2.  Select Insert | Recommended Charts and choose Clustered Bar.
3.  Modify the Chart.  Add chart title, axis titles, and bold the font of the axis titles and axis numbers.  The easiest way to bold the titles is to select an element and type Ctrl-B.

This is what the completed chart looks like:

ScatterPlots:  An important statistical technique called linear regression helps to determine the relationship between on variable X, and another variable Y.

The basis of the technique is a graph that shows individuals measured on both x and y.  The graph represents each individual as a point.  Because the points seem to scatter around the graph, the graph is called a scatterplot.

Suppose you're trying to find out how well a test of aptitude for sales predicts salespeople's productivity.  You administer the test to a sample of salespersons and tabulate how much money they make in commissions over a two month period.  Each person's pair or scores (test score and commissions) locates him or her within the scatter plot.  Here's the salesperson data:

To create the scatterplot graph, follow these steps:

1.  Select the data that go into the chart.  Select B14 through C33.  Including B13 creates the same chart but with an incorrect title.  The cells in column A are just placeholders that organize the data.

2,  /Select Insert | Recommended Charts.  Select the 1st option, Scatter chart.

3.  Modify the chart.  Add a new title and axis titles and embolden the titles.

Pictured below is the resulting graph:

Now right-click on any point in the scatterplot and choose "Add Trendline"  Select the Linear Radio button and click the bottom two check boxes (Display Equation on Chart and Display R-Squared Value on Chart).

The resulting trendline is really called a regression line.  The meaning of the equations will be discussed later.


Another application of a scatter chart is to create something like a line chart.  The conventional line chart works when the values on the x-axis are equally spaced.  Suppose your data looks like the values below (note the x-values are not equally spaced)

Select the data and then Insert | Recommended Charts and Excel automatically chooses "Scatter with Straight Lines and Markers"

This is the resulting chart: