Chapter 1 In This Chapter ▶ Understanding the parts of a worksheet MA ▶ Getting the skinny on workbooks and worksheets TE RI AL Tapping into Formula and Function Fundamentals ▶ Working with cells, ranges, named areas, and tables ▶ Applying formatting D ▶ Figuring out how to use the Help system ▶ Using functions in formulas E GH ▶ Using nested functions TE ▶ Writing formulas CO PY RI xcel is to computer programs what a Ferrari is to cars: sleek on the outside and a lot of power under the ho
8 Part I: Putting the Fun in Functions show you how to build formulas and how to use the dozens of built-in functions that Excel provides. That’s where the real power of Excel is — making sense of your data. Don’t fret that this is a challenge and that you may make mistakes. We did when we were ramping up. Besides, Excel is very forgiving. It won’t crash on you. Excel usually tells you when you made a mistake, and sometimes it even helps you to correct it. How many programs do that!? But first the basics.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-1: Seeing how to use basic Excel program functions. Figure 1-2: Looking at a workbook and worksheets.
10 Part I: Putting the Fun in Functions At any given moment, one worksheet is always on top. In Figure 1-2, Sheet1 is on top. Another way of saying this is that Sheet1 is the active worksheet. There is always one and only one active worksheet. To make another worksheet active, just click its tab. Worksheet, spreadsheet, and just plain old sheet are used interchangeably to mean the worksheet. Guess what’s really cool? You can change the name of the worksheets.
Chapter 1: Tapping into Formula and Function Fundamentals Don’t delete a worksheet unless you really mean to. You cannot get it back after it is gone. It does not go into the Windows Recycle Bin. Figure 1-4: Inserting a new worksheet. You can insert many new worksheets. The limit of how many is based on your computer’s memory, but you should have no problem inserting 200 or more. Of course we hope you have a good reason for having so many. Which brings us to the next point. Worksheets organize your data.
12 Part I: Putting the Fun in Functions Ribbon is set to show formula-based methods. Along the left, functions are categorized. One of the categories is opened to show how you can access a particular function. Figure 1-5: Getting to know the Ribbon. These categories are along the bottom of the Formulas Ribbon: ✓ Function Library: This includes the Function Wizard, the AutoSum feature, and the categorized functions. ✓ Named Cells: These features manage named areas.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-6: Eyeing the Watch Window. Another new feature that goes hand in hand with the Ribbon is the Quick Access Toolbar. (So there is a toolbar after all!) In Figure 1-5 the Quick Access Toolbar sits just under the left side of the Ribbon. On it are icons that perform actions with a single click. The icons are ones you select by using the Customization tab in the Excel Options dialog box.
14 Part I: Putting the Fun in Functions Figure 1-7: Looking at what goes into a worksheet. In Figure 1-7, the active cell is C7. You have a couple of ways to see this. For starters, cell C7 has a border around it. Also notice that the column head C is shaded, as well as row number 7. Just above the column headers are the Name Box and the Formula Box. The Name Box is all the way to the left and shows the active cell’s address of C7.
Chapter 1: Tapping into Formula and Function Fundamentals If the Formula Bar is not visible, choose the Advanced tab; in the Display section in the Excel Options dialog box, choose to make it visible. A range is usually a group of adjacent cells, although noncontiguous cells can be included in the same range (but that’s mostly for rocket scientists and those obsessed with calculus). For your purposes, assume a range is a group of continuous cells. Make a range right now! Here’s how: 1.
16 Part I: Putting the Fun in Functions Say you have a list of clients on a worksheet. What’s easier — thinking of exactly which cells are occupied, or thinking that there is your list of clients? Throughout this book, we use areas made of cell addresses and ranges, which have been given names. It’s time to get your feet wet creating a named area. Here’s what you do: 1. Position the mouse pointer over a cell, click and hold the left mouse button down, and drag the pointer around. 2.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-10: Using the Name Box to find the named area. Tables work in much the same manner as named areas. Tables have a few features that are unavailable to simple named areas. With tables you can indicate that the top row contains header labels. Further, tables default to have filtering ability. Figure 1-11 shows a table on a worksheet, with headings and filtering ability. Figure 1-11: Trying a table.
18 Part I: Putting the Fun in Functions Excel lets you format your data in just the way you need. Formatting options are on the Home Ribbon, in the Number category. Figure 1-12 shows how formatting helps in the readability and understanding of a worksheet. Cell B1 has a monetary amount and is formatted with the Accounting style. Cell B2 is formatted as a percent. The actual value in cell B2 is .05. Cell B7 is formatted as currency. The currency format displays a negative value in parentheses.
Chapter 1: Tapping into Formula and Function Fundamentals This starts the Help system. From there you can search on a keyword or browse through the Help Table of Contents. Later on, when you are working with Excel functions, you can get help on specific functions directly by clicking the Help on this function link in the Insert Function dialog box. Chapter 2 covers the Insert Function dialog box in detail. Figure 1-13: Using the Format Cells dialog box for advanced formatting options.
20 Part I: Putting the Fun in Functions We use the word return to refer to what displays after a formula or function does its thing. So to say the formula returns a 7 is the same as saying the formula calculated the answer to be 7. Table 1-1 Basic Formulas Formula What It Does =2 + 2 Returns the number 4. =A1 + A2 Returns the sum of the values in cells A1 and A2, whatever those values may be. If either A1 or A2 has text in it, then an error is returned.
Chapter 1: Tapping into Formula and Function Fundamentals 5. Click a third cell. This cell will contain the formula. 6. Type a =. 7. Click the first cell. This is an important point in the creation of the formula. The formula is being written by both your keyboard entry and clicking around with the mouse. The formula should look about half complete, with an equal sign immediately followed by the address of the cell you just clicked. Figure 1-14 shows what this looks like.
22 Part I: Putting the Fun in Functions Figure 1-15: Completing the formula. Figure 1-16: A finished formula. Understanding references References abound in Excel formulas. You can reference cells. You can reference ranges. You can reference cells and ranges on other worksheets. You can reference cells and ranges in other workbooks. Formulas and functions are at their most useful when using references, so you need to understand them.
Chapter 1: Tapping into Formula and Function Fundamentals 1. Enter some numbers in many cells going down one column. 2. Click in another cell where you want the result to appear. 3. Enter =SUM( to start the function. 4. Click the first cell that has an entered value, hold the left mouse button down, and drag the mouse pointer over all the cells that have values. 5. Release the mouse button. The range address appears where the formula and function are being entered. 6. Enter a ). 7. Press Enter. 8.
24 Part I: Putting the Fun in Functions What happened? Excel, in its wisdom, assumed that if a formula in cell C2 references the cell B2 — one cell to the left, then the same formula put into cell C3 is supposed to reference cell B3 — also one cell to the left. When copying formulas in Excel, relative addressing is usually what you want. That’s why it is the default behavior. Sometimes you do not want relative addressing but rather absolute addressing.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-17: Getting ready to drag the formula down. Figure 1-18 shows what the worksheet looks like after the fill handle is used to get the formula into all the cells. This is a real timesaver. Also, you can see that the formula in each cell of column E correctly references the cells to its left. This is the intention of using relative referencing. For example, the formula in cell E15 ended up with this formula: =B15 + C15 + D15.
26 Part I: Putting the Fun in Functions Two types of errors can occur in formulas. In one type, Excel can calculate the formula, but the result is wrong. In the other type, Excel is not able to calculate the formula. Check out both of these. A formula can work and still produce an incorrect result. Excel does not report an error because there is no error for it to find. Often this is the result of not using parentheses properly in the formula.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-19: Getting a message from Excel. On the other side of the fence are errors in returned values. If you got this far, then the formula’s syntax passed muster, but something went awry nonetheless.
28 Part I: Putting the Fun in Functions Using Functions in Formulas Functions are like little utility programs that do a single thing. For example, the SUM function sums up numbers, the COUNT function counts, and the AVERAGE function calculates an average. There are functions to handle many different needs: working with numbers, working with text, working with dates and times, working with finance, and so on. Functions can be combined and nested (one goes inside another).
Chapter 1: Tapping into Formula and Function Fundamentals 4. Click the first cell with an entered value and, while holding the mouse button, drag the mouse pointer over the other cells that have values. An alternative is to enter the range of those cells. 5. Enter a ). 6. Press Enter. If all went well, your worksheet should look a little bit like ours, in Figure 1-20. Cell B10 has the calculated result, but look up at the Formula Bar and you can see the actual function as it was entered.
30 Part I: Putting the Fun in Functions Function Comment =NOW() Takes no arguments. =AVERAGE(A6,A11,B7) Can take up to 255 arguments. Here, three cell references are included as arguments. The arguments are separated by commas. =AVERAGE(A6:A10,A13:A19,A23:A29) In this example, the arguments are range references instead of cell references. The arguments are separated by commas. =IPMT(B5, B6, B7, B8) Requires four arguments. Commas separate the arguments.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-21: Using the Insert Function dialog box. Try it! Here’s an example of how to use the Insert Function dialog box to multiply a few numbers: 1. Enter three numbers in three different cells. 2. Click an empty cell where you want the result to appear. 3. Click the Insert Function button on the Formulas Ribbon. As an alternative, you can just click the little fx button on the Formula Bar. The Insert Function dialog box appears. 4.
32 Part I: Putting the Fun in Functions Figure 1-22: Getting ready to enter some arguments to the function. 8. Click the RefEdit control to the right of the Number1 entry box. The Function Arguments dialog box shrinks to just the size of the entry box. 9. Click the cell with the first number. Figure 1-23 shows what the screen looks like at this point. Figure 1-23: Using RefEdit to enter arguments. 10. Press Enter. The Function Arguments dialog box reappears with the argument entered into the box.
Chapter 1: Tapping into Formula and Function Fundamentals instead is the address of the cell that contains the value — exactly what you want. 11. Repeat Steps 7–9 to enter the other two cell references. Figure 1-24 shows what the screen should now look like. Figure 1-24: Completing the function entry. 12. Click OK or press Enter to complete the function. Figure 1-25 shows the result of all this hoopla. The PRODUCT function returns the result of the individual numbers being multiplied together.
34 Part I: Putting the Fun in Functions You do not have to use the Insert Function dialog box to enter functions into cells. It is there for convenience. As you become familiar with certain functions that you use repeatedly, you may find it faster to just type the function directly into the cell. Nesting functions Nesting is something a bird does, isn’t it? Well, a bird expert would know the answer to that one, but we do know how to nest Excel functions.
Chapter 1: Tapping into Formula and Function Fundamentals =COUNTIF(B5:B25, “>” & AVERAGE(B5:B25)). The AVERAGE function is evaluated first, and then the COUNTIF function is evaluated using the returned value from the nested function used as an argument. Nested functions are best entered directly. The Insert Function dialog box does not make it easy to enter a nested function. Try one. In this example, you use the AVERAGE function to find the average of the largest values from two sets of numbers.
36 Part I: Putting the Fun in Functions Figure 1-27: Getting a result from nested functions. You can nest functions up to 64 levels.
