Excel Formulas and Functions For Dummies. Bluttman Ken
in the Formula Box. Cells that have formulas do not normally display the formula, but instead display the result of the formula. When you want to see the actual formula, the Formula Box is the place to do it. The Name Box, on the left side of the Formula Bar, is used to select named areas in the workbook.
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. Position the mouse pointer over the first cell where you want to define a range.
2. Press and hold the left mouse button.
3. Move the pointer to the last cell of your desired area.
4. Release the mouse button.
Figure 1-8 shows what happened when I did this. I selected a range of cells. The address of this range is A3:D21.
Figure 1-8: Selecting a range of cells.
One more detail about ranges: You can give them a name. This is a great feature because you can think about a range in terms of what it is used for, instead of what its address is. Also, if I did not take the extra step to assign a name, the range would be gone as soon as I clicked anywhere on the worksheet. When a range is given a name, you can repeatedly use the range by using its name.
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, I 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, and drag the pointer around.
2. Release the mouse button when you’re done.
You’ve selected an area of the worksheet.
3. Click Define Name in the Defined Names category on the Formulas Ribbon.
The New Name dialog box appears. Figure 1-9 shows you how it looks so far.
4. Name the area, if need be.
Excel guesses that you want to name the area with the value it finds in the top cell of the range. That may or may not be what you want. Change the name if you need to. In Figure 1-9, I changed the name to Clients.
5. Click the OK button.
Figure 1-9: Adding a name to the workbook.
That’s it. Hey, you’re already on your way to being an Excel pro! Now that you have a named area, you can easily select your data at any time. Just go to the Name Box and select it from the list. Figure 1-10 shows how to select the Clients area.
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.
With filtering, you can limit which rows show, based on which values you select to display.
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 98 dollars and 60 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.
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 percentage. The actual value in cell B2 is .05. Cell B7 is formatted as currency. The currency format displays a negative value in parentheses. This is just one of the formatting options for currency. Chapter 5 explains further about formatting currency.
Figure 1-12: Formatting data.
Besides selecting formatting on the Home Ribbon, you can use the familiar (in previous versions) Format Cells dialog box. This is the place to go for all your formatting needs beyond what’s available on the toolbar. You can even create custom formats. You can display the Format Cells dialog box two ways:
✔ On the Home Ribbon, click the drop-down list in the Number category and then click More Number Formats.
✔ 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! I discuss this dialog box and formatting more extensively in Chapter 5.
Figure 1-13: Using the Format Cells dialog box for advanced formatting options.
Excel is complex; you can’t deny that. And lucky for all of us, help is just a key press away. Yes, literally one key press – just press the F1 key. Try it now.
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.