A BRIEF INTRODUCTION TO EXCEL SPREADSHEETING:
FUNDAMENTALS, FORMULAS, AND FUNCTIONS
Richard M. Felder
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.
Department of Chemical and Biomolecular Engineering
North Carolina State University
| A|| B|| C||
|1 || Personal Budget Sheet: November 2015|
| 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 shown above has 14 rows and four columns (A-D). In preparing it, 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 entered in Cell D6 would be
= B6-C6 (entered in Cell D6)
(all formulas begin with equal signs), which means, subtract the number in Cell C6 from the number in Cell B6 and put the result in Cell D6. 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.
As soon as input values and formulas are entered in their
cells, the spreadsheet program displays the values and the results of the formula executions. If an input cell value is changed, all formulas that include that cell address are automatically recalculated to reflect the new input value. Once the spreadsheet has been prepared it can be printed out and used to generate plots and tables to be included in a report.
Open Excel. The window that opens has features similar to those listed below. There may be differences between what we describe and what you see on your screen, however, since the exact format of Excel depends on which version you are using. Try to find the following elements or their equivalents on your screen as we describe them:
Different versions of Excel are often installed on different computers, so some of our descriptions may not exactly match what you see on your screen.
- Worksheet (or just Sheet:) 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
- Title bar. The bar at the top of the window frame with
"Book 1 - Microsoft Excel" 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.
- Standard toolbar. This toolbar contains buttons that provide convenient shortcuts for some of the more commonly used operations, including opening and saving files, printing the spreadsheet, formatting cells, cutting and pasting, and creating graphs.
- Menu bar. Select items for each of the menus shown by moving the pointer to the desired menu label (e.g. File or Format or Tools) and clicking the left mouse button, and clicking again on the desired menu selection.
- Formatting toolbar. The formatting toolbar consists of three rows of buttons and entry windows. The top row contains labels ("Edit", of the categories displays the font used for text and numbers (Calibri is often default), the font size (11 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.
- Formula bar with address window and edit line. The line just above the
worksheet column headings (A B C...) contains a narrow window (the address window), three buttons (X, check mark, fx) 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 any combination of letters and numbers now, but don't hit the return key.
- Once anything is typed, it appears in both the cursor cell [A1] and the edit line. If you click on the X button on the formula bar, the entry in the cell remains unchanged. If you click on the check mark, what you typed will be entered in the cell and the cursor will remain in the same cell. If you click on the [Return] key, the cursor moves down one cell. Try all three options. (Don't worry about the fx button for now.)
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
In the remainder of this tutorial, you will produce a spreadsheet that looks like this:
- 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.
- 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.
- Go directly to a named cell. Change the cell address in the narrow window directly above the worksheet on the left of the Excel window from A1 to D30 and hit [RETURN]. The cursor should now be in Cell D30.
- Return the cursor to Cell A1.
- 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. Type 27 [RETURN]. The number you typed should appear in Cell AV61.
- 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. Go back to Cell A1.
You will enter the first row, the column titles in Row 3, 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.
| || A|| B|| C||
|1 || Trial 324: M = || 3 || |
| 2 |
| 3 || Run || X || Y || M(X-Y) |
| 4 || 1 || 2.00
|| 4.0 || -6.0 |
| 5 || 2 ||
3.00 || 4.0 || -3.0 |
| 6 || 3 || 6.00
|| 2.0 || 12.0 |
| 7 || TOTAL ||
11.00 || 10.0|| 3.0 |
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.
- a number, such as 4, -6.82, and 3.5e-2 (3.5x10-2, 0.035).
- a label--any string of characters, like Run, X25, and &^#%^. If you enter X25 into a cell, it will be treated as a string of three characters unless it is part of an expression beginning with an = sign, in which case the entry will be considered part of a formula.
- 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.
Note: Throughout the remainder of the exercise we will tell you to type things enclosed in angled brackets (< >) followed by [RETURN] (for the Return key): for example, you may see an instruction like type <
26.3 >[RETURN]. (Don't type it now.) When you see such instructions, type what is in the brackets but not the brackets themselves and then hit the Return key.
11. Enter a label in a cell.
12. Enter and position labels and numbers in cells.
- Move the cursor to Cell A1 if it is not already there.
- Type < Trual 324: M = >. For the moment, don't follow it with
[RETURN]. (Don't type the brackets, and really type "trual"). 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.
Notice that the cursor moved down to Cell A2 as soon as you hit the return key. Instead of doing that, you could have clicked on the check symbol on the formula bar and the cursor
would then 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 you had typed the entry. If you had wanted to
cancel the editing without changing the content of Cell A1, you could have
clicked on the X on the formula bar.
Erasing and editing cell contents
13. Erase the contents of a cell. Let's now get rid of that 27.
- Move the cursor to Cell AV61 (type AV61 in the address window).
- Choose the Edit menu and select Clear. Alternatively, you can just hit the delete or backspace button. The 27 should disappear from Cell AV61. (The power of the Clear command is that you can highlight a large group of cells and clear them all with a single command.)
- Send the cursor back to Cell A1.
14. Edit (revise) the contents of a cell.
We now observe that the word "Trual" in Cell A1 should really be "Trial". Move the pointer next to the offending u in the edit line. Hold down the left mouse button and drag the pointer over the u, so that it (and only it) is highlighted. Type < i > [RETURN]. The corrected text should appear in Cell A1.
15. Save the spreadsheet as a named Excel file.
Choose the File menu and select Save As in the dialog window that comes up, change directories if necessary, and in the "Save as" window type <mod1>[RETURN]. The spreadsheet file has now been stored under the name mod1.xlsx 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
black rectangle with white markings on it (once known as a floppy disk icon).
Selecting and operating on a range of cells
16. Select and deselect a range of cells.
- Press and hold on Cell A3 and drag the pointer to C7. Move the pointer to Cell A3. Hold down the left mouse button, and while keeping it depressed,
drag the pointer to Cell C7, and release the button.
The shaded rectangular region defined by your starting point (Cell A3) and finishing point (Cell C7) has been selected. A rectangular range like this one is designated in Excel as A3:C7. Ranges may be selected in several different ways other than the one just shown, and can be easily deselected.
- Point and click on any cell. The selected range A3:D7 is now deselected.
- Click on the A above the first column. The entire column has been selected. Then click on the 8 to the left of the eighth row. The entire row has been selected.
- Point and click on Cell A3. Then, holding the shift key down with one finger, click on C7. The range A3:C7 is again selected. This is a good method for selecting a very large range of cells that doesn't fit on a single screen and cannot be easily selected by dragging the pointer across it. Don't deselect the range
17. Copy, cut, paste, and clear cell contents.
Once you have selected a range, you may carry out a number of operations on the cells in the range. If you deselected the range A3:C7, reselect it.
18. Format cells and cell ranges.
- Copy. Select the Copy command under the Edit menu or click on the Copy icon on the standard toolbar (the icon that looks like one sheet of paper overlapping another one). The Copy command inserts the contents of the selected range into a storage buffer without removing them from their current location.
- Paste. Click on E3. Select the Paste command under the edit menu, or click on the Paste icon on the standard toolbar or the left side of the formatting toolbar (the icon that looks like a clipboard with a sheet of paper on it). The contents of the storage buffer now appear with the upper left cell on E3. The cell contents still remain in the storage buffer, so you could paste them into another location on the worksheet if you choose to by clicking on the upper left cell of the desired location and selecting Paste again. (Don't.)
- Cut and Paste. Select the range E3:G7, and select the Cut command under the edit menu or click on the Cut icon on the standard toolbar (the icon that looks like a pair of scissors). The contents of E3:G7 go into the storage buffer. Depending on your version of Excel, the contents of E3:G7 will either disappear or remain where they are. Then, click on I3 and select the Paste command. What was in E3:G7 will then disappear and reappear in I3:K7.
- Clear (erase) Select the range I3:K7, and select the Clear command under the edit menu or the Clear button at the upper left of the formatting bar. A window will come up asking you whether you want to clear everything in the cell or any of several other options. Let's not worry about those now, and just click on "All." The contents of the selected range will disappear.
If you mistakenly deleted something you didn't want to delete, you can undo your last action by selecting "Undo" under the edit menu or clicking on the left-curving arrow on the standard toolbar. Try it, and then clear I3:K7 again.
Cells may contain numbers and alphanumeric labels, and numbers can be displayed in a variety of ways. For example, 15.23 may be displayed exactly like that, rounded off (15.2, 15), in scientific notation (1.523E+01), as currency (15.23), , or as a percentage (1523%). You can use the Format --> Cells command (select Cells under the Format menu) to specify the format you want to use for the cells in a selected range.
- Select cells B4:B7 (drag the pointer over B4, B5, B6, and B7 with the shift key down, or click on B4 and then click on B7 holding the shift key down). Go to the "Format" menu on the menu bar and select "Cells". In the window that appears, click on "Number" under "Category" and specify 2 decimal places, and click on "OK". The numbers in the selected range will be displayed with two decimal places. When a number is later inserted into B7, it will also have two decimal places.
- Select C4:C7. Format those cells to be numbers with one decimal place following the above procedure.
19. Save the revised spreadsheet.
Select File --> Save. The spreadsheet will be saved under the previous file name (mod1.xlsx).
You should never work for more than 2-3 minutes without saving. If the system crashes while you are working, you could lose everything you have done since the last save.
20. Choose the File menu and select Exit.
You can now take a break or proceed directly to Step 21 to continue the exercise.
* * * * *
Recalling a stored spreadsheet
If you logged off before, bring up Excel again.
21. Open the previously saved file.
Select File --> Open Recent --> mod1.xlsx. The spreadsheet generated previously should appear on the screen. If it does not, recreate it.
If you hadn't recently worked on the desired file, you would select File --> Open, and then find the file name mod1.xlsx and click on it to open it.
22. Enter a formula in a cell.
- Enter M(X-Y) in Cell D3 and right-justify it.
- Click on D4. Type < = C1 * (B4-C4) > [RETURN]. (Don't omit the = sign.)
You have just entered the formula D4 = C1*(B4-C4). The value in Cell D4 will be that in Cell C1 (3) times the difference between the value in Cell B4 (2) and that in Cell C4 (4). Click on D4. The formula appears on the edit line and the calculated value (-6) 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, prefix it with a left single quote mark (').)
- Any component of a formula entry consisting of one or two
letters followed by a sequence of numbers (e.g. B6, D16, AV27) 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.
23. Enter a formula by pointing and clicking on component cells.
Keeping the cursor on Cell D4,
- Type < = C1 * ( > (Don't type [RETURN] yet)
- Point to Cell B4 and click. Notice that the cell address appears in the formula on the edit line.
- Type < - >
- Click on Cell C4. Observe the result on the edit line.
- Type < ) > [RETURN].
The same formula as before has now been entered in Cell D4, with the same result. In short, you can insert a cell address in a formula by typing it in or by clicking on the cell while the formula is being edited.
24. Change the value of a cell that appears in a formula.
Change the value in Cell B4 to 5 (click on Cell B4 and type 5 [RETURN]). Notice that the value in Cell D4 immediately changes to 3, reflecting the new value in B4. Change the number in B4 back to 2.
We now want to copy the formula in Cell D4 into Cells D5 and D6, 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 D5 should be D5 = C1*(B5-C5), and that in Cell D6 should be D6 = C1*(B6-C6). This task turns out
to be very easy to accomplish with Excel.
25. 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.
- Select the cell containing the formula to be copied. Click on
- Copy the formula by clicking and dragging over the desired target range. Point to the lower right corner of Cell D4, press and hold the left mouse button, drag the cursor down to Cell D6, and release the button. The formula has now been copied, with the appropriate changes in cell addresses having been made automatically. (Check this: click on Cell D5 and look at the formula in the edit line, and then do the same for Cell D6.)
Note: This dragging technique is a quick alternative to copying and pasting when you want to copy the contents of a single cell into an adjacent range of cells.
Relative and Fixed Cell Addresses in Formulas
- When you enter cell addresses such as B4 and C4 in formulas they are relative cell addresses. To Excel, the formula (B4-C4) in Cell D4 means the value two cells to the left of the formula cell (B4) minus the value one cell to the left of the formula cell (C4).
- When you copy a formula from one cell (D4) to another (D5), the same addresses relative to the new formula cell are used. If the formula in Cell D4 involves B4, then the copied formula in Cell D5 will instead involve B5 (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 location. For example, we did not want Cell C1 in our formula to change to C2, C3,
etc., when we copied down to higher numbered rows. To keep a cell address fixed, we precede the row and column identifiers of the fixed address in the formula with dollar signs. Thus, C1 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 when the cell is copied.)
Excel has a variety of built-in mathematical functions (ABS, EXP, LN, SIN, etc.), statistical functions (SUM, AVERAGE, MAX, MIN), financial functions, and others. Some of these functions are listed at the end of this tutorial.
26. Enter a function that sums the values in Cells B4:B6.
Click on Cell B7 and type < =SUM(B4:B6) > [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 B4 through B6). Check the addition for yourself.
27. Copy the formula in Cell B7 into Cells C7 and D7.
| || A|| B|| C||
|1 || Trial 324: M = || 3 || |
| 2 |
| 3 || Run || X || Y || M(X-Y) |
| 4 || 1 || 2.00
|| 4.0 || -6.0 |
| 5 || 2 ||
3.00 || 4.0 || -3.0 |
| 6 || 3 || 6.00
|| 2.0 || 12.0 |
| 7 || TOTAL ||
11.00 || 10.00|| 3.00 |
There is one difference, however. When you copied the contents of Cell B7 into C7 and D7, you also copied the cell format, which in B7 is a number with two decimal points. The values in C7 and D7 are therefore 10.00 and 3.00. Format both cells to be numbers with one decimal point. (Select the two cells, and then select Format --> Cells, and specify that the cell contents should be numbers with one decimal point.) The spreadsheet will be the one we were aiming for.
28. Change a cell value and recalculate the spreadsheet.
Change the value in Cell B5 to 4. Notice that all cells affected by this
change (D5, B7, and D7) instantly assume their new values.
At this point, you could print the worksheet (File --> Print), expand it to include more data, or quit Excel (File --> 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) || log10X
| 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 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
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