SPREADSHEETING WITH EXCEL
I. FUNDAMENTALS, FORMULAS, AND FUNCTIONS

Richard M. Felder
Department of Chemical Engineering
North Carolina State University

(Revised by E.M. Wilcox, August, 1998)

SPREADSHEETS

A spreadsheet is a matrix of information, organized by rows and columns. Each cell of the spreadsheet may contain a number, a character string, or a mathematical formula. An illustrative spreadsheet is shown below.

  A B C D
1 Personal Budget Sheet: September 1998
2     
3    Actual  
4   Budget Expense Saving
5     
6 Entertainment $50.00 $60.00 ($10.00)
7 Books $20.00 $0.00 $20.00
8 Groceries $150.00 $120.00 $30.00
9 Rent $150.00 $150.00 $0.00
10 Stationery $10.00 $0.00 $10.00
11 Transportation $20.00 $10.00 $10.00
12 Utilities $30.00 $30.00 $0.00
13     
14 Total $430.00 $370.00 $60.00

The spreadsheet has 14 rows and four columns (A-D).

In preparing this spreadsheet, the user will have typed in labels (text) or numbers for the cells in Rows 1-12, Columns A-C, such as

Expense in Cell [C4] (Column C, Row 4)
150.00 in Cell [B8]
In other cells the user will have placed formulas to be used to calculate the cell contents. For instance, the formula in Cell D6 would be

=B6-C6 (entered in Cell D6)

which means, subtract the number in Cell C6 from the number in Cell B6 and put the result in Cell D6. (All formulas begin with equal signs.) The formula in Cell B14 might be

=SUM(B6:B12) (entered in Cell B14)

which means add the numbers in Cells B6 through B12 and put the result in Cell B14.

Once input values and formulas have been entered in their respective cells, the spreadsheet program executes all the formulas and inserts the results in the corresponding cells of the spreadsheet. If some of the cell values are then changed, the formulas are automatically recalculated to reflect the new input values. Once the spreadsheet has been prepared it can be printed out and used to generate plots and tables to be included in a report.

RUNNING EXCEL ON UNITY/EOS WORKSTATIONS

Excel is Microsoft's (and the world's) most popular spreadsheet program. It runs on Windows machines (like the Windows NT workstations in the basement of the laundry building) and on Macintoshes. It can also run on UNIX machines (like the SPARCstations in Riddick 118), but only under a Windows emulator like SoftWindows or WABI, both of which can be accessed from the SPARCstations. Running on a Windows machine rather than a Windows emulator is invariably faster.

IMPORTANT COMMANDS

Before taking you through an introductory tutorial, we list several important commands for easy reference. If you want to quit a session or if you get in trouble, check this list.

TUTORIAL

Getting Started

Log on and open Excel, following the procedure given previously for the type of workstation you are running (Windows or UNIX). The Excel window that opens has the following components (find them on the screen as we describe them):

Some of our descriptions may not exactly match what you seen on your screen. The reason is that two different versions of Excel are installed on the Windows NT and UNIX file servers.

We will explain the other features of the Excel window in the exercises that follow. The symbol [Return] henceforth denotes the Return or Enter key.

