Excel Formulas and Functions For Dummies. Bluttman Ken
Чтение книги онлайн.

Читать онлайн книгу Excel Formulas and Functions For Dummies - Bluttman Ken страница 4

Название: Excel Formulas and Functions For Dummies

Автор: Bluttman Ken

Издательство: Автор

Жанр: Зарубежная образовательная литература

Серия: For Dummies

isbn: 9781119076797

isbn:

СКАЧАТЬ is, a cell in which any entry would go into should you start typing. The active cell has a border around it. Also, the contents of the active cell appear in the Formula Box.

      remember When I speak of, or reference, cell, I am referring to its address. The address is the intersection of a column and row. To talk about cell D20 means to talk about the cell that you find at the intersection of column D and row 20.

      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. To the right of the Name Box, the Formula Box shows the contents of cell C7.

      technicalstuff 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 edit 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 can click the little check-mark button to finish the entry. The check-mark button is visible only when you are entering a formula. Pressing the Enter key also completes your entry; clicking the X cancels the entry.

      An alternative is to enter a formula directly into a cell. The Formula Box displays the formula as it is being entered into the cell. When you want to see just the contents of a cell that has a formula, make that cell active and look at its contents 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.

      tip If the Formula Bar is not visible, choose File ⇒ Options, and click the Advanced tab. Then, 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. 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.

      remember A range address looks like two cell addresses put together, with a colon (:) in the middle. And that’s what it is! A range address starts with the address of the cell in the upper left of the range, then has a colon, and ends with the address of the cell in the lower right.

      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.

      tip An alternative method of naming an area is to select it, type the name in the Name Box (left of the Formula Bar), and press the Enter key.

      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.

      tip The Insert Ribbon contains the button to use for inserting a table.

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 98 dollars and 60 cents? Is it a percentage? Any of these formats is correct:

      ✔ 98.6

      ✔ $98.60

      ✔ 98.6%

СКАЧАТЬ