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 Excel 2007 files have the .xlsx extension. Older version Excel files have the .xls extension. When Excel starts up, it displays a blank workbook ready for use. If at any time you need another new workbook, click the Microsoft Office button and click on New. You will be presented with a plateful of templates, including a blank workbook. That’s the baby you want, so give it a click to select it and then click the Create button.
10 Part I: Putting the Fun in Functions 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. Names like Sheet1 and Sheet2 are just not exciting. How about Baseball Card Collection or Last Year’s Taxes? Well, actually Last Year’s Taxes isn’t too exciting either. The point is you can give your worksheets meaningful names.
Chapter 1: Tapping into Formula and Function Fundamentals 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. Use them wisely and you will find it easy to manage your data.
12 Part I: Putting the Fun in Functions Formula Ribbon Clicking a category to access particular function Also known as Analysis ToolPak 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. In fact, the new Name Manager is here, a brand-new Excel 2007 feature.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-6: Eyeing the Watch Window. Working with rows, column, cells, ranges, and tables A worksheet contains cells. Lots of them. Billions of them. This might seem unmanageable, but actually it’s pretty straightforward. Figure 1-7 shows a worksheet filled with data. Use this to look at a worksheet’s components. Each cell can contain data or a formula. In Figure 1-7, the cells contain data.
14 Part I: Putting the Fun in Functions Getting to know the Formula Bar Taken together, the Formula Box and the Name Box make up the Formula Bar. You use the Formula Bar quite a bit as you work with formulas and functions. The Formula Box is used to enter and edits formulas. The Formula Box is the long entry box that starts in the middle of the bar. When you enter a formula into this box, you then can click the little check-mark button to finish the entry.
Chapter 1: Tapping into Formula and Function Fundamentals 1. Position the mouse pointer over the first cell where you wish to define a range. 2. Press and hold the left mouse button down. 3. Move the pointer to the last cell of your desired area. 4. Release the mouse button. Figure 1-8 shows what happened when we did this. We selected a range of cells. The address of this range is A3:D21. A range address looks like two cell addresses put together, with a colon (:) in the middle.
16 Part I: Putting the Fun in Functions 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. Release the mouse button when done. You’ve select an area of the worksheet. 3. Click Name a Range in the Named Cells category on the Formulas Ribbon.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-10: Using the Name Box to find the named area. Figure 1-11: Trying out a table. With filtering, you can limit which rows show, based on which values you select to display. The Insert Ribbon contains the button to use for inserting a table. The Insert Ribbon is shown in Figure 1-11.
18 Part I: Putting the Fun in Functions Formatting your data Of course you want to make your data look all spiffy and shiny. Bosses like that. Is the number 98.6 someone’s temperature? Is it a score on a test? Is it ninety-eight dollars and sixty cents? Is it a percentage? Any of these formats is correct: 98.6 $98.60 98.6% Excel lets you format your data in just the way you need. Formatting options are on the Home Ribbon, in the Number category.
Chapter 1: Tapping into Formula and Function Fundamentals On the Home Ribbon, click the drop-down list from the Number category, and then click More. Right-click any cell and select Format Cells from the pop-up menu. Figure 1-13 shows the Format Cells dialog box. So many settings are there it can make your head spin! We discuss this dialog box and formatting more extensively in Chapter 5. Figure 1-13: Using the Format Cells dialog box for advanced formatting options.
20 Part I: Putting the Fun in Functions To be specific, a formula in Excel calculates something, or returns some result based on data in the worksheet. A formula is placed in cells and must start with an equal sign (=) to tell Excel that it is a formula and not data. Sounds simple, and it is. All formulas should start with an equal (=) sign. An alternative is to start a formula with a plus sign (+). This keeps Excel compatible with Lotus 1-2-3. Look at some very basic formulas.
Chapter 1: Tapping into Formula and Function Fundamentals That was easy, wasn’t it? You should see the result of the formula — the number 20. Try another. This time you create a formula that adds together the value of two cells: 1. Click any cell. 2. Type in any number. 3. Click another cell. 4. Type in another number. 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.
22 Part I: Putting the Fun in Functions 9. Click the cell that has the second entered value. In this example, this is cell B6. The formula in cell E3 now looks like this: =B3 + B6. You can see this is Figure 1-15. Figure 1-15: Completing the formula. 10. Press Enter. This ends the entry of the function. All done! Congratulations! Figure 1-16 shows how the example ended up. Cell E3 displays the result of the calculation.
Chapter 1: Tapping into Formula and Function Fundamentals 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. And if that isn’t enough to stir the pot, you can use three types of cell references: relative, absolute, and mixed.
24 Part I: Putting the Fun in Functions 4. If C2 is not the active cell, click it once. 5. Press Ctrl + C, or click the Copy button in the Clipboard category on the Home Ribbon. 6. Click cell C3. 7. Press Ctrl + V, or click the Paste button in the Clipboard category on the Home Ribbon. 8. If you see a strange moving line around cell C2, press the ESC key. Cell C3 should be the active cell, but if it is not, just click it once. Look at the Formula Bar.
Chapter 1: Tapping into Formula and Function Fundamentals Copying formulas with the fill handle As long as we’re on the subject of copying formulas around, take a look at the fill handle. You’re gonna love this one! The fill handle is a quick way to copy the contents of a cell to other cells with just a single click and drag. The active cell always has a little square box in the lower-right side of its border. That is the fill handle.
26 Part I: Putting the Fun in Functions Figure 1-18: Populating cells with a formula by using the fill handle. 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.
Chapter 1: Tapping into Formula and Function Fundamentals The second type of error is when there is a mistake in the formula or in the data the formula uses that prevents Excel from calculating the result. Excel makes your life easier by telling you when such an error occurs. To be precise, it does one of the following: Excel displays a message when you attempt to enter a formula that is not constructed correctly.
28 Part I: Putting the Fun in Functions Error Type When It Happens #DIV/0! When you’re trying to divide by 0 #N/A! When a formula or a function inside a formula cannot find the referenced data #NAME? When text in a formula is not recognized #NULL! When a space was used instead of a comma in formulas that reference multiple ranges; a comma is necessary to separate range references #NUM! When a formula has numeric data that is invalid for the operation type #REF! When a reference is invalid #VA
Chapter 1: Tapping into Formula and Function Fundamentals =SUM(A1:A5) + AVERAGE(B1:B5) Returns the sum of the range A1:A5 added with the average of the range B1:B5. This is an example of a formula that combines the result of two functions. Ready to write your first formula with a function in it? This function creates an average: 1. Enter some numbers in a column’s cells. 2. Click an empty cell where you want to see the result. 3. Enter =AVERAGE( to start the function.
30 Part I: Putting the Fun in Functions A formula can consist of nothing but a single function — preceded by an equal sign, of course! Looking at what goes into a function Most functions take inputs — called arguments or parameters — that specify the data the function is to use. Some functions take no arguments, some take one, and others take many — it all depends on the function. The argument list is always enclosed in parentheses following the function name.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-21 shows the Insert Function dialog box. This great helper is accessed by clicking the Function Wizard button on the Formulas Ribbon. The dialog box is where you select a function to use. Figure 1-21: Using the Insert Function dialog box.
32 Part I: Putting the Fun in Functions RefEdit controls Figure 1-22: Getting ready to enter some arguments to the function. More argument entry boxes appear as you need them. First, though, how do you enter the argument? There are two ways. 7. Enter the argument one of two ways: • Type the numbers or cell references into the boxes. • Use those funny-looking squares to the right of the entry boxes. In Figure 1-22 two entry boxes are ready to go. To the left of them are the names Number1 and Number2.
Chapter 1: Tapping into Formula and Function Fundamentals Figure 1-23: Using RefEdit to enter arguments. 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. You do not have to use the Insert Function dialog box to enter functions into cells. It is there for convenience.
34 Part I: Putting the Fun in Functions Figure 1-25: Math was never this easy! Figure 1-26 shows the daily closing price for the S&P 500, for the month of September 2004. A possible analysis is to see how many times the closing price was higher than the average for the month. Therefore, the average needs to be calculated first, before you can compare any single price. By embedding the AVERAGE function inside another function, the average is first calculated.
Chapter 1: Tapping into Formula and Function Fundamentals The COUNTIF function counts the number of cells in a range that meet a condition. The condition in this case is that any single value in the range is greater than (>) the average of the range. The formula in cell D7 is =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.
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.