Using Excel 1: Excel Basics

Overview

• Getting data from the Web into Excel
• Creating your own formulas in Excel
  
• Using Excel's built-in functions
• Using the Chart Wizard
  
• Loading the Data Analysis toolbox into the Tool menu
• Using the histogram tool in Excel
• Printing part of a spreadsheet
• Miscellaneous

Getting data from the Web into Excel

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

Once Excel is open, click on the "cell" (box) you wish to be 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.
 
Creating your own formulas in Excel

You can enter algebraically manipulate the contents of cells to produce new results in Excel.  To do this, you enter an appropriate algebraic formula in a cell.  All formulas must begin with an equal sign: =. 

If you wanted to add the contents of one cell, say A12, to twice the contents of another, say B32, you would type "=A12 + (2*B32)" into any empty cell.  When typing a formula, you can just use the mouse to click on an appropriate cell instead of typing out its cell name [eg A12].

Once you have entered a formula, you can copy and paste it into other cells.  BUT, if you do, the cell name[s] used in the original formula will be 'updated' as you copy it.  To keep a cell name from being 'updated' when copied, you must put dollars signs, $, around the letter in the cell name.  This is referred to as 'locking' a cell name.  For example, $A$12 would lock the contents of A12 in a formula.

Using Excel's built-in functions

1. Click on the cell in which you wish the statistic to appear.
2. 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.
3. 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.
4. 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.
5. Click on OK. The worksheet window will return with the statistic you requested in the cell you highlighted.

Array Functions like "Frequency" and "Trend"

So, select the range you wish to put the list of results, Click in the formula bar somewhere as if you were going to type something there , then press Control-Shift-Return [on Wintel machines] or Command-Return [on Macs].

Using the Chart Wizard

The "Chart Wizard" helps you to produce scatter plots, bar graphs, pie charts and other types of charts in Excel.  You can launch the Chart Wizard by selecting the Toolbar button marked with a bar graph. 

The Chart Wizard steps you through several menu options.  You first select the type of chart you want to produce.  Select, for example, the XY scatter plot option.  Next you select how you want the scatter plot of (x,y) pairs to look: as indivual points, points joined by line segments, etc. 

You'll next be asked to select data to use in the scatter  plot.  It can be in adjacent columns or rows, but it must be adjacent.   Be sure to select  both sets of data at the same time.  Also, it's important to note that Excel will always take the first set of data [first column or first row] as the X information and the second set as Y.  If you select three or more sets of data, the first will be X.  The rest of the data will be treated as separate sets of Y information.  The finished plot will contain different "series" of points, one for each set of Y information paired with the same X information.

To finish using the Chart Wizard, you'll be able to tell Excel where to locate your chart and what labels you might want to use on the X and Y axes of the plot.  You can resize, alter labels, change scales, etc on the plot after it's been produced in the worksheet. 

Loading the Analysis Toolpak into the Tool Menu

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.
 

Using the histogram tool in Excel

1. You should already have the data you want to graph in a column of the spreadsheet.
2. 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.
3. Choose "Tools --> Data Analysis". A window will appear with many functions listed.
4. Choose Histogram from this list of functions. A window should appear asking you for information about the graph.
5. Enter the cells for the "Input Range" [the data] and for the "Bin Range" [the class intervals].
6. 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.
7. 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

1. Highlight the rectangle of the spreadsheet that you want to print.
2. Choose "File --> Print Area --> Set Print Area".
3. 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

Which Excel? These instructions were written for Excel 98; a version widely used on campus. Other versions probably similarly, but there may be slight differences.