Installing Data Analysis Toolpak:

1.) Check to see if it is already installed go to Tools and if Data Analysis is listed you are all set. Otherwise, go to step 2.

2.) Go to Tools/Add-Ins and select Analysis ToolPak and then hit OK. You may be directed to insert a Microsoft Office or Excel disk for personal computers. If so, follow the directions on screen.

You only need to do this once, unless you use another computer.

Descriptive Statistics/Summary Statistics (for numerical RVs)

1.) Select Tools/Data Analysis. In the table, choose Descriptive Statistics and hit OK. Where it says Input Range, you can enter the cells that contain your data (say, A1:A18). Another way to enter in the cells is to click on the space next to Input Range and then highlight the cells containing your data.

Assuming the data is in a column, keep the Grouped By option on Column. Obviously, if you entered your data in a row (across) you should check Row.

You should also check the Label in First Row box, if you labeled your data and included the label in the input range.

For the Output Options you can choose either the Output Range and enter in an empty cell (say C1). With this option, the summary statistics will be put in cells starting in C1. Or, you could choose New Worksheet and the output will be put on a new sheet.

Lastly, you should check Summary Statistics and then hit OK.

The output will include the mean, median, mode, standard deviation, variance, minimum value, maximum value, range and number of data points.

Histograms (Graphically summarizing a numerical RV):

Use the Excel file (Making Histograms) on the course website to get quick and accurate histograms.

Otherwise:

1.) First you sort the data so we can see what the range of the data is. Highlight column and then click the icon that has an A…Z. This will sort one column from low to high.

2.) Then you need to define the bins or x-axis for your histogram. Enter the label “Bin” into a blank cell. Type in the bins. You can use Excel to automatically fill in bins with a pattern. Type the first 3 numbers and then highlight them by clicking your cursor and dragging. Then move the cursor to the dark square in the lower right corner of your highlighted cells and click and drag it to autofill. This will fill in the rest of the bin numbers.

3.) To draw the histogram, select Tools/DataAnalysis/Histogram OK. For the Input Range highlight your data or enter in the cell range. For Bin Range highlight the cells you created in step 2. If you highlighted the column titles, be sure to click in the box next to label. Select Chart Output and select OK. A histogram should appear. Click on the histogram and drag the squares in the corner to resize the plot. To make the plot nicer looking, double-clicking on any bar. Choose Options and change Gap Width from 150 to 0. This will make the bars be adjoining. Hit OK. You can click over the axes titles and retype in more informative ones.

Lastly, note that Excel calculates the counts in each bin in a non-intuitive way, we need to shift all the counts up one cell. To do this easily, click on the first bin’s count. Then right-click and delete that entry. Choose the option to shift all cells in column up and click OK. Finally, be sure to delete the last row which says “More”.

Bar Chart/Pie Charts (Graphically summarizing a Categorical RV):

1. Get the counts per class. [Follow steps 1 – 3 under Histograms to create a column of the categories, and get a table of the categories and counts. Delete the More category, if not applicable.] If data is in summarized form you can skip to step 2.

2.) Highlight both columns (column names then summarized data in a box). If your classes are numerical, you should rename them to be more informative and so that Excel will create an appropriate graph).

3.) Select the Chart Wizard (Choose Bar chart or Histogram or pie chart – depending on view you like)

4.) Hit next and enter titles. Finish

5.) Delete legend and change gap width by double clicking on a bar and choosing the Options tab. Change the gap width to 0. You may also need to resize your graph.

Scatterplot (Graphically summarizing two dependent, numerical variables)

1.) In Excel the column containing the explanatory variable (X) needs to be listed directly to the left of the column containing the response variable (Y). Highlight both the X and Y values. Click on the Chart Wizard on the tool bar (the icon that looks like a bar chart) or select Insert/Chart on the menu bar and hit enter. Choose the XY(Scatter) chart type, hit Next.

2.) Only if you forgot to highlight the data (otherwise skip down to the next step): You must enter the data here. For the data range enter the cells containing your data (ex: a1: b23) or highlight the data in the two columns containing your X and Y data. Make sure the series in selection is for columns. Hit Next.

3.) For chart title type an appropriate title. Label your X and Y axes with a short description of the data and its units (ex: Time (minutes)). Hit Next when you are done with the titles and axis labels and then choose Finish.

4.) After the Scatterplot appears, place your cursor over the legend and delete it.

5.) To change the scale of the y-axis (if there is a lot of empty space vertically), place the cursor over any one of the y-axis numbers and double click. Choose the Scale option and change the minimum value and/or maximum value to fit the range of values for Y. Then hit OK. Be sure to select these numbers appropriately so that you don’t accidentally ignore some points on the graph. The x-axis can be adjusted similarly.

6.) To get the least-squares line and R² value, click on the graph. Then go to Chart/Add Trendline and click on the tab that says options. Then check the boxes next to Display equation and R² values. Then click OK.

Copying Output:

1.) You can now cut and paste this into your word processing document or print it out, if needed.

2.) You may need to resize your chart to get a meaningful view (ie: one where the points aren't all clustered together). To do this click in the chart and move the black box in the lower right corner down to expand the view of the chart. You can do this in Excel or in Word.