Spreadsheeting with Excel

SPREADSHEETING WITH EXCEL
I. FUNDAMENTALS, FORMULAS, AND FUNCTIONS

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

(Revised by E.M. Wilcox in August 1998 and by W.R. Wilcox in March 2004)

Please email your comments and suggestions

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. (This is an example only, and is not to be done or submitted by the student. The exercise to be submitted begins below at TUTORIAL.)

	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) and 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

Excel is Microsoft's (and the world's) most popular spreadsheet program.

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.

1. Loading a stored spreadsheet. To recover a spreadsheet that you previously stored, choose the File menu and select Open. Alternatively, click the "Open File" icon (which looks like a yellow file folder being opened) at the far left of the Excel tool bar. In the dialog window that opens, point to the name of the desired spreadsheet file under "File Name" (scroll to it if necessary) and then double-click the left mouse button. The stored spreadsheet will appear in the worksheet window and its name will appear in the header. If you do not see any files, make sure that under "List Files of Type" it says Microsoft Excel Files (.xls) or, alternatively, All Files.

2. Saving. To store (save) the worksheet you have been working on, choose the File menu and select Save, or click the ``Save File'' icon (which looks like a floppy disk with an arrow pointing into it on the Excel tool bar). Name the spreadsheet in the dialog window that opens, or click on OK to save under the previous name. The file name may be anything you like, but the file type should be .xls (e.g. budget.xls). Save frequently while you are working!

3. Getting on-line help. If you have never done so, explore the on-line Help facility (use the Help menu button at the upper right of the Excel window) to get an idea of the kind of information available there. If you don't explore the facility first, you may not think of using it when you need it.

4. Quitting. To quit Excel, choose the File menu and select Exit, or double click on the horizontal bar in the upper left-hand corner. To exit the windows emulator, go to the Program Manager window and exit as you did in Excel.

TUTORIAL

Getting Started

Log on and open Excel, following the procedure given previously. The Excel window that opens has the following components (find them on the screen as we describe them):

1. Sheet (or Worksheet:) The large grid with columns labeled A,B,C,... and rows labeled 1,2,3,... You enter values (sometimes numbers, sometimes alphanumeric text) in some of the cells of the worksheet (e.g., Cell [C3], located in Column C and Row 3) and enter formulas in other cells. You can build a spreadsheet with many more rows and columns than are shown on the screen at the moment; you just can't display all of it at once. For large programs it is often convenient to work on more than one sheet. To create or switch to a different sheet, you would click on the number of the sheet you wish to change to at the bottom of the Excel window. (Don't do it now.) The Workbook is the set of worksheets that comprise a complete file.

2. Title bar. The bar at the top of the window frame with "Microsoft Excel - Book 1" in it. As soon as you save a current spreadsheet or load a new one, the spreadsheet file name will appear in the title bar.

3. Standard toolbar. This toolbar contains buttons that provide convenient shortcuts for some of the more commonly used operations, such as opening and saving files, printing the spreadsheet, formatting cells, cutting and pasting, and creating graphs.

4. Menu bar. You select items for each of the menus shown by holding down the left mouse button on the desired menu button (e.g. File or Format or Tools), dragging the pointer down to the desired menu selection, and releasing the mouse button.

5. Formatting toolbar. The formatting toolbar displays the font used for text and numbers (Arial is default), the font size (10 is default), buttons to make selected text boldface (B), italicized (I), or underlined (U), buttons to make text left-adjusted, centered, or right-adjusted within a cell, and several others.

6. Formula bar. The line just above the worksheet column headings (A B C...) contains a narrow window (the address window) and a wide window (the edit line).

· The address window should now contain [A1], indicating that the cell cursor is at Column A, Row 1. (The cursor location is the cell with the darkened border on the worksheet.) Type a number now, but don't hit the return key.

· As soon as you enter information in the cell, an X, a check mark, and an = are displayed to the left of the edit line if they weren't already there. If you click on the X, the previous value in the cell will remain unchanged. If you click on the check mark or hit [Return], what you typed will be entered. (Don't worry about the other button for now.) Try it.

· When you click on a cell, whatever is in the cell will be displayed on the edit line. As soon as you type anything, whatever you type will appear on the edit line, replacing the former cell contents.

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.

6. 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.

7. 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.

8. 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.

9. 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).

10. 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 will appear roughly 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:

· a number

· a label-- any string of characters, like RUN, X, and TOTAL

· a formula, like =(2.0+B5)/C5 (two plus the number in Cell B5 divided by the number in Cell C5). Formulas always begin with equal signs.

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.

12. Enter a label in a cell.

· Move the cursor to Cell [A1] if it is not already there.

· Type < Trial Wormsheet: M = >. For the moment, don't follow it with [CR]. (Don't type the brackets, and do really type ``Wormsheet"). What you typed should appear on the edit line. Whenever text is displayed on the edit line, you can edit it using the mouse, arrow keys, and delete key, just as though you were editing a text file in a text editor or word processing program.

