Lab 1: Excel as Calculator

1. Lab 1: Excel as Calculator

2. Let’s get started! • Open a new Excel spreadsheet. • If necessary, hit to maximize the sheet. You may need to move the top bar of the spreadsheet to see it. (It’s in the upper right hand corner.) • Within the spreadsheet, you can move around using arrow keys or the mouse. I recommend using the keyboard as much as possible- if you can do that, you’ll be quicker. • “Home” and “End” also help you jump around. • I recommend keeping track of things you do with lots of labels and even text boxes. Make a text box (under the “Insert” tab) and in it, write a message.

3. Basic Calculations • To get Excel to do arithmetic, just start by typing an = in the cell, and then type what you want Excel to solve. • Try typing =3+5 in a cell. What do you get? • How about =90/10? • Watch out for order of operations. What’s the difference between =40+30/5 and (40+30)/5?

4. Exponents • To square a number or raise it to a higher power use the ^ mark (shift-6). • What’s 63? Use Excel to find out. • To get a square root, you can use either =SQRT() or ^ with a fraction. What are two ways to get the square root of 49? Try them!

5. Relative reference • Say that you have a set of numbers and you want to add 5 to each of them. • For example, enter the numbers 5, 6, 7, and 8 in cells A1-4 of your Excel spreadsheet. • Let’s add 5 to A1-4 in cells B1-4. First, in cell B1, enter =A1+5 . What do you get? • Cut and paste that cell into B2-4. What happens? • What if you wanted to multiply each by 8? • What if you wanted to multiply each by the number below it?

6. Absolute Reference • Say that you want to multiply a set of numbers by a number that’s in another cell. For example, say that in cells C1-4, we want the product of cells A1-4 and cell A6. (Put 20 in cell A6.) • To multiply cell A1 by A6 in cell C1, you can just put =A1*A6. Put that in. Now try copying that into cell C2. What happens?

7. Absolute Reference • To “lock in” one of the values, put $’s in the cell reference, so A6 becomes $A$6. • Change cell C1 to be =A1*$A$6. Did the value in C1 change? • Copy that cell into cells C2-C4. Is it working? • Now change cell A6 from 20 to 30. It should update on its own. See why this is convenient?

8. Formulas vs. Values • Go back and click on cell C1. It should look something like this: • The number you see in the cell, 150, is the “value” in that cell. The “formula” in that cell is above, just past the fx. In this case it’s =A1*$A$6.

9. Navigation • Ctrl-arrows move you to the end of a row (or column) of information. Put the cursor on cell A1 and push Ctrl-down. You should jump down to cell A4. • Ctrl-x is cut, ctrl-c is copy, and ctrl-v is paste. How do you copy the 7 from cell A3 into cells D8, E8, and F8? • Make a new spreadsheet by clicking on this little icon at the bottom. • To go up and down a sheet, use ctrl-pageup and ctrl-pagedn.

10. Cell formatting • Go to cell D12 and type in 1/2. What happens? • This is a formatting issue- Excel assumes you’re inputting a date. Be sure your Home tab is highlighted and look for this box. • The drop-down arrow lets you change the formatting of the cell. Experiment with it. • Change a cell’s format BEFORE you enter a value into it!