Math320 Excel Notes

Math 320 Notes for Excel

AN INTRODUCTION TO USING MICROSOFT EXCEL:

You can find computers that can run Excel in the ArtSci computer lab, which is in the basement of the same building that has Holmes lounge.

If you have a personal computer, then Excel may already be installed on it.

Once you are seated at a computer with Excel, there will normally be an icon on the screen that says ``Excel''. Double-click on this icon with the mouse. (That is, move the mouse pointer to that icon and click the left-hand mouse button two times quickly in succession.) A blank spreadsheet screen will appear after a few seconds. If there is no icon on the computer screen, click on Start, then Programs... on a dropdown menu, then Microsoft Excel on another dropdown menu. Again, a blank spreadsheet will appear in a few seconds.

To enter numbers or text, click on a cell with the mouse to make that cell the active cell and then enter text or numbers from the keyboard.

The Tab key moves you one cell to the right.

The Enter key moves you to the first cell in the next row.

The right-arrow and down-arrow keys can be used instead of the Tab and Enter keys.

Note that columns in Excel are denoted by letters (A,B,C,D,...) and rows have numbers. Thus cell B3 is the third cell in the second column (column B).

Using Excel to add a row of numbers: Assume that you have entered some numbers, for example in row 3 columns B through G, that you want to sum or find the average or standard deviation of these numbers. (If you don't know yet what a standard deviation is yet, you can look ahead in the book.) First, click on an empty cell. The result of the operation will appear here. (Otherwise, you might accidentally overwrite a cell with valuable data.)

Highlight cell I3 with the cursor. This is two cells beyond your row of numbers. Now enter =SUM(B3:G3) (don't forget the = ! ) at the keyboard and press the Enter key. The sum of the numbers in B3:G3 will appear in cell L3. NOTE: By Excel convention, this sequence of cells is written as B3:G3. Note that a colon is used to separate B3 and G3, and not a common or dash, while will usually give you either an error message or the wrong answer. The set of four cells in a column immediately below cell B3 is written B4:B7. The notation B3:G7 means the 5x6 block of cells with B3 and G7 as corners.

Using Excel Statistical Functions: Again, click on an empty cell in which you want something to appear, for example cell I4. Click on the button with an icon like f_x on the first icon menubar at the top of the Excel screen. Alternatively, you can click on Insert | f_x Function... This notation means that you first click on Insert on the main menubar at the top of the screen and then on ``f_x Function...'' on the resulting drop-down menu.

Some versions of Excel do not display all icons on the icon menubar at the same time, if you do not see f_x on the icon menubar, click on the ``More Buttons'' extension button with an icon like >>. A menu will drop down that probably has f_x on it. After you click on it, it will be displayed on the main menubar.

Click on Statistical on the dialog box (or window) that then appears. In the Statistical menu that drops down, double-click on AVERAGE (for example) or else click on AVERAGE and then click OK.

A window will drop down with edit lines to fill in. There may be help messages for the edit lines at the bottom of that window. If not and one of the edit lines appears mysterious, click on it. A help message for that line may then appear in the bottom half of the window. If the currently-highlighted cell has a value calculated by a previously-entered calculation, then the drop-down menu may give you options for that formula only. To then see a menu for all statistical procedures, click on Cancel on the dialog box and then highlight an empty cell.

Enter B3:G3 for the six cells in the third row. Excel may suggest another range of cells, but do not be deterred and enter B3:G3. Note that the average of the six numbers in these cells appears in the window itself. If you want the answer to appear in cell I4 (or the cell that you highlighted), press Enter or click OK.

When a function asks you for a range of cells, you can usually click on the edit line and then mark the range of cells with the cursor. This is often faster and more accurate than manually entering a range of cells.

As another example, suppose that we want to find the average and standard deviation of a set of 50 numbers in rows 3 through 7, columns A through J. The results should end up in cells B2 and C3. Then (a) click on cell B2 to make that cell active, (b) click on

f_x | Statistical | AVERAGE

(c) enter the cell range A3:J7 in the Edit Line marked Number 1, and (d) click on OK or else press Enter. The average value will appear in cell B2. Repeat the same steps for cell C2 and the function STDEV.

Note what appears in the ``formula edit line'' just underneath the first icon menubar whenever you use an Excel formula or function. If you highlight a cell, this window tells you whether that cell contains an entered number or else a formula that calculates the number.

On an PC, you can also highlight a cell and press the F2 key. If the cell value was computed by a formula, the formula will appear in the cell and also all the cells used by the formula will be outlined. Click on a different cell to restore the appearance of the screen. The F2 key can also be used to show text in a cell that is too wide for that column and is obscured by an entry in the cell immediately to the right.

Other useful spreadsheet operations: To copy the value of a single cell B1 into cell B11, enter (for example) ``=B1'' in cell B11. To copy the ratio of cells B3 and C3 into cell D3, enter =B3/C3 into D3.

You can also highlight a cell and click on the Sum button (which looks like a capital Greek sigma or a summation sign) on the icon menubar. Excel will make a guess as to what range of cells you wanted to sum, but you can edit the formula in the formula edit window to make your own choice.

Workbooks and Worksheets: An Excel file is called a WorkBook, which is composed of one or more WorkSheets. The first new WorkBook is called Book1.xls (unless you rename it) and start with three WorkSheets called Sheet1, Sheet2, and Sheet3. You can move between the three WorkSheets by clicking on the WorkSheet tabs at the bottom of the Excel screen. You can rename Worksheets, delete WorkSheets, create new WorkSheets, and make WorkSheets invisible either by highlighting a WorkSheet tab and clicking the right mouse button or else by entering Format | Sheet . For example, if you only need two Worksheets, you could rename `Sheet1' to `Main' or `MyFormulas', `Sheet2' to `ExtraData', and delete Sheet3.

To reference a cell in another Worksheet, enter (for example) =Sheet3!B3 in a cell. (Note the exclamation point !) To reference a cell in a Worksheet in another Workbook, enter (for example) =[Book2]Sheet3!B3 . (Note the square brackets []) If you want to reference a cell in Sheet1 in another Workbook, then =Book2!B3 may also work, but this may only work if Sheet1 is the default Worksheet in that Workbook.

Copy and Paste: You can copy a block of cells in Excel from one location to another by: (a) Use the mouse pointer to highlight a block of cells, (b) click on the Copy button on the second icon menubar (it looks like two 8 1/2 by 11 sheets of paper with the upper right-hand corners folded; alternatively, click on Edit on the main menubar and then Copy), (c) move the mouse pointer to where you want the upper-left-hand corner of the block of cells to be copied, and (d) click on the Paste button on the icon menubar just next to the right of the Copy button (or enter Edit | Paste). A copy of the block of cells will now appear with its upper left-hand corner at the current mouse-pointer location.

To delete a block of cells, highlight the cells and press the key that says Del. This deletes only the values in those cells but not formatting information, so that you may still see colors or darkened lines from borders. Enter Edit | Clear | All to delete both values and formatting information.

You can also ``Cut and Paste'' instead of ``Copy and Paste''. This is the same as ``Copy and Paste'' except that the data disappears from its original location. To do this, click on the Cut button on the icon menubar instead of Copy. The Cut button looks like a pair of scissors.

You can find out what icons on the menubars do before you click on them by moving the move pointer to them and not clicking the mouse button. After about a second, a short description of that button should appear.

MORE ADVANCED TOPICS:

Copying and Pasting from Another Application: You can sometimes copy and paste a block of data directly from a Web page or word processor into an Excel spreadsheet. For example, suppose that you want to copy the numbers

     269  268  265    212  183  252
     285  294  315    293  254  274
     241  269  229    207  281  244
     242  220  238    252  233  264
     230  255  229    294  246  283
     259  251  271    324  291  287

into an Excel spreadsheet so that it appears as above in a six by six block of cells.

The key is whether numbers in the same row are separated by space characters in the Web browser or text editor (as it appears) or whether they are separated by invisible tab characters. If there are no separating tab characters, and you copy the block of numbers into the computer clipboard and then paste it into your spreadsheet beginning at cell B10 (for example), then the numbers will be pasted as six text strings into the single column B10:B15. If you try to sum these numbers by (for example) entering =SUM(B10:B15) into cell B16, then you will get 0 since Excel will think that you are adding six strings of text.

On the other hand, if numbers on the same row separated by invisible tab characters, then the data will be pasted into a 6x6 block of numbers at B10:G15 and can be treated as any other 6x6 block of numbers.

In general, if you can save the data to a computer disk file, then you can import it into a new Excel WorkBook using the Excel Text Import Wizard. You can then copy and paste the data between workbooks within Excel. The Text Import Wizard is smart enough (with your help) to figure out what the invisible separating characters are and to make the necessary adjustments. (See the Excel Text Import Wizard.)

Undoing and Redoing: If you make a mistake or change your mind, you can usually ``Undo'' one or more previous steps. Click on the ``Undo'' button on the second icon menubar. The ``Undo'' button looks like an arrow pointing left or counter-clockwise. There is also a ``Redo'' button in case you change your mind about an ``Undo''. This has an icon that looks like an arrow pointing right or clockwise. The ``Undo'' and ``Redo'' buttons have drop-down menus next to them. Sometimes you can undo a previous step without also undoing all of the steps inbetween.

THE EXCEL DATA ANALYSIS TOOLPAK: (Go to the top of this page)

Some of the most useful statistical functions in Excel are in the Data Analysis ToolPak. Most Statistical Functions return a single value in a single cell. In contrast, ToolPak functions can create up to 5 or 6 tables containing a variety of useful information at a single click of a button. The ToolPak function for Histograms is described in HomeWork 1. ToolPak functions for Correlation, Regression, Two-sample t-tests, and One-Way ANOVAs are described in detail on the Math320 Calculation Web page.

Click on Tools on the main Excel menu and then click on Data Analysis... for a menu of these functions.

If the option Data Analysis... does not appear on the Tools dropdown menu, then click on AddIns... This leads to a window that lets you install additional ToolPaks. Click on both Analysis ToolPak and

Analysis ToolPak -
VBA

(if the latter is present) and then click on OK. If there is no entry for Analysis ToolPak, look for

Data Analysis
ToolPak

. After Excel is done, return to the Tools menu. The option Data Analysis... should now appear. If you installboth Analysis ToolPak but not

Analysis ToolPak
- VBA

, then some statistical operations involving graphics may not work properly.

In the Data Analysis... dropdown window, you can double-click on options for Correlation, Regression, Two-sample t-tests, and One-way ANOVAs. All of these cause a window to appear that asks where your data is and what options you want to use. In most cases, data samples can be entered in Excel by either row or by column. (That is, each sample is either part of a column or part of a row.) However, in the Correlation and Regression functions, the data MUST BE IN SEPARATE COLUMNs or else you will get incorrect and confusing output.

If you get an error message from running an Analysis Toolpak function that says something about not be able to save a chart to a ``Shared Workbook'', then click on Tools | AddIns... from the main Excel menubar and make sure that

Analysis Tookpak -
VBA

is checked. If not, use the mouse to check it and click on OK. Try the Toolpak function again after Excel is finished. If you get the same error again or else Analysis Tookpak - VBA is already checked, then write your output to a different Workbook (see the next paragraph) and then Copy and Paste the output back to your main WorkBook. You can usually move and paste between different WorkBooks by clicking on File on the Excel main menubar and then entering a number for a recently opened spreadsheet. Otherwise, you can move between WorkBooks by working with the Minimize and Maximize buttons at the upper right of each WorkBook Excel window.

Where Your Output Will Go:

Unless you tell Excel otherwise, most ToolPak options write their output to a separate ``worksheet''. This means that you will not be able to see your data on the same screen as the output. Your data is not lost forever, however: You can move among different worksheets of the same ``WorkBook'' by clicking on tabs at the bottom of the Excel screen.

To prevent Excel from writing its output to a new ``worksheet'', click on the button that says ``Output Range''' in the ToolPak option window and enter the name of an empty cell. Then the ToolPak output (which can be voluminous in some cases) will fill a block of cells starting with that cell as the upper-left corner. The same dialog box also has the option of writing your output to a new WorkBook. That may be advantageous if you run across certain types of ``sharing'' errors.

Labels:

If you check Labels in ToolPak option windows, then Excel will assume that the first cell in each sample (row or column) is a text label. If you click on this button but do not make provision for actual labels (such as ``Height'' or ``Weight'' or ``Aardvark clutch size''), then Excel will use the first number in each sample as the label and subtract that observation from each sample.

Since these numbers will appear as odd-looking labels in your output, the grader may have little sympathy if you get the wrong answer because you used the first row of data as column labels.

See also Cramped Cells and Awkward Formatting below.

IMPORTING DATA FROM A COMPUTER TEXT FILE (Go to the top of this page)

The Excel Text Import Wizard allows you to import data into Excel from a computer text file. Assume that you have copied some data into a computer file named MYFILE.TXT and that the data is at the top of this file. For example, you may have saved a file from a Web browser and then used a text editor like Notepad (on a PC) to delete the part of the file above the data that you are interested in. To import this data into an Excel spreadsheet,

(i) Open Excel. Assume that the spreadsheet being displayed is called Book1.xls. (This is what normally happens when you load Excel.)

(ii) Enter File | Open... from the Excel main menubar,

(iii) In the OpenFile dialog box that appears, make sure that ``All Files (*.*)'' is selected in the ``Files of Type'' window at the botton of the dialog box. Otherwise, only Excel WorkBook files may be visible.

(iv) Find the directory that contains MYFILE.TXT and double-click on the file name MYFILE.TXT.

An Excel window or dialog box will now open that says ``Text Import Wizard''.

If numbers on the same row in MYFILE.TXT are separated by spaces or tabs, select ``Delimited'' and click on ``Next''. In the next dialog box, click on ``Space'' or ``Tab'' (separated) if that is how the data columns are separated. How Excel views the data will be displayed in the second dialog box, so that you can adjust settings until Excel understands how the columns are formatted.

If numbers in the same column are at the same row offsets, click on ``Fixed Width'' in the first dialog box and then ``Next'' to go to the second dialog box. Again, adjust settings until Excel understands how the columns are formatted.

(v) Click on ``Finish''. A new WorkBook named MYFILE.TXT will appear.

(vi) Finally, Copy and Paste the data in EX0254.DAT into your original WorkBook ``Book1.xls''.

At this stage, the WorkBook ``Book1.xls'' will be on top in Excel, and you can copy the data to the computer clipboard by (a) highlighting the data and (b) clicking on the Copy button.

The problem now is to find your original WorkBook to complete the Paste operation. First, click on File in the Excel main menubar. If an entry in the ``known file'' list at the bottom of the drop-down menu says ``Book1.xls'', then select Book1.xls. This will take you back to your original WorkBook.

If that doesn't work, click on the Minimize buttom on the upper right of the MYFILE.TXT Excel Window. Then MYFILE.TXT will be replaced by an icon within Excel and your original WorkBook will come to the front. To see the icons for minimized WorkBooks, it may be necessary to click on the Maximize button for the current Excel window and then drag the lower-right corner of the top window up slightly. The Maximize button is just next to the Minimize button on the upper right of any Excel window.

IMPORTING DATA FROM THE TEXTBOOK DATA DISKETTE: (Go to the top of this page)

If you purchased a new copy of the textbook, the data for most exercises and figures is on a diskette that is attached to the back of the book. If a problem has more than a few numbers, it may be faster and less error prone to read the data from a disk than to enter the data by hand. Similarly, you can copy data for homework problems that are not in the textbook by Copying and Pasting from the HomeWork Web page.

The computer disk has data for 305 exercises and figures from the textbook. The data are stored in ASCII (plain text) format as well as in formats for three statistical computer programs (Minitab, Systat, and Ecustat). You want to use ASCII format for Excel. If you download all of the exercises and figures in ASCII format, it will take up 124Kb of disk space. These 305 text files can easily be stored on a small portion of a floppy disk.

For example, go through the following steps to download the data for Exercise 54 in Chapter 2:

(i) Put the textbook data disk in the computer's floppy disk drive.

(ii) Run the program Extract on the data disk by entering Start | Run | A:EXTRACT from the computer's main screen. Here Start | Run (etc.) means to click on the Start button and then on Run on the drop-down menu that appears. You can also run A:EXTRACT from a console or DOS-prompt window providing that you first make A: the default drive. Press the Enter key.

(iii) When the EXTRACT banners appear, press the space bar (or any other key) to go to the first EXTRACT menu.

(iv) Enter option (4) from the format menu for ASCII format, then press Enter.

(v) Enter option (3) in the next menu for all excercise and figure data files from a particular chapter, then press Enter.

(vi) When asked from which chapter of Chapters 2-17, enter 2 for Chapter 2 and then Enter, and

(vii) When asked, enter the name of a directory on the computer's hard drive for the files to be copied to.

In the last step, pick the name of a relatively empty folder or directory to make it easier to find the files later --- C:\TEMP should be safe. Be sure to remember the name of the directory. EXTRACT will now download 27 files for 22 exercises and 5 figures into C:\TEMP. The file with the data for Exercise 54 will be named EX0254.DAT. The data for Figure 18 in Chapter 2 is named FIG0218.DAT.

To import the data in EX0254.DAT or FIG0218.DAT (or any other computer text file) into Excel, go through the steps in Importing data from a text file.

THINGS YOU CAN DO IN EXCEL: (Go to the top of this page)

Summing Rows and Columns, and Copying Formulas So That You Can Compute Many Different Sums at Once: Suppose that you have a block of 8*5=40 numbers in rows 3 through 10 and columns B through F in a spreadsheet:

        A   B   C   D   E   F   G   H
     1
     2
     3     69  68  65  12  83
     4     85  94  15  93  54
     5     41  69  29   7  81
     6     42  20  38  52  33
     7     69  68  65  12  83
     8     41  69  29   7  81
     9     30  55  29  94  46
    10     59  51  71  24  91
    11

If you enter =SUM(B3:F3) in cell G3, then the row sum for row 3 columns B:F will appear in cell G3. You can use the ``Copy and Paste'' buttons to get row sums for all eight rows 3 through 10 in the following manner. First, click on cell G3 and then on the Copy icon. Then highlight cells G3:G10 and click on Paste. The Sum formula for row 3 will be copied to cells G3:G10 as expected, but will have UPDATED cell references.

That is, =SUM(B4:F4) will be stored in cell G4, =SUM(B5:F5) in cell G5, =SUM(B6:F6) in cell G6, and so forth. In this way, you can get all eight row sums at once in one easy operation. You can store the sum of all the numbers in the table (which is the sum of all the row sums) in cell G11 by entering =SUM(G3:G10) in that cell.

You can calculate column sums in the same way. Enter =SUM(B3:B10) in cell B11 and copy the contents of B11 to the range B11:F11. Then row 11 (cells B:F) will have the five column sums. The sum of these five column sums (=SUM(B11:F11)) will be the same as the sum of the eight row sums (=SUM(G3:G10)), namely the sum of all 5*8=40 numbers in the table.

You are of course not restricted to sums. For example, you could enter =B3*C3 + D3*E3 + F3 in cell H3 and copy that formula to cells H3:H10 in the same way. Then the same function of five row entries will be computed for all eight rows and stored in column H. You can store the sum of those eight values in cell H11 by entering =SUM(H3:H10). You can then divide that value by the sum of all 40 entries in the table by entering =H11/G11 in cell I11. (Exercise: What answer do you get when you do this?)

Sometimes you want to PREVENT a row or column reference from being updated when it is copied. To do this, precede it by $ . For example, the formula ``=SUM($B$1:$B$10)'' will sum the cells B1:B10 no matter where the formula is copied. Note the use of four $ symbols, since this formula has four row and column references that should be kept fixed.

Cell Justification (Left,Center,Right): When you enter text in a cell, text is normally pushed to the left and numbers are pushed to the right. This is called ``left justification'' and ``right justification'', respectively. Numbers should normally be right justified so that the digits column will line up.

Sometimes a block of cells with wide columns will look odd because the text column headings are pushed left while the numbers are pushed to the right. You can change how a cell or block of cells are justified by (a) highlighting the cell or block of cells and (b) clicking on the left, center, right justification buttons on the icon menubar. These three icons look like a page of text with the lines respectively left, center, and right justified. Sometimes one or two of these three buttons are on auxiliary menubars so that you have to look around for them.

You can accomplish the same thing with a little more effort by entering

Cramped Cells:

If the text in a cell is wider than the column that it is in, then you will not be able to see all of the text. An exception is if the cell immmediately to the right of the text is empty, in which case Excel allows the text to overflow into the next cell or cells. If the cell has been formatted for center justification, then text may also overflow to the cells to the left.

Column headings in ToolPak statistical function output often look cramped because the text is too wide for the default column widths. To open up one or more of these columns so that you can see the entire text, you can either

(i) Open up the column by moving the mouse cursor to one of the lines between Excel column headings and dragging it to the left or right, or else

(ii) highlight a cramped cell (or a block of cells) and click on

Format | Columns | AutoFit Selection

The columns will widen and result should be easier to read.

Awkward Formatting:

Tables of numbers in Excel can sometimes be confusing because their decimal points are not aligned properly. That is, ``32.76865'' may appear in one cell and ``100'' in the cell immediately underneath, with ``100'' appearing directly below ``865''. To fix this, highlight the cells, enter

Format | Columns | Number | Number

and specify (for example) Decimal places: 2. The two numbers will now be displayed as ``32.77'' and ``100.00'' with the decimal points aligned correctly.

SPECIAL EFFECTS:

The following can make your output look clearer and prettier and impress your friends, but should be pursued only if you have no better use for your time. The grader make take off points if you go overboard and your homework becomes harder to read as a result.

Center Text in a Box without Gridlines: Current Excel versions have a `Merge and Center' button that can be used to build impressive labels or column headings that can extend over several columns and rows. First, highlight a block of cells, either within a row of column headings or else extending over several rows. Click on the ``Merge and Center'' button on the icon menubar. (This looks like a box with a large ``a'' inside it.) You may be warned that all entries in the block will be erased except for the leftmost or upper leftmost entry. If you continue, the spreadsheet gridlines within the block will disappear and all text will disappear except for the contents of the leftmost or upper leftmost cell, which will be centered horizontally. (Otherwise, you can center it by clicking on the ``Center Justify'' button.)

If the block extends over more than one row, it can be centered vertically by entering

Borders for Blocks of Cells, Background Colors, and Font Colors: The Borders, FillColor, and FontColor buttons on the 2nd icon menubar have drop-down menus for Border type for a block of cells, fill or background color, and font color. The default for Border Type is a single line at the bottom of block, but you can specify solid lines around the block. The icons for Border, FillColor, and FontColor look like a box or page with dots inside, a paint can, and a capital letter A, respectively. These can be used to display a row or block of cells with bright red text on a yellow or pumpkin-colored background within a heavy outside border.

Font attributes: These refer to boldfaced, italic, and underlined text and have icons that look like capitals B, I, and U. To put text in boldface, highlight a cell or block of cells and click on B. An effective way to make a table of numbers stand out is to put the column headings in boldface. If the column headings are short, it also helps to center the headings within their cells.

Most menu icons are toggles, which means that clicking on B a second time removes the boldface.

To undo these options and make your spreadsheet look plain again, highlight the affected (infected?) cells and enter

Edit | Clear | Formats

The text and numbers in these cells will stay the same, but the original default formatting will return.

WARNINGS: (1) The purpose of these options is to make your output easier to read by making it clearer what is a heading, what are table entries, and what is important. Output that is clearer is also more attractive. A little bit of formatting can often make a spreadsheet look less dull and make it easier to read. However, if you overuse special effects, your spreadsheet may look busy and be harder to read. The effect on readers may also be to admire your colors and special effects but ignore the information in your spreadsheet. If your spreadsheet has important information in it, this may not be what you had in mind. Thus you should be conservative in using special effects.

(2) The effect of colors depends on the computer, and specifically on the amount of video memory for displaying colors. If you put bright red text on a pumpkin-colored background (or even white text on a black background), it may be readable on a newer computer but illegible on an older computer.