Moving around the worksheet

  1. Move the cursor around the worksheet using the mouse. Move the pointer to different cells of the worksheet and click the left mouse button. Note that the cell cursor jumps to the cell on which you click and the cell address of its new location is shown on the control/status line.

  2. Move the cursor with the arrow keys. Use the four arrow keys toward the lower right of the keyboard to move the cell cursor up, down, left, and right.

  3. Go directly to a named cell. Using the mouse, choose the Edit menu (move the pointer to it and press and hold the left mouse button) and select Go to... (drag the pointer down to that item and release the button). In the Selection window of the dialog box that appears, type D5 [Return]. The cursor should now be in Cell D5.

  4. Return the cursor to Cell A1. Press [Control][Home]. (The control key is toward the lower left of the keyboard and the home key is in the second group of keys from the right.)

  5. Shift the worksheet. Hold the right arrow down to push the cursor past the rightmost column showing on the screen. Notice that you are bringing additional columns into view on the right and pushing others off to the left. Keep going past Column Z and observe how the adjacent columns are labeled (AA, AB, AC,...). Now move down to higher numbered rows at the bottom with the down arrow, pushing upper rows out of view at the top.

  • Enter a number in a cell. Send the cursor to Cell AV61. (Use the Go To command under the Edit menu.) Type 27 [Return]. The number you typed should appear in Cell AV61.

    Scrolling the worksheet.

  • Scroll horizontally one column at a time. Look at the bar just below the last visible row of the worksheet. On the left are some arrow buttons (for scrolling from one sheet to another) and sheet numbers, and on the right is the horizontal scroll bar, a window with a button or bar inside it and arrow buttons on either side. Point to the small arrow button at the left of the scroll bar and click. The worksheet shifts left by one column. Do it once more. Now scroll back to the right, with the arrow at the right of the scroll bar, until the 27 reappears at the lower right.

  • Scroll horizontally with the slider bar. Move the pointer to the slider bar (the button within the scroll bar). Hold the left mouse button down and move the pointer left and then right to scroll the worksheet horizontally.

  • Scroll vertically. Use the vertical scroll bar to the right of the worksheet to scroll up and down a row at a time (click on arrows) or continuously (drag the slider bar with the pointer).

  • Return to the upper left of the worksheet. Press [Control][Home]. Cell [A1] should appear at the upper left corner of the worksheet with the cursor in it.

    In the remainder of this tutorial, you will produce a spreadsheet that appears as follows:

    Trial Worksheet: M=     3

    Run X Y M(X-Y)
    1 2 4 -6
    2 3 4 -3
    3 6 2 12
    TOTAL 11 10 3

    You will enter the first row, the column titles, the entries for the Columns headed RUN, X, and Y, and the word TOTAL, and you will then enter formulas that Excel will use to calculate the values of M(X-Y) and the column totals.

    Entering labels and numbers in cells

    A cell can contain:

    We will show you how to enter labels and numbers in the next series of instructions, and we will eventually get to formulas.

    Note: Throughout the remainder of the exercise we will tell you to type things enclosed in angled brackets (< >) followed by [Return]: for example, type < 26.3 > [Return]. (Don't type it now.) When you see instructions like this, type what is in the brackets but not the brackets themselves and then hit the Return key.

      11. Enter a label in a cell.

    Instead of hitting the [Return] key to enter what you typed into Cell [A1], you could have clicked on the check symbol. In either case the cursor would remain on Cell [A1]. You could also have hit the down arrow key to move the cursor down to Cell [A2] or the right arrow to move the cursor right to Cell [B1] after the entry had been made in Cell [A1]. If you had wanted to cancel the editing without changing the content of Cell [A1], you could have clicked on the X symbol.

      12. Move the cursor to Cell C1 (point at it and click the left mouse button). Type < 3 >.

      13. Type [Return] to enter the 3 in Cell C1.

      14. Move the cursor to Cell A3. Type RUN[Return].

      15. Justify and center labels.

      16. Enter a number and move the cursor down one cell.

      17. Enter 2 and 3 respectively in Cells A6 and A7, moving down with each entry.

    At this point the worksheet should appear as follows (with slightly different spacing):

    Trial Wormsheet: M =
    RUN X Y
    1 5 4
    2 3 4
    3 6 2
    TOTAL   

    Not shown but still part of the worksheet is the 27 in Cell AV61.

    Erasing and editing cell contents

      18. Erase the contents of a cell. Let's now get rid of that 27.

      19. Edit (revise) the contents of a cell.

    Naming and saving a worksheet Selecting a range of cells (highlighting a region of the worksheet)

      20. Select a range with the mouse.

      21. Deselect.

      22. Select a range using the Select command.

      23. Select an entire column, an entire row, or the complete worksheet..

    Copying Cells

    Saving the revised worksheet.

      24. Choose the File menu and select Save. Alternatively, click the disk icon on the tool bar. The current version of the spreadsheet will be saved under the same file name (mod1.xls).

    You should never work for more than three minutes without saving. If the system crashes while you are working, you will lose everything you have done since the last save.

    Quitting Excel

      25. Choose the File menu and select Exit.

    You can now log off and take a break or proceed directly to Step 26 to continue the exercise. Before you log off, be sure to close the windows emulator.

    * * * * *

    Recalling stored worksheets

    If you logged off before, bring up Excel again. (On a UNIX workstation, press the middle mouse button to bring up the Applications Menu and select MS-Windows Emulators - Soft Windows; under Program manager double click on MS Office; double click on Excel.)

      26. Open the previously saved file.

    Entering Formulas

      27. Enter a column heading.

      28. Enter a formula in a cell.

    You have just entered the formula D5 = C1*(B5-C5). The value in Cell D5 will be that in Cell C1 (3) times the difference between the value in Cell B5 (5) minus that in Cell C5 (4). The formula appears on the edit line and the calculated value (3) appears in the cell on the worksheet. (We will explain the dollar signs in front of the column and row labels of Cell C1 when we talk about copying formulas.)

    Note the following points about formulas:

      29. Enter a formula by pointing and clicking on component cells.

    Keeping the cursor on Cell D5,

    The same formula has now been entered in Cell D5, with the same result.

    In short, you can insert a cell address in a formula by typing it in or by pointing at the cell and clicking the left mouse button.

      30. Change the value of a cell that appears in a formula.

    Copying a Formula

    We now want to copy the formula in Cell D5 into Cells D6 and D7, but not to copy it exactly. We want each cell in Column D to contain the difference between the values in the same row of Column B (X) and Column C (Y) multiplied by the value in C1. Thus, the formula in Cell D6 should be D6 = C1*(B6-C6), and that in Cell D7 should be D7 = C1*(B7-C7). This task turns out to be very easy to accomplish with Excel. Excel will automatically change the cell names when a formula is copied.

      31. Copy a formula in one cell into selected adjacent cells.

    How did Excel know which cell addresses should go in the copied formulas? The answer follows.

    Relative and Fixed Cell Addresses in Formulas

  • Sometimes you may want to refer to a fixed cell address in a formula, i.e., a cell address that should not change when you copy the formula to a new cell. For example, we did not want Cell C1 in our formula to change to C2, C3, etc., when we copied to lower formula cells. To keep a cell address fixed, we precede the row and column identifiers of the fixed address in the formula with dollar signs. Thus, $C$1 in a formula will always be Cell C1, regardless of where on the worksheet the formula is copied. ($C1 would fix the column but would let the row vary.)

    Excel Functions

      32. Move to Cell B9. Type < =SUM(B5:B7) >[Return].

    You have just used an Excel function called SUM, which calculates the sum of the cell values in its argument (in this case, Cells B5 through B7).

    Excel has a variety of built-in mathematical functions (ABS, EXP, LN, SIN, etc.), statistical functions (AVERAGE, MAX, MIN), financial functions, and others. Some of these functions are listed at the end of this tutorial.

      33. Copy the formula in Cell B9 into Cells C9 and D9.

    You copied the formula in Cell B9 into Cells C9 and D9, changing the cell addresses appropriately. Verify that the formulas (edit line) and the values (worksheet) in Cells C9 and D9 are correct.

    The worksheet should now appear like the one shown at the beginning of this exercise.

      34. Save.

      35. Change a cell value and recalculate the spreadsheet.

    Printing a Spreadsheet

      36. Print the worksheet.

      37. Exit from Excel. Choose the File menu and select Exit.


    APPENDIX: EXCEL FUNCTIONS

    Listed below are some (but not all) of the built-in functions provided by Excel. Arguments may be numbers, cell addresses, or cell ranges [e.g. EXP(4.5), SUM(A2,A4), AVERAGE(C6:C15)].

    If a function has multiple arguments, the argument values or the addresses of the cells containing these values may be listed as ranges (C3:C9), or as individual cell addresses and/or numbers separated by commas (A4,C5,17.3), or a combination (A5,B4:B8,C3). To get a complete list of functions and more information about them, use the on-line help facility.

     

    ABS(X)

     

    Absolute value of X

     

    COS(X)

     

    Cosine of X (X in radians)

     

    EXP(X)

     

    ex

     

    INT(X)

     

    Integer value of X

     

    LN(X)

     

    ln X

     

    LOG(X)

     

    log X

     

    RADIANS(X)

     

    3.14159 X/180

     

    RAND()

     

    Returns a random number uniform on the interval [0,1].

     

    SIN(X)

     

    Sine of X (X in radians)

     

    SQRT(X)

     

    Square root of X

     

    TAN(X)

     

    Tangent of X (X in radians)

     

     

     

     

     

    AVERAGE(X:Y)

     

    Average of the arguments

     

    RSQ(R1,R2)

     

    Pearson's product-moment correlation coefficient for he paired data in Ranges R1 and R2

     

    COUNT(X:Y)

     

    Count of non-blank arguments

     

    MAX(X:Y)

     

    Maximum of the arguments, not counting blank cells and cells containing text strings

     

    MIN(X:Y)

     

    Minimum of the arguments

     

    SUMSQ(X:Y)

     

    Sum of squares of the arguments

     

    STDEV(X:Y)

     

    Sample standard deviation of the arguments (N-1 weighting)

     

    SUM(X:Y)

     

    Sum of the arguments

     

    VARS(X:Y)

     

    Sample variance (N-1 weighting) of the arguments

     

     

     

     

     

    MDETERM(M)

     

    Determinant of the square matrix specified by Range M

     

    MINVERSE(M)

     

    Inverse of square matrix M

     

    MMUL(M1,M2)

     

    Product of multiplication of matrix M2 by matrix M1

     

    TRANSPOSE(M)

     

    Transpose of matrix