Читать книгу Excel Formulas and Functions For Dummies - Bluttman Ken - Страница 5

Before you can write any formulas or crunch any numbers, you have to know where the data goes and how to find it again. I wouldn’t want your data to get lost! Knowing how worksheets store your data and present it is critical to your analysis efforts.

Understanding workbooks and worksheets

A workbook is the same as a file. Excel opens and closes workbooks, just as a word processor program opens and closes documents. When you start up Excel you are presented with a selection of templates to use, the first one being the standard blank workbook. Also there is a selection of recent files to select from. After you open a new or already created workbook, click the File tab to view basic functions such as opening, saving, printing, and closing your Excel files (not to mention a number of other nifty functions to boot!). Figure 1-1 shows the contents presented on the Info tab.

Figure 1-1: Seeing how to use basic Excel program functions.

Excel 2016 (also Excel 2013, Excel 2010, and Excel 2007) files have the .xlsx extension. Older version Excel files have the .xls extension.

Start Excel and select to open a blank workbook. Double-click the Blank Workbook icon and you're ready to go. When you have more than one workbook open, you pick the one you want to work on by selecting it on the Windows Taskbar.

A worksheet is where your data actually goes. A workbook contains at least one worksheet. If you didn’t have at least one, where would you put the data? Figure 1-2 shows an open workbook that has two sheets, aptly named Sheet1 and Sheet2. To the right of these worksheet tabs is the New Sheet button (looks like a plus sign), used to add worksheets to the workbook.

Figure 1-2: Looking at a workbook and worksheets.

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. 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. You have two ways to do this:

✔ Double-click the worksheet tab and then type a new name.

✔ Right-click the worksheet tab, select Rename from the menu, and then type a new name.

Figure 1-3 shows one worksheet name already changed and another about to be changed by right-clicking its tab.

Figure 1-3: Changing the name of a worksheet.

You can try changing a worksheet name on your own. Do it the easy way:

1. Double-click a worksheet’s tab.

2. Type a new name and press Enter.

You can change the color of worksheet tabs. Right-click the tab and select Tab Color from the menu.

To insert a new worksheet into a workbook, click the New Sheet button, which is located after the last worksheet tab. Figure 1-4 shows how. To delete a worksheet, just right-click the worksheet’s tab and select Delete from the menu.

Figure 1-4: Inserting a new worksheet.

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.

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, I hope you have a good reason for having so many, which brings me to the next point.

Worksheets organize your data. Use them wisely, and you will find it easy to manage your data. For example, say that you are the boss (I thought you’d like that!), and over the course of a year you track information about 30 employees. You may have 30 worksheets – one for each employee. Or you may have 12 worksheets – one for each month. Or you may just keep it all on one worksheet. How you use Excel is up to you, but Excel is ready to handle whatever you throw at it.

You can set how many worksheets a new workbook has as the default. To do this, click the File tab, click Options, and then click the General tab. Under the section “When creating new workbooks,” use the spinner control to select a number.

Introducing the Formulas Ribbon

Without further ado, I present the Formulas Ribbon. The Ribbon sits at the top of Excel. Items on the Ribbon appear as menu headers along the top of the Excel screen, but they actually work more like tabs. Click them, and no menus appear. Instead, the Ribbon presents the items that are related to the clicked Ribbon tab.

Figure 1-5 shows the top part of the screen, in which the Ribbon displays the items that appear when you click the Formulas header. In the figure, the Ribbon is set to show formula-based methods. At the left end of the Formula Ribbon, 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.

✔ Defined Names: These features manage named areas.

✔ Formula Auditing: These features have been through many Excel incarnations, but never before have the features been so prominent. Also here is the Watch Window, which lets you keep an eye on the values in designated cells, but within one window. In Figure 1-6 you can see that a few cells have been assigned to the Watch Window. If any values change, you can see this in the Watch Window. Note how the watched cells are on sheets that are not the current active sheet. Neat! By the way, you can move the Watch Window around the screen by clicking the title area of the window and dragging it with the mouse.

✔ Calculation: This is where you manage calculation settings, such as whether calculation is automatic or manual.

Figure 1-6: Eyeing the Watch Window.

Another great 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 above 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 Quick Access Toolbar tab in the Excel Options dialog box. You can put the toolbar above or below the Ribbon by clicking the small drop-down arrow on the Quick Access Toolbar and choosing an option. In this area too are the other options for the Quick Access Toolbar.

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. Some, or even all, cells could contain formulas, but that’s not the case here.

Figure 1-7: Looking at what goes into a worksheet.

Columns have letter headers – A, B, C, and so on. You can see these listed horizontally just above the area where the cells are. After you get past the 26th column, a double lettering system is used – AA, AB, and so on. After all the two-letter combinations are used up, a triple-letter scheme is used. Rows are listed vertically down the left side of the screen and use a numbering system.

You find cells at the intersection of rows and columns. Cell A1 is the cell at the intersection of column A and row 1. A1 is the cell’s address. There is always an active cell – that 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.

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.

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.

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.

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.

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.

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%

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.

Getting help

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. Chapter 2 covers the Insert Function dialog box in detail.