· Now hit the [Return] key to insert the text in Cell A1. The text has gone over the width of Column A, but since nothing will be entered in Cell B1 this is not a problem. 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. Enter a number in a cell.

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

· Don't type [Return] yet. (If you did, type another 3.) As soon as you typed the number, it appeared on the edit line preceded by an =. If you had wanted the 3 to be considered a label, you would have preceded it with an apostrophe ('3).

· Type [Return] to enter a 3 in Cell C1.

13. Move the cursor to Cell A3. Type RUN [Return]. Notice that the text appears at the left of the cell (i.e., it is left-justified in the column). Excel does this automatically when you enter anything but a number or formula.

14. Justify and center labels. You can position a label in a cell using the justification buttons on the formatting tool bar. These buttons have icons consisting of left justified, centered, and right justified lines. Clicking on these buttons will adjust the positioning of the text in a highlighted region or in the current cell if no region is highlighted. Try it on Cell C3, ending by centering the label.

15. Enter a number and move the cursor down one cell. Move the cursor to Cell A5 and type 1. Then hit the down-arrow key to enter the number and move the cursor down to the next cell. Notice that numbers are automatically right justified.

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

17. Enter < TOTAL >[Return] in Cell A9, and then center it.

18. Move the cursor to Cell B3. (Point and click.)

19. Enter < X > [Return] and center it.

20. Enter 5, 3, and 6 in Cells B5, B6, and B7.

21. Move the cursor to Cell C3 and enter < Y > (center).

22. Enter 4, 4, and 2 in Cells C5, C6, and C7.

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

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

· Move the cursor to Cell AV61 (use the Go To command under the View menu).

· Choose the Edit menu and select Clear then All. Alternatively, you can just hit the delete or backspace button. The 27 should disappear from Cell AV61.

· Send the cursor back to Cell A1. ([Control][Home])

24. Edit (revise) the contents of a cell. We observe that the word "Wormsheet" in Cell A1 should really be "Worksheet." Move the pointer next to the offending m in the edit line. Hold down the left mouse button and drag the pointer over the m, so that it (and only it) is highlighted. Type k [Return]. The corrected text should appear in Cell A1.

Naming and saving a worksheet

25. Choose the File menu and select Save As... After a few seconds a dialog window will come up with your current directory name in the Selection window. Click on the file name and type [mod1.xls]. The file worksheet has now been stored under the name mod1.xls and the file name appears at the top of the spreadsheet window. A convenient shortcut for saving the spreadsheet is the Save button on the tool bar, which appears as a floppy disk icon. You can give a stored spreadsheet file any name you want. However, for normal applications the extension should always be .xls.

Selecting a range of cells (highlighting a region of the worksheet)

