Presenting Data in Charts Using Excel
The first step in constructing a graph of statistical data is to construct a frequency distribution.  Let's continue with the frequency distributions that were constructed in the previous handout.

Graphs of Numerical (Quantitative) Data
Following is the final version of the frequency distribution for the numerical data from the previous handout.

Starting with this spreadsheet, we will proceed to construct several charts. 
1. For some of the graphs, it will be necessary to add an extra interval (of equal width) prior to the first interval of the frequency distribution.  To do this, click on the row label for row 2.  This will select the entire row.  Click Insert, Row.  This inserts a blank row above row 2.  In this row, enter the extra interval "11 to under 15.99" which has  frequency, relative frequency, and percentage of 0.
2. For some of the graphs, it will be necessary to use the interval midpoints.  To compute them, you need to insert a blank column between columns D and E.  To do this, click the column label for column E.  Click Insert, Column.  A blank column will be inserted to the left of column E.  In cell E2, type the following formula:  =(B2+D2)/2  and press Enter.  Copy this formula to cells E3 through E9 and label column E "Midpoints." 

Graphs can be constructed in Excel using the ChartWizard tool (see "Introduction to Charts using Excel" handout).  The ChartWizard is a four-step procedure for constructing a graph.  Before clicking on the ChartWizard button, first select the data from your spreadsheet that is to be depicted in the graph. 

1. Histogram
A histogram is called a "Column Chart" in Excel.  A histogram can be constructed using frequency, relative frequency, or percentage.  To construct a histogram for the frequency distribution, first select the range of cells containing the frequencies (F2:F9).  For a relative frequency histogram, you would select G2:G9; for a percentage histogram, you would select H2:H9.  Click the ChartWizard button on the standard toolbar.
Step 1 (Type):  Select chart type (click Column) and then select chart sub-type (click Clustered column).  Click Next>.
Step 2 (Source Data): The cell range  that you highlighted should be showing in the Data range box and the arrangement of the data series is in Columns.  Click the Series tab at the top of the window.  Click in the box labeled Category (X) axis labels.  Click the red marker (this will send you back to your worksheet) and select cells E2:E9 (cells containing the midpoints).  Click the red marker to return to the ChartWizard.  Click Next>.
Step 3 (Options): 
1. Select the Titles tab.  Enter the chart title, X-axis label, and Y-axis label.
2. Select the Legend tab.  Click the check in the Show legend box to remove it.
3. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.  If you want the graph to appear in the worksheet with your frequency distribution, click As an object in and select the worksheet containing your frequency distribution.  Click Finish.
    To clean up the graph and make it look more acceptable according to the conventions of the textbook, perform the following:
Click the right mouse button while pointing at any one of the columns.  Click Format Data Series, Options, and change the Gap Width to 0.  Click OK.  This removes the gaps between the columns.
    If you want to change the color scheme or change the appearance of the graph, click the right mouse button on the element you want to change and then click Format for that object.  Choices will be provided for Color, Fill Pattern, Texture, Gradient, Font, etc.

2. Polygon
A polygon is a "Line Chart" in Excel.  A polygon can be constructed using frequency, relative frequency, or percentage.  To construct a frequency polygon for the frequency distribution, first select the range of cells containing the frequencies (F2:F9).  For a relative frequency polygon, you would select G2:G9; for a percentage polygon, you would select H2:H9.  Click the ChartWizard button on the standard toolbar.
Step 1 (Type):  Select chart type (click Line) and then select chart sub-type (click Line with markers).  Click Next>.
Step 2 (Source Data): The cell range that you highlighted should be showing in the Data range box and the arrangement of the data series is in Columns.  Click the Series tab at the top of the window.  Click in the box labeled Category (X) axis labels.  Click the red marker (this will send you back to your worksheet) and select cells E2:E9 (cells containing the midpoints).  Click the red marker to return to the ChartWizard.  Click Next>.
Step 3 (Options): 
1. Select the Titles tab.  Enter the chart title, X-axis label, and Y-axis label.
2. Select the Legend tab.  Click the check in the Show legend box to remove it.
3. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.  If you want the graph to appear in the worksheet with your frequency distribution, click As an object in and select the worksheet containing your frequency distribution.  Click Finish.

3. Cumulative Polygon (Ogive)
An ogive graph is a "Line Chart" in Excel.  An ogive can be constructed using cumulative frequency, cumulative relative frequency, or cumulative percentage.  To construct a cumulative frequency ogive for the frequency distribution, first select the range of cells containing the cumulative frequencies (I3:I9).  To construct a cumulative percentage ogive, you would select J3:J9.  Click the ChartWizard button on the standard toolbar.
Step 1 (Type):  Select chart type (click Line) and then select chart sub-type (click Line without markers).  Click Next>.
Step 2 (Source Data): The cell range that you highlighted should be showing in the Data range box and the arrangement of the data series is in Columns.  Click the Series tab at the top of the window.  Click in the box labeled Category (X) axis labels.  Click the red marker (this will send you back to your worksheet) and select cells B3:B9. These cells contain the interval lower limits.  Click the red marker to return to the ChartWizard.  Click Next>.
Step 3 (Options): 
1. Select the Titles tab.  Enter the chart title, X-axis label, and Y-axis label.
2. Select the Legend tab.  Click the check in the Show legend box to remove it.
3. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.  If you want the graph to appear in the worksheet with your frequency distribution, click As an object in and select the worksheet containing your frequency distribution.  Click Finish.
    To make the graph consistent with the conventions of the textbook, the line should be smooth, not jagged.  To accomplish this, right click the mouse while pointing at the line.  Select Format Data Series, select the Patterns tab, and check the box Smoothed line.

