Excel Formulas and Functions For Dummies. Bluttman Ken

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,