26. Select a range with the mouse. Move the pointer to Cell A5. Hold down the left mouse button, drag the pointer to Cell [C7], and release. The black rectangular region defined by your starting point (Cell A5) and finishing point (Cell C7) has been selected. Once you have selected a region, you may do a variety of things with it (don't do any of them now):

· Erase the contents of the selected cells (using the Clear command under the Edit menu).

· Copy the contents to another region of the worksheet, having them also remain where they are (using the Copy command under the Edit menu).

· Cut them from their current location and Paste them somewhere else (using the Cut and Paste commands under the Edit menu).

· Format the selected cells (using the Cells command under the Format menu) so that whatever numbers appear in them now and subsequently are written as decimals with a specified number of digits past the decimal point (fixed format), or in scientific notation, e.g. 4.1e-03 (scientific format), or with dollar signs, etc.

27. Deselect. Click the left mouse button anywhere in the worksheet outside the highlighted region. The highlighting disappears.

28. Select a range using the Select command.

· Choose the Edit menu and select the Go To command. A dialog window should come up with a cursor blinking in the selection box.

· Type < a5:b7 > [Return]. The highlight box should cover the designated region (a5 at the upper left to b7 at the lower right). (This is a convenient way to select a range that goes beyond the visible portion of the worksheet.)

· Deselect. (Point and click anywhere in the worksheet area.)

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

· Click the left mouse button on the letter at the top of any column. The entire column is selected. You can select an entire row by clicking on its number to the left of the worksheet, or select the entire worksheet area by clicking the button at the top left corner of the worksheet (above 1 and to the left of A). Try it.

· Deselect in the usual way.

Copying Cells

30. Select the range you wish to copy. Select range A3:B7 by pointing at Cell A3, holding down the left button, dragging to Cell B7, and releasing.

31. Copy the selected range. Right click on the highlighted cells, and select copy. Point to Cell D3 (the upper left cell of the region in which you want the selected range to be copied) and click the right mouse button and select paste. The range should be duplicated, with the upper left cell in the location you clicked on (Cell D3). You could have done the same thing by choosing Copy from the Edit menu and then clicking on Cell D3 and choosing Paste from the Edit menu. There are also copy and paste buttons on the toolbar.

32. Erase the cell values just inserted. Select range D3:E7 by pressing the left mouse button and dragging the pointer. Choose the Edit menu and select Clear - All. The cells should now be blank. Alternatively, you can undo the copy by selecting Edit - Undo, or by clicking the counterclockwise arrow icon on the tool bar.

Saving the revised worksheet.

33. 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

34. Choose the File menu and select Exit. You can now log off and take a break or proceed directly to Step 35 to continue the exercise.

* * * * *

Recalling stored worksheets

If you logged off before, bring up Excel again.

35. Open the previously saved file. Choose the File menu and select Open. When the dialog window comes up, point to the name of the desired file (mod1.xls), first scrolling to it or changing the drive if necessary, and then double-click with the left mouse button. The worksheet generated previously should appear on the screen. If it does not, recreate it.

Entering Formulas

36. Enter a column heading. Enter M(X-Y) in Cell D3 and center it (i.e., enter M(X-Y) [Return] and center it (button on the formatting toolbar).

33. Enter a formula in a cell.

· Move the cursor to Cell D5.

· Type < =$C$1 * (B5-C5) > [Return]. (Don't omit the = sign.)

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:

· When you begin an entry with an equal sign (=), Excel understands that what follows is either a number or a formula (as opposed to a label). (To enter a label that begins with an equal sign, you must prefix the label with a left quote mark (').)

· Any component of a cell entry consisting of one or two letters followed by one or two numbers (e.g. B6, D16, AM27) is taken to be a cell address, and the number in that cell will be substituted in the formula.

· The arithmetic operators that can go in formulas are +, -, * (multiplication), / (division), and ^ (exponentiation). For example, D6^2 signifies the square of the value in Cell D6.

· If a formula contains a string of operations, e.g. 5^2 + 3/4 * 6, all exponentiations will be done first, then all multiplications and divisions from left to right, and finally all additions and subtractions from left to right. To change this order, use parentheses.

38. Enter a formula by pointing and clicking on component cells. Keeping the cursor on Cell D5,

· Type < =$C$1*( > (no [Return] yet)

· Point to Cell B5 and click. Notice that the cell address appears in the formula on the edit line.

· Type < - >

· Click on Cell C5. Observe the result on the edit line.

· Type < ) > [Return].

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.

39. Change the value of a cell that appears in a formula. Change the value in Cell B5 to 2 (click on Cell B5 and type 2 [Return]). Notice that the value in Cell D5 immediately changes (to -6), reflecting the new value in B5.

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.

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

· Select the cell containing the formula to be copied. Point to Cell D5 and click on it.

· Copy the formula by clicking and dragging over the desired target range. Point to the little black square at the lower right corner of Cell D5, press and hold the left mouse button, drag the cursor down to Cell D7, and release.

The formula has now been copied, with the appropriate changes in cell addresses having been made automatically. (Check this: click on Cell D6 and look at the formula in the edit line, and then do the same for Cell D7.)

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

Relative and Fixed Cell Addresses in Formulas

· When you enter cell addresses such as B5 and C5 in formulas they are relative addresses. Suppose the formula cell is the cell containing the formula (D5). To Excel, the formula (B5-C5) in Cell D5 means the value two cells to the left of the formula cell (B5) minus the value one cell to the left of the formula cell (C5).

· When you copy a formula from one cell (D5) to another (D6), the same addresses relative to the new formula cell are used. If the formula in Cell D5 involves B5, then the copied formula in Cell D6 will instead involve B6 (as before, the cell two columns to the left of the formula cell).

· 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

41. 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.

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

· Click on Cell B9.

· Point to the square at the lower right of Cell B9, drag over C9:D9, and release.

· 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.

43. Save.

44. Change a cell value and recalculate the spreadsheet. Change the value in Cell B6 to 5. Notice that all cells affected by this change (B9, D6, and D9) instantly assume their new values.

Printing a Spreadsheet

45. Print the worksheet. Format the worksheet by clicking on File, Page Setup, Page, Landscape and Sheet, Gridlines & Column Headings, then Print Preview. If everything appears as you want, then click on Print and select the number of pages you wish to print.

46. Print the worksheet's formulas. Here, we also want to show the formulas. Click on Tools, Options, View, Window Options, Formulas. Then print again.

APPENDIX: EXCEL FUNCTIONS

See format conversions for the relationships between mathematical, Excel and MATLAB formats for functions.

See How to enter Greek letters, symbols, superscripts and subscripts into Excel.

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 Help. Functions can also be accessed by Insert, Function. This shows the structure of each function. Clicking on OK provides a template for entry of the information required for the function.

	ABS(X)		Absolute value of X
	COS(X)		Cosine of X (X in radians)
	EXP(X)		e^x
	INT(X)		Integer value of X
	LN(X)		ln X (natural log)
	LOG(X)		log X (log based 10)
	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)
	PI()		p
	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