Download Exploring Microsoft Excel 2007: A Step-by-Step Guide for CIS 100 Students - Prof. Charles and more Lab Reports Computer Science in PDF only on Docsity!
Spelman College Department of Computer and Information Science
CIS
Lab Book
CIS100: Introduction To Computers By: Scott Anderson Charles Hardnett Edited by: Blanche Bryant Iretta Kearse Alfred Watkins Tina Young
This lab book contains all the major handouts for the CIS 100 course. There will be other
handouts distributed in class (and also available on the class web site).
The bulk of this lab book contains reference material on how to use the Microsoft
Windows XP operating system to manage your programs and files, and instruction on
how to use the major applications that CIS 100 covers: Microsoft Word, Microsoft
Excel, Microsoft Access, and Microsoft PowerPoint. These manuals are designed for
hands-on instruction: to be read while using the application. You might bring this manual
with you whenever you use the computer, but be especially sure to bring it with you
when your class meets in the Tapley 226 computer lab and it is a laboratory day.
The last Chap of the lab book explains how to do the final comprehensive project in CIS
100. Your grade on the final project is worth one fifth of your grade for the course, so
you should pay careful attention to the instructions and advice we give. Alternative final
projects may be assigned by your instructor.
This document is a work in progress. If you have suggestions for improvements, please
let us know. The course web site is, http://www.spelman.edu/~compsci/cis100/.
5.2 Moving Around
- The bulk of the Excel window is a screen with lots of rows and columns, with light gray lines separating the boxes.
- Click at a few locations in the table. Notice that the cell that you clicked in is highlighted with a solid black border. That is the selected cell. 25
- Every cell has an address. You can tell the address of the selected cell in two ways: (1) The row and column that it is in are highlighted, and (2) the cell address is given in a special area just below the Ribbon menus called the Edit box. See if you can select cell āC9ā. How about āF7ā?
- You can also change the selected cell using the arrow keys. Try it.
- Type your name in cell A1. Press Enter when you are done; that finishes your entry into the cell. You should get in the habit of putting your name in A1 with all your spreadsheets, so that there will never be any question of whose file this is.
- Save your change by one of three methods: (1) Choose save , or (2) click the icon that looks like a floppy disk, or (3) use the keyboard shortcut, which is āCtrl+Sā. (That means to hold down the control key, marked āCtrlā on your keyboard, and type an āsā The control key is another kind of shift key.) āCtrl+Sā is a useful shortcut; use it often.
- Use the scroll bars at the right and bottom edge of your screen to move to different parts of the spreadsheet. Notice the changes to the row and column labels. The next column after Z is AA, then AB, and so forth. A Excel spreadsheet can have up to 256 columns and 65,536 rows!
- You can go to a particular location if you know its address. Click on Home tab, navigate to the Editing menu and click on āGo toā , which has a keyboard shortcut of āCtrl+Gā. When the dialog box comes up, type in IV65536 as the address to go to.
- You can return to the left by using the ( Home ) key. To return to the upper left, use Ctrl+Home. This is just like Word 2007! Note: The best way to work with any application is to make sure you are editing the correct filename in the correct location,(if necessary, use āSave Asā to save it to the correct location) and then to use āCtrl+S ā to save every so often, depending on how much work youād like to lose in the event of a catastrophe. 5.3 Labels, Values, and Formulas Now, weāll begin doing some calculations, which is the whole point of a spreadsheet. But first, we need a little terminology. There are only three kinds of things that can be in a cell in a Excel spreadsheet: (1) a label , which is a piece of text, like a word or a phrase, (2) a number , which we can calculate with, and (3) a formula , which uses the numbers to calculate other numbers.
- Go to cell A3 and type the word āBudgetā.
- Now look above the spreadsheet, just below the menu tabs and right of the sign, and youāll see a box containing the cell contents, which is the word āBudgetā This special box is called the Formula Bar. You can edit the contents of a cell by clicking in Formula Bar and editing its contents.
- Go one cell down, to cell A4, and type 2007. (Always type āEnterā when you are done.) Notice how it doesnāt line up with the word āBudgetā Thatās because, by default, numbers are right-justified and labels are left justified.
- Of course, 2007 is not a number we want to calculate with, itās just the year, and so we really want it to be a label. To force it to be a label, just add an apostrophe at the beginning. That is, type ā2007. (You can either retype it in cell A4 or you can go up to the edit box and just insert the apostrophe.) Notice that the apostrophe does not show up in cell A4; it only appears in the edit box.
- Skip down a few cells to cell A6, and type the following table. The first column is all labels, and should be in column A, rows 6-9; the second column is all numbers, and should be in column B, rows 6- 9. Food 300 Clothing 200 Shelter 700 total
- Letās save our work so far, so that we donāt lose it in the event of a crash. Just type āCtrl+Sā.
- We would like the spreadsheet to calculate the total of the second column. Suppose we did it by typing ā=300+200+700ā in cell B9. Try it. What happens?
- Sure enough, the correct total is computed. Now, go back to cell B7 and change the clothing amount so that we are spending 250 dollars. Does the total change? Of course not, since itās still calculating 300+200+700.
- What we want to do is tell the spreadsheet to compute the total using whatever number is in B7, plus whatever numbers are in B6 and B8. Go to cell B9 and type the following: =B6+B7+B8. You can use lower case (ābā) instead of āBā if you prefer, but donāt forget the initial equal sign. Again, the correct result is calculated. Go back and change some of the numbers to be sure that the formula always recalculates based on the current cell contents.
- Look up at the edit box and see that, even though cell B9 shows the number 1250, the actual contents of the cell is the formula =B6+B7+B8.
- What happens if we leave off the initial equals sign and just type B6+B7+B8 into the cell? Try it. Since it starts with the letter āBā Excel again assumes that itās a label (as it did with the word āBudgetā). Starting the formula with an equals sign tells Excel that we are entering a formula, whether it starts with a number or a letter. ( Note: you can actually start it with any mathematical operator +, - , *, etc. and it will be interpreted as a formula, but equals makes more sense.)
- It can be tedious to type cell addresses, so sometimes you just want to click on the cell you mean. Go back to cell B9, type the leading plus sign, then click with the mouse on cell B6. Notice how the cell address is typed for you. Then type a plus sign and click on cell B7. Finally, type the last plus sign, click on cell B8, and hit āEnterā. This method can be a big advantage for someone who thinks visually, rather than verbally (in terms of cell addresses).
- Still, this formula can be improved. This time, type =sum(B6:B8) into cell B9. This formula uses one of the built-in Excel functions. There are literally hundreds of them, which you can see by clicking on an empty cell, then clicking on menu item. formulas Insert Function The part of the formula within the parentheses , B6:B8 ,is a range. The two addresses indicate the upper left and lower right of a rectangular block of cells. In this case, we want Excel to sum the numbers in that range.
- Ranges are easy to specify by clicking and dragging the mouse. Letās type the formula again. First type =sum( then click and drag the mouse from B6 to B8. Finally, type the last parenthesis and āEnterā. Thatās a very easy way to add up a column of numbers.
- Try changing the formula to =average(B6:B8). Now you know the average of your budget categories. With functions and ranges, you can do lots of interesting things.
- One last formula. First, delete the current formula by pressing the āDeleteā key when cell B9 is selected.
- Because adding numbers is so common, thereās a special short-cut just for that. Up in the icon bar, youāll find an icon that looks like. For those who have forgotten their Greek alphabet, thatās a capital āSigmaā which is the Greek equivalent of āSā and mathematicians use it to stand for summation. So, when you see , think ā sum ā With B9 still selected, click on that AutoSum icon in the editing menu. Notice the area that Excel highlights. What formula did Excel put in? Hit āenterā to accept the formula.
- How did Excel figure that formula out? It looked left and up and decided you wanted to sum the column of numbers. (The same icon also works for rows of numbers.) Excel then collected cells into the range until it found a blank cell. Itās a pretty clever rule, but you should always double-check to make sure the formula does what you want.
- Save your changes with āCtrl+Sā. Typing in labels and numbers is pretty straightforward. The power of a spreadsheet comes from formulas, so practice with them, try lots of variations, and get as comfortable as you can with them.
formula? Go to cell E10 and look at the formula. Itās changed! How?
- Where it used to be =sum(B6:B8) itās now =sum(E7:E9). In both cases, the formula is adding up the three cells just above the location of the formula. In other words, the āB6ā in the formula in cell B9 doesnāt really mean B6; it means āthe cell three cells above the formulaā. When we copied the formula, it still meant that. This is called relative addressing , because the addresses used in the formula are relative to the location of the formula.
- Change the āshelterā amount in the new table to 800. Notice how the two tables are doing separate Calculations---the two totals are independent.
- Letās begin a new table. This one will be about the credit-card debt that we will rack up while weāre in college. As you know, the interest rate on a card is very important, so letās make a place for that value. Go to cell A12 and enter the label āInterest Rate:ā and in cell B enter the value ā 24%ā --yes, type the percent symbol.
- Go to row 14 and put in the following column headings: No Month Old Interest Charges Paid new
- The leftmost column is just going to be 1, 2,.... That would be tedious to type, but Excel gives us a short-cut. First, put a 1 in cell A15 and a 2 in cell A. These will be the first two elements in our series.
- Now, select the two cells with the mouse by clicking and dragging. They should be highlighted in light blue with a black border. There will be a black dot at the lower right corner of the border. This is called the ā fill handle ā.
- Move your mouse over the fill handle. The cursor will change from a thick hollow cross (like the Swiss flag) to a thin solid cross.
- Now, click and drag your mouse down the first column. Stop at cell A35 and release. Notice how the area is filled in with the numbers 1 - 21.
- The second column is going to be the name of the month, like āSep-2007ā , ā Oct- 2002 ā,.... That would be even more tedious to type than the first one, but you can almost guess what to do. Type ā Sep- 2007 ā in cell B15 , then click and drag down the column to cell B.
- Navigate to the Editing tab and click on the button; select Series , Month for Date Unit and click OK. Wasnāt that easy? Even better, itās impossible to make a typographical error.
- Now, enter our starting balance for our credit card into cell C15. Letās imagine that our initial balance is $100, so enter 100 into that cell. Your table should look something like this: No Month Old Interest Charges Paid new 1 Sep- 07 100 2 Oct- 07 3 Nov- 07
- Now weāre ready to figure out our interest. The interest rate we entered above (in cell B12) is the yearly rate, so to find the monthly rate, we just divide by 12. This interest rate is multiplied by the balance, to yield the monthly interest amount they will charge us. Enter the following formula in cell D15 (if you want, you can click on the appropriate cells, instead of typing their addresses): =C15(B12/12)* You should get 2 or, in other words, they charge you two dollars to lend you a hundred bucks for a month, at 24 percent per year. Makes sense, doesnāt it?
- This first month, our new charges will be $200, so enter 200 into cell E15. Letās pay off $100 of our charges, so enter 100 into cell F15. Our new balance is simply our old balance, plus interest, plus new charges, minus payment, or: =C15+D15+E15-F You should get 202.
- The next row is interesting, because we donāt get to make up the old balance. Instead, it needs to be the same as the new balance on the preceding row. How to do this? Try the following formula: =G This formula just ā forwards ā the balance from the preceding row. The equal sign is necessary just so that Excel knows the G15 is a cell address and not a label. Your table should look like: 1 Sep- 07 100 2 200 100 202 2 Oct- 07 202 3 Nov- 07
- The interest calculation is just the same, so letās copy/paste that from the cell above. Does the copied
formula work? Why not? Look carefully at the formula and see if you can figure out why not.
- It doesnāt work because of relative addressing. When we copied the formula, it continued to multiply the cell to the left (the old balance in C15) by the cell 3 up and 2 to the left (the interest rate in cell B12). This means the copied formula is multiplying by cell B13, which is empty, and an empty cell counts as zero.
- Look at figure 5.1, which gives a before-and- after picture of what happened. Make sure you understand it.
- The solution is absolute addressing. When we use absolute addressing, we decorate the cell address with dollar signs to mean ā no matter where I copy this formula, I always mean this exact cell.ā Go back to cell D15 , and change it to: =C15($B$12/12)* Now, copy this formula to cell D16. Does it work?
- Copy the rest of row 15 down, using click-and-drag to select the row, then copy (Ctrl+C) and paste (Ctrl+V). Your table should look like: 1 Sep 100 2 200 100 202 2 Oct 202 4.04 200 100 306. 3 Nov 1 Sep- 07 100 2 200 100 202 2 Oct- 07 202 4.04 200 100 306. 3 Nov- 07
- Now, letās fill the table in a hurry. Click and drag to select C16:G16 , which have the formulas for October. Press (Ctrl+C) to copy it to the clipboard.
- Select C17:G35. Press ( Ctrl+V ) to paste the clipboard into the range. Because the clipboard contains a short row of cells and the destination is a rectangular region, Excel copies the clipboard repeatedly.
- To find out the total interest youāve paid, just sum up column D. Itās easiest just to click on the cell below the column ( D36) and click on the Sigma. You should get just under $530. To find out the total of your charges and your payments, sum up columns E and F. Be sure to save your work regularly with (Ctrl+S). Figure 5.1: Copying a relative address changes the address. 5.6 Exercise In this section, we will learn only a few new things, but we will practice setting up a side-by-side comparison, which is a common and valuable use of spreadsheets.
- We will compare two strategies to save for a childās college tuition. Letās try to save $50,000 in 18 years; that should be enough to send our kid to an inexpensive public school. The first strategy is very safe; weāll put the money in the bank and letās assume weāll get a 4 percent interest rate. The second strategy is more risky: weāll put the money in a stock-owning mutual fund^1 and hope to average an 8 percent return. (^1) A mutual fund is like a bunch of people getting together to invest their money. For a stock mutual fund, the fund manager uses the money to buy stock in various companies, and the investors gain or lose money depending on how those companies do. (some mutual funds invest in stocks, others in bonds, others in a mixture, and there are more exotic ones , too.) A mutual fund invests in lots of different
Do the same things for the fund balance. Notice that they both say ā Too Little ā.
- The IF function is a new one for us. It needs three things:
- Something to test (must be true or false), 2) a value for when the test is true, and 3) a value for when the test is false. These three things are separated by commas. In this case, the values are just words or phrases in quotation marks (what computer people call a ā string ā). The test is a comparison between the number in a cell and another number, using the same ā greater than ā sign you learned in elementary school.
- Now, adjust the numbers in the contribution cells until you can find the smallest contribution that is enough. We got $2102 for the bank account and $ for the fund account. Notice how we have to put away over 50 percent more money if we use a nice safe bank account versus a riskier mutual fund.
- Finally, we said that the mutual fund investment was risky. What is our final value if the rate of return is only 6 percent? What if itās 10 percent? What is our total contribution under each investment strategy? Can you see why spreadsheets are so valuable, once you know how to use them? They can help you make all kinds of financial decisions. Notice, though the importance of our assumptions (such as the average annual return) and how easy the spreadsheet makes it to try different assumption
