The Basics of Excel

I. Navigating the Worksheet 1. Menu Bar 2. Sheet Tabs a. Note that you can name these tabs by right-clicking on the tab, then choose to rename. This comes in handy when working with many sheets. Rename the first tab as Start b. You can insert additional sheets by going to the [Insert] button on the menu bar, then choose [Worksheet]. Insert Sheet 4 3. Named Cells and Range a. If you have a large spreadsheet, then navigating it becomes tricky (e.g., remembering where you put things). You can choose to name a cell or range of cells by highlighting the cell or range, then [Insert], [Name], [Define], then enter a name and hit [add]. To do the following you will need to place the cursor on cell A1, hold down the left button on the mouse, then drag the cursor to A5. Name the range of cells from A1...A5 Myspace Now, anytime you want to return to this range you can either do [Edit], [Goto] or simply choose the pull down option in the lower left hand corner of the status bar (next to the cell address) and choose the Named Range. Place your cursor on cell D15, then return to you Myspace II. Entering Text Enter Lautzenheiser in cell A1 and Mark in cell B1 a. You will have noticed that my last name got cut off. You can use Excel’s autoformat function, which will adjust the width of a column to the longest entry in that column by putting the cursor between columns A and B so that the cursor becomes a vertical line with a double arrow in it, then simply left double click. Use the autoformat function to expand the width of column A. b. Alternatively you can place the cursor as before – between cells A and B, then hold down the left button of the mouse and drag the width. Manually adjust column B so that it is just wide enough for my first name c. You can make the two columns the same width by placing the cursor on the column heading A, hold down the left mouse button, then drag over to column heading B (the cursor will have turned into a down arrow). Now, whatever adjustments you make will be done to both columns. This can be done to rows as well. Make Columns A and B 20 (145 pixels) wide. d. Adjusting the fonts, bold, italics, etc. in excel is similar to Word. You can do this for one cell or for a range of cells by highlighting the range then choosing font size, bold, etc.

1

Make by last name and first name a 24 font size and adjust the column widths and row width so that my name has plenty of room. e. You can modify the contents of a cell – without retyping everything – by going to the cell, then typing in the space appearing above the column headings. Modify Cell A1 to Dr. Mark Lautzenheiser, Economics Department f. You will notice that the cell does not look very good. Excel allows you to merge cells and wrap text. Let’s see how we can make cell A1 look nicer. First, notice that we no longer need the contents of cell B1. Delete the contents of cell B1 Set the width of column A to 50 and height of row 1 to exactly 58.2 (you may need to place the cursor between rows 1 and 2, then right click and choose Row Height, which allows you to input the exact height) Wrap the text in cell A1 (once in cell A1, right click or choose Format, then Format Cells from the tool bar, goto the Alignment Tab and check the Wrap text option). Now, my name should look very nice indeed. You can do a similar thing to merge cells. Simply highlight the cells that you wish to merge (note, only one cell should have anything in it), then format the cells in the Alignment tab. Merge cells A1..C3 You can play around with all sorts of ways to format my name now by using the alignment tab. Center my name – both vertical and horizontal – and rotate it a bit Before moving on, take a look at the other options in the Format Cells window. g. You can shade the cells and use different colors for the text by simply highlighting the cell or range of cells (place your cursor on a cell, hold down the left button on the mouse, then drag), then using the colors in the upper right hand corner of the status bar. Make the letters of my name and title Red and the cell shaded Grey III. Entering Numbers a. Entering and formatting numbers is very similar to text. Enter 1 in cell F10 and 2 in cell F11 b. A handy little feature in Excel is the Autofill function. Highlight cells F10 and F11, then place the cursor at the bottom right of cell F11 until the cursor turns into a plus sign, then hold down the left button on the mouse and drag down to cell F19. Use the autofill function such that numbers 1 to 10 appear in cells F10 to F19 c. Now you can practice formatting numbers by highlighting your numbers and going to the format cells. Format your numbers to currency d. You can move cells by highlighting the range of cells, then cut and past (either right click on the mouse button or goto Edit in the menu bar). Move your range of numbers to cell C10

2

e. You can see what your spreadsheet will look like by going to [File], [Print Preview]. If you would like to put a border around the cells containing numbers, select that range, then goto Format Cells, and choose the Borders tab. Put a double line border around the numbers Now, take another look at your Print Preview. Ok, Let’s open a new Worksheet by going to [File], [New]. IV. Calculations 1. Simple numerical calculations + * / ^

addition subtract multiplication division exponent To perform simple numerical calculations, such as 2+2, you enter =2+2 into a cell. In cell A1, calculate the sum of 3, 5, 2 In cell A2, calculate the difference between 10 and 7 In cell A3, calculate the product of 5 and 4 In cell A4, divide 25 by 2 In cell A5, raise 2 to the power of 3 When done, delete the contents of cells A1..A5

2. Calculations with cells a. Performing calculations with cells is just like with numbers except that instead of entering the numbers, you enter the cells to be used in the calculations. For example, if I have 2’s in cells A1 and B1, then I can perform the calculation in any cell in the worksheet by entering =A1+B1. This can be done for as many cells as you like – though there are more efficient ways when several cells need to be used. The benefit of this is that you can enter different numbers in cells A1 and B1, and the new calculation is down automatically. In addition, as we will see, you can copy the cell with the formula. Use the autofill function to enter 1 thru 10 in cells A1..A10 and the numbers 10 thru 1 in cells B1..B10. In cell C1, sum the numbers in A1 and B1. Now, in cell C1, copy and paste the formula down to C10. Suppose you had meant to multiply the numbers instead. Goto cell C1 and modify it (don’t rewrite the whole thing – of course this doesn’t seem like a big time saver now, but it would be if you had a long formula) so that you get the product of cells A1 and A2.