4. Scatter Diagram
A scatter diagram depicts bivariate numerical (quantitative) data.  Suppose you have a random sample of 6 students, each of whom has provided weekly work hours and a grade point average.  The data follows.

A scatter diagram is an "XY (Scatter) Chart" in Excel.  To construct a scatter diagram for the above data, first select the range of cells containing the data  and labels (A1:B7).  Click the ChartWizard button on the standard toolbar.
Step 1 (Type): Select chart type (click XY (Scatter) and then select chart sub-type (click Scatter, points only).  Click Next>.
Step 2 (Source Data): The cell rangethat you highlighted should be showing in the Data range box and the arrangement of the data series is in Columns.  Click Next>.
Step 3 (Options): 
1. Select the Titles tab.  Enter the chart title, X-axis label, and Y-axis label.
2. Select the Legend tab.  Click the check in the Show legend box to remove it.
3. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.  If you want the graph to appear in the worksheet with your data, click As an object in and select the worksheet containing your data.  Click Finish.

Graphs of Categorical (Qualitative) Data
Following is the summary table for class level from the previous handout.

1. Bar Chart
A bar chart is a "Bar Chart" in Excel.  Select the range of cells containing the data (A2:B5).  Click the ChartWizard button on the standard toolbar.
Step 1 (Type): Select chart type (click Bar) and then select chart sub-type (click Clustered bar).  Click Next>.
Step 2 (Source Data): The cell range that you highlighted should be showing in the Data range box and the arrangement of the data series is in Columns.  Click Next>.
Step 3 (Options): 
1. Select the Titles tab.  Enter the chart title, X-axis label, and Y-axis label.
2. Select the Legend tab.  Click the check in the Show legend box to remove it.
3. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.  If you want the graph to appear in the worksheet with your summary table, click As an object in and select the worksheet containing your summary table.  Click Finish.

2. Pie Chart
A pie chart displays categorical data as pieces of a pie.  Select the range of cells containing the data (A2:B5).  Click the ChartWizard button on the standard toolbar.
Step 1 (Type): Select chart type (click Pie) and then select chart sub-type (click 3-D Pie).  Click Next>.
Step 2 (Source Data): The cell range of the data should be showing in the Data range box and the arrangement of the data series is in Columns.  Click Next>.
Step 3 (Options): 
1. Select the Titles tab.  Enter the chart title.
2. Select the Legend tab.  Click the check in the Show legend box to remove it.
3. Select the Data Labels tab.  Click Show label and percent.  Note: In Excel XP, you can show the label, value, and percent for each piece of the pie.
4. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.  If you want the graph to appear in the worksheet with your summary table, click As an object in and select the worksheet containing your summary table.  Click Finish.

3. Pareto Diagram
A Pareto diagram is a vertical bar chart (column chart) with the categorized responses arranged in descending order of their frequencies.  Superimposed on the bar chart is the cumulative polygon for the data.  A Pareto diagram provides two graphs in one.  Some preparation of the data is required.
1. First, create an ordered summary table so that the categories appear with frequencies in descending order.  
2. Next, compute category percentages.  In cell C1, type the label %.  In cell C2, type the following formula:  =B2/B$6 and press Enter.  Copy the formula down to cells C3:C5. 
3. Next, compute cumulative percentages.  In cell D1, type the following formula:  =C2 and press Enter.  In cell D2, type the following formula:  =D2+C3 and press Enter.  Copy the formula down to cells D4:D5.

Select the range of cells containing the data (A2:A5,C2:C5,D2:D5).  Click the ChartWizard button on the standard toolbar
Step 1 (Type): Select chart type, click the Custom Types tab, and select Line-Column on 2 Axes.  Click Next>.
Step 2 (Source Data): The cell range of the data should be showing in the Data range box and the arrangement of the data series is in Columns.  Click Next>.
Step 3 (Options): 
1. Select the Titles tab.  Enter the chart title, X-axis label (Class Level), Y-axis label (Percentage), and second Y-axis label (Cum percentage).
2. Select the Legend tab.  Click the check in the Show legend box to remove it.
3. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.  If you want the graph to appear in the worksheet with your summary table, click As an object in and select the worksheet containing your summary table.  Click Finish.
You may need to make some adjustments in the scales of the two vertical axes.  To do this, point at an axis, right click the mouse, and choose Format Axis.  Select Scale, and adjust the minimum value, maximum value, and major unit until the scale appears the way you want it.

4. Side-by-Side Bar Chart
A side-by-side bar chart allows you to display bivariate categorical data in a way that allows for comparisons to be made.  As an example, start with the following cross-classification table for gender vs class level, which has been obtained from the student data file for this class.  

Start by selecting cells A2:F4 (do not include the totals).
Step 1(Type): Select chart type ( click Bar)  and then select chart sub-type (click Clustered bar).  Click Next>.
Step 2 (Source Data): The cell range of the data should be showing in the Data range box and the arrangement of the data series is in Rows.  Click Next>.
Step 3 (Options): 
1. Select the Titles tab.  Enter the chart title (Class Level by Gender), X-axis label (Class Level), and Y-axis label (Percentage).
2. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.  If you want the graph to appear in the worksheet with your summary table, click As an object in and select the worksheet containing your cross-classification table.  Click Finish.