Excel Basics

Overview

These instructions try to step through the following tasks:

moving data from the Web to Excel,
using Excel's built in functions,
loading the Data Analysis toolbox into the Excel program,
using the histogram tool from the Data Analysis toolbox,
printing only a portion of a spreadsheet.

How to get data from the Web into Excel

Navigate within your browser program (Netscape or Internet Explorer) until you get to the page with the data that you wish to copy. Using the mouse, click at the top of the data you wish to copy and, holding the button down, drag the mouse to the bottom of the data you wish to copy. The data should be highlighted at this point. Now choose "Copy" from the "Edit" menu. (Hereafter, we will abbreviate this choice by "Edit --> Copy".) This will copy the data onto the computer's "clipboard" (temporary memory).

You now need to start Excel. If your computer has enough RAM memory, you can leave your browser running while you start up Excel. For some computers, however, you will need to exit from your browser into order to make memory available for Excel.

Once Excel is open, click on the "cell" (box) where you wish to place the first point of data. The rest of the data will lie below this cell. To put the data there, choose "Edit --> Paste". The data should appear in the worksheet with one number in each cell. (If the data numbers were in a rectangular array, i.e., a "table", rather than a single column, on the web page, further steps may be needed to distribute them properly in the spreadsheet; but we will not discuss that here.)

How to use Excel's built-in functions

To compute averages, medians, and standard deviations based on a column of data:

Click on the cell in which you wish the statistic to appear.
Choose "Insert --> Function" Or, click on the "function wizard" button with f_x on it. A window will appear with a list of all the functions available categorized. Most of the functions we will use are in the "statistics" category.
Scroll down the list until you find the function you wish to use and select it with the mouse. A window will appear with the function's name and a space where you can enter the location of the data.
Either (a) enter the first and last cell names with a colon between them (for example, B5:B752) OR (b) click on the button to the right of the blank -- Your window will move aside so that you can "loop" the data range you wish to use. Press return to return to the function window.
Click on OK. The worksheet window will return with the statistic you requested in the cell you highlighted.

If you know the name of the function you want to use and the format to enter its arguments (the cell names), you can just highlight the cell and start typing, beginning with an equal sign. For example, =average(a3:b10) (followed by the Enter key) will put the average value of the contents of the 16 cells in the rectangle from A3 in the upper left to B10 in the lower right.

Array Functions like "Frequency" and "Trend"

Some functions return more than one value. These are called "array functions", because their results are arrays of numbers. We have to have enough room to put all the resulting numbers, or they won't appear in the worksheet. In my experience, though, the function wizard and the f_x button don't work correctly for these functions. Highlighting a cell, typing the desired formula, and copying (by dragging or using the Edit --> Copy command) to the adjacent also doesn't work. Even highlighting the desired range of cells for output, typing the function, and pressing the usual Return (or Enter) Key doesn't work. To enter them correctly, highlight the range you wish to put the list of results, click in the formula bar (just above the spreadsheet), type the function, then press Control-Shift-Return (on Wintel machines) or Command-Return (on Macs).

How to load the Analysis Toolpak into the Tool Menu:

Excel has a menu called "Tools". To check whether the Data Analysis toolbox has already been loaded, select this menu and look for the menu item (usually near the end) called Data Analysis. If it is there, continue to the next section.

If the Data Analysis Tool is not in the "Tools" menu, select "Tools --> Add-ins..". A window will open containing a list of toolboxes that you may add. Near the top of this list will be a toolbox called Analysis Toolpak. Select this toolbox with the mouse and then click OK. After a few seconds, the computer will return you to the worksheet window and a new menu item called "Data Analysis" should be available at the bottom of the "Tools" menu.

What if the "Add-ins.." list does not include the Analysis Toolpak? For a toolbox to be added, Excel must know where to look for additional toolboxes. By default, it looks in a folder called "Excel Add-ins" which is kept in the Office directory. Microsoft Excel 5.0 folder. If Excel cannot find the Data Analysis toolbox: exit from Excel; make sure the Excel program and the Excel Add-ins directory containing the Data Analysis file exist and are in the same directory. Then start Excel and try again.

How to use the histogram tool in Excel

You should already have the data you want to graph in a column of the spreadsheet.
Create a new column of numbers that represent the upper boundaries of ranges ("class intervals", or bins) into which the data are to be sorted for the histogram.
Choose "Tools --> Data Analysis". A window will appear with many functions listed.
Choose Histogram from this list of functions. A window should appear asking you for information about the graph.
Enter the cells for the "Input Range" (the data) and for the "Bin Range" (the class intervals).
Choose "Output Range" and enter the cell in which you want the output to start (extending down and to the right). The output of this command is the list of frequencies with which data appear in the various class intervals and the histogram (bar graph) itself.
Choose the "Chart Output" option at the bottom of the window and click on the "OK" button. The histogram should appear in the spreadsheet. (Actually, the resulting graph will be misleading, for two reasons: (1) the bars will all be the same width, even though the class intervals may not be, because the command gives a bar graph, not a true histogram; and (2) the upper boundaries of the class intervals will be in the center of the corresponding bar, not to the right where they belong. Using only class intervals of equal length, enough to include all of your data values, will solve, or rather mask, the first problem, but you should keep the second in mind when using this command.)

Printing part of a spreadsheet

Remember that for most of the assignments, you need not to turn in all the data; only the summary data is requested. To print out only what you need:

Highlight the rectangle of the spreadsheet that you want to print.
Choose "File --> Print Area --> Set Print Area".
Choose "File --> Print". The computer should respond with a window asking for any last-minute instructions about the print job. Click "Ok", and the portion of the spreadsheet should be printed on the default printer (or the printer you chose in the last window).

Miscellaneous

Why Excel? You are not required to use the Excel spreadsheet for these assignments. You may use any spreadsheet that allows you to do the calculations and graphs required (for example, histograms). However, Excel is officially supported by ITS and so is widely available,and people at the computer center should be able to help you if you have trouble.
Which Excel? These instructions were written for Excel 98; a version widely used on campus. Other versions probably work fairly similarly, but there may be slight differences.