MODULE III: NAVIGATING AND FORMULAS

1 MODULE III: NAVIGATING ANDFORMULASC opyright 2012, National Seminars Training2 NAVIGATING and FormulasEXCELiiiUsing Grouped WorksheetsWhen multiple worksheets are selected, the worksheets are grouped. If you type, edit, createformulas, or format entries in one of the grouped worksheets, entries in the same cell in all thegrouped worksheets change at the same time :1. Select the first worksheet you want to Hold down the [Ctrl]key if the other worksheets you want in the group are not adjacent,or the [Shift]key if they are, and select any additional worksheets you want to add to the Select the cells or range you want to enter information into, type the desired entry, andpress the [Enter] Select the cell or range to which you want to apply formatting and apply the desired : Be sure to ungroup the sheets before proceeding.

2 You can do this by clicking one of theungrouped sheet tabs or right-clicking one of the grouped sheet tabs and select theUngroup caution: Do not save the file while grouped unless you reallyintend and FORMULAS , continuedMoving and Copying Data Between WorksheetsInstead of cutting and pasting, you can move data between worksheets using drag-and-dropediting by holding down the [Alt]key as you :1. Select the range you want to copy or move and then use your mouse to point to theborder of the selection. Notice the pointer changes shape (Fig. 4-1) to a Move or Hold down the [Alt]key to move or [Ctrl] & [Alt]keys together to copy and then click onthe border.

3 Note that the pointer will now become a Move or Copy Start dragging the selection down toward the destination sheet tab and just kiss the Continue dragging on the new sheet, then release the mouse button and the keys whenyou have positioned the range where you want : To move or copy data from one workbook to another, make sure that you have the two ormore workbooks open, then on the Viewtab in the Windowgroup, click the Arrange All buttonand select one of the options in the Arrange Windowdialog box (or on the Windowmenu, select one of the options). You will now be able to drag and drop data betweenworkbooks using the steps and FORMULAS , continuedEXCELiiiCreating 3-D FormulasYou can create FORMULAS on one worksheet that refer to numbers on other worksheets in the sameor different workbooks.

4 In 3-D FORMULAS , the worksheet names are separated from the celladdresses by exclamation points (!).There are several ways to create the 3-D formula . The simplest way to remember is to start bytyping an = sign. Think about each of the components you want in the formula and then selectthem one at a time, remembering to insert a mathematical operator between each one. Finish byclicking the [Enter]key at the :1. Select the worksheet in which you want to enter the Select the cell in which you want to enter the formula and type =.3. Select the worksheet containing information you want to use in the Select the cell containing the information you want to use in the Type the desired mathematical Select the worksheet containing the next piece of information you want to use in the Select the cell containing the information you want to use in the Continue adding mathematical operators and cell addresses as needed to complete the Press the [Enter] and FORMULAS , continuedWorking With Range Names You can assign a name to a cell or range in a worksheet.

5 Once a name has been assigned, thename can be used in any instance where you can use a cell address. Advantages to using namesinstead of cell addresses include: 1. Names reduce the chance of error in FORMULAS . It is much easier to recognize if the nameEXPENSESis typed incorrectly. If a cell or range address is typed incorrectly, it is muchharder to detect. 2. Names adapt to changes within a range because it is absolute. (For example, when rowsand columns are added to or removed from the range). 3. Names are easy to recognize and maintain in FORMULAS . (For example, the formula =TOTALSALES-EXPENSESis easier to understand than the formula B3-C3).

6 4. You can easily move the active cell to a named cell or range using the Namebox. 5. Names created in one worksheet are available to all other worksheets in the workbook. 6. Names can refer to noncontiguous ranges or to ranges that contain blank cells, columns,or rows. 7. Names are absolute by default. If you use a name in a formula , the formula always refersto that range even if you copy or move the and FORMULAS , continuedEXCELiiiAssigning NamesYou can use names instead of cell references in FORMULAS and dialog boxes. You can use theName Boxto assign range names. Simply click in the name Box(Fig.)

7 4-3) on the formula bar,type the desired name , and press the [Enter]key. The following rules apply to naming ranges: Names must start with a letter or an underscore. The remainder of the name can containany character except a space or a hyphen. Avoid using the dollar sign ($), since it may beconfused with an absolute reference. Names can be up to 255 characters long. You should keep them short to make them easyto use and to conserve space in FORMULAS . Names are not case sensitive. They can be typed in either uppercase or lowercase. You should not use names that resemble cell references (such as A1).

8 7 EXCELiiiNavigating and FORMULAS , continuedEditing and Deleting Range NamesSteps:1. On theFormula tab in the Defined Namesgroup, click the name Managerbutton asshown in Fig. 4-4 (or select the Insertmenu, then the Namecommand, then ).2. Use the New, Edit, or Deletebuttons in the name Managerdialog box as necessary toedit or delete a range Click the Closebutton when done. 8 NAVIGATING and FORMULAS , continuedEXCELiiiJumping to a Named RangeYou can use the name Box(next to the formula bar) to jump to a named range. If the formula baris not displayed, on the Viewtab in the Show/Hidegroup, you can select the formula Barcheckbox (or use the Viewmenu to check the formula Barcheck box to display it).

9 Steps:1. Click the arrow for the Namelist on the formula Select the name of the desired and FORMULAS , continuedUsing Range Names in 3-D FormulasA 3-D formula is when cell references in a formula refer to cells in more than one worksheet. Instandard 3-D FORMULAS , you must activate the worksheets and select the cells you want to usewhile you are building the formula . When using names in FORMULAS , you can either type the nameinto the formula or select the name from the Paste Namedialog box. If range names are long, thePaste Namedialog box avoids typing :1. Select the cell in which you want to create the Type =to start the Press the [F3]key to open the Paste Namedialog Double-click the desired range Type the desired mathematical Continue to enter names by pressing [F3]repeatedly and double-clicking on the desiredrange names as necessary to complete the Finish by pressing the [Enter] and FORMULAS , continuedEXCELiiiUsing Number FormatsYou can format cells to change the way a number appears in a cell; however, formatting does notchange the underlying value.

10 The underlying value is still used in all calculations, and it appearson the formula bar when the cell is selected. Using formatting, you can add features such asdollar signs ($), percent symbols (%), commas (,), and a fixed number of decimal places. You canapply formats to a cell either before or after you enter data, and they can be applied to one cell, arange of cells, columns, rows, or the entire default format for a cell with a numeric entry is the Generalstyle. You can change the formatof numbers using the options in the Numbergroup on the Hometab (or using the buttons on theFormattingtoolbar), which apply preset number styles.