3

b. You will have noticed that in copying and pasting a formula, Excel assumes that you want the cells to change. At times, you may wish to have a cell (or, parts of a cell) held constant in the formula. For example, maybe you want to multiply every entry in column A by the cell in B1. You can do this by placing $ signs around the cell you want held constant – in this case, $B$1. Modify the formula in cell C1 so that when copied to other places, the B1 cell does not change, then copy and paste cell C1 to C2..C10. c. What would happen if you inserted a new column between columns A and B? You can insert a column (or, a row) by placing the cursor on the column letter (or, row number) and left click the mouse button – this should highlight the entire column (or, row). Now, either goto [Insert], [Column] or simply right click the mouse button and choose Insert. Insert a new column between the current A and B column You will notice that the formula has been automatically adjusted. You can delete that column in much the same way as you inserted it. Delete the new column B The Insert and Delete for rows and columns comes in handy when we mistakenly leave information, have new information that needs to be added, and for better presentation. 3. Calculations with Functions a. Suppose you wanted to calculate the sum all the numbers in column A. For instance, in Cell A11, you would need to enter the following =a1+a2+a3+a4+a5+a6+a7+a8+a9+a10. This would get a bit tedious if you had a thousand numbers to add. Excel allows you to do this by using functions. You can simply place the cursor in cell A11, then hit the Summation sign (the strange capital E) in the status bar. In cell A11, sum the numbers from cells A1..A10. You see that Excel assumes that you want to sum all numbers immediately above cell A11. If, on the other hand, you wanted to sum only cells A3..A7, then modify the formula. Modify the formula in cell C11 to sum only the cells A3..A7 b. Now, suppose you wanted to calculate the mean (or, average) of the cells A1..A10. You could first use the summation formula for the entire range, then divide it by 10 (the numbers of cells): =sum(A1:A10)/10. Calculate the mean of the range A1..A10 Alternatively, you could use the Count function in Excel. This function will simply count the number of cells: =sum(A1:A10)/count(A1:10) Calculate the mean using the sum and count function Of course, things are even easier for this. Calculating means is a fairly common thing to do, thus Excel does have a function that will allow you to do this quickly.

4

Simply go back to the Function Button (that strange E in the toolbar), but use the pull down arrow, choose Average. Calculate the mean (or, average) use the Excel function c. You are not limited to columns or row for that matter. Most of these functions can be used to perform calculations on any range you wish. In cell D11, sum the entire range of cells A1..C10 d. Once you have a formula entered using the function key, you can copy that formula just like before. Copy the formula for the mean (or, average) from cell A11 to cells B11 and C11 Notice that you could have chosen to paste the cell A11 in a Special way. Suppose for instance you did not want to copy/paste the formula from A11, but just the number, then you could copy cell A11, then put your cursor on the new cell and choose Paste Special, then choose Values. Copy the number in cell A11 to cell F15 Notice all the options in the Paste Special window. For example, you could copy the column range A1..A10, then Past Special as a row by clicking on the Transpose option towards the bottom of the window. e. Finally, take a look at all the various functions. Goto the function key and choose More Functions at the bottom of the pull down window. You can enter a search for a specific formula – for example, enter variance and you’ll see various types of variances that can be calculated (e.g., population variance, sample variance). Or, you can choose to look at the categories such as finance, statistical, etc. – this is a goldmine for doing most formulas.

V. Quick look at Printing Excel spreadsheets can be very large, which makes it difficult to print sometimes. There are plenty of ways to deal with this. In the Print Window you can tell excel to fit your spreadsheet to any number of pages (including 1). Though easiest, this option may force the font size to be way too small. You can also adjust column widths and row heights if the spreadsheet is close to being able to print the way you like. Alternatively, you can add page breaks. For example, you would highlight the entire row (click on the number for the row), then go to the [Insert] option on the tools bar. a. I have placed a spreadsheet containing some financials for Proctor & Gamble. Try various ways for printing the balance sheet.

5

VI. Constructing a Spreadsheet Probably the most difficult aspect of spreadsheets for first time students is the proper structuring of the spreadsheet. The difficulty most likely arises because structuring a spreadsheet requires some planning, which takes time. Let’s jump into this with a little example. JD’s Lawn Care JD is attempting to understand the relationship between prices, quantity, and profits (net income). JD would like to set-up a spreadsheet so that he can ‘play’ with different prices and quantities to see what happens to his net income. Take the following relationships. Sales = Price x Quantity Variable Costs = $5 x Quantity Fixed Costs = $100 Net Income = Sales – Variable Costs – Fixed Costs [Here, JD had determined that his costs increase by $5 for every lawn he mows – e.g., cost of gas etc. His fixed costs arise from say interest payments made to his parents for the start-up loan. Hence, he will need to pay his parents $100 no matter how many lawns he mows.] What JD wants is a spreadsheet that will allow him to try different prices, then see what happens to net income. Or, try different quantities (i.e., lawns mowed) and see what happens to net income. See what you can do to help him out.

6