Excel Companion to Chapter 1 PDF

18 EXCEL COMPANION to Chapter 1 Excel Companion to Chapter 1 This Excel Excel, helpingCompanion serves as a primer for Microsoft you become familiar with the buttons: apr...

18 EXCEL COMPANION to Chapter 1 Excel Companion to Chapter 1 This Excel Excel, helpingCompanion serves as a primer for Microsoft you become familiar with the buttons: aprimary button (typically the left button) and. commands that everyday use of Microsoft Excel concepts and secondary button (typically the right). You move your Before you intensely study this Conmpanion, you requires. pointing device and use one of the buttons to execute ono consider how you plan to use Excel as you learn should firstIf of these six basic operations': you skipped reading the "From the statistics. Click Move the mouse pointer over an object and press the and Learning Microsoft Excel" on Authors' Desktop: Using primary button. You click links on a Web page and many of page 5, you may want to review it now so that you have a better the user interface elements identified in the "Get Set!" part choices you have in using Excel understanding of the with this text. of this section. The book also uses the verb clear when How you plan to use Excel will affect which telling you to click on a check box to remove its check skills you immediately need to know. If you plan to Excel mark. Basic Excel instructions in later use the companions, you should Select Similar to click, but when youpress the primary but know, or at least have some awareness of, just about every ton, another list of menu commands or skill discussed in the rest of this choices click the Microsoft Windows Start menu and appear. You use PHStat2, you will have a less Companion. If you plan to then select immediate need for the skills related to worksheet entries and formulas Programs or All Programs (depending on the version) to in Sections E1.3. El.4, and E1.5. discussed display a list of programs and program folders installed on (You can master such your system. skills later, as you read through this book.) The rest of this companion presents skills in an increas Double-click Move the mouse pointer over ing order of difficulty. Make sure you have mastered the click the primary button twice in rapid an object and succession. You double-click Desktop program icons to open programs, and skills presented in the first sections before going on to the you later sections. If you consider yourself an experienced Excel double-click the icons that represent files to open and use those files. user, you might want to take the time to scan this compan ion if only to become familiar with the terms used through Right-click Move the mouse pointer over an object and out the book to describe Excel objects and operations. click the secondary button. You typically right-click an object to reveal a shortcut menu of to that object. commands that apply E1.1 PRELIMINARIES: BASIC Drag A multipart mouse mouse pointer over an object operation. First, you move the COMPUTING SKILLS and holding down the primary button,then, you while pressing and If vou have ever surfed the Web, sent an instant message. pointer somewhere else on the screen and move the mouse playedmusic or games, or written word-processed assign button. You use drag to resize release the primary ments, you have already mastered the skills necessary in Excel, to select a group of adjacent windows and, in Microsott order to use Microsoft Excel. However, if you are new to worksheet cells. computing, you should use the following countdown of Drag-and-drop A multipart mouse move the mouse pointer over an operation. First, you skillson your mark, get ready, get set, go- -to master the pressing and holding down the primary object and then, while basic computing skills needed with this book. (f you are an the mouse pointer over button, you move experienced computer user, you wil! want to skim this eo the primary button. another onscreen object and release tion and note the definitions of the boldfaced terms ) but in this text, you Drag-and-drop mostly use has many applications ing PivotTables this operation when defin- Chapter 2). (discussed in the Excel Companion On Your Mark! programs, Microsoft Excel makes fre- 'Alternate methods of Like many other keyboard and a mouse-type pointing device. a expects your pointing device to have interaction, recognition input, are possible such as on suitably using speeci quent use of If youare makeequipped Microsoft Excel two using an alternate method. systenp equivalents to the mouse sure vou know e operations defined in this sectiol.  El.I: Preliminaries: Basic Computing Skills 19 If these operations are new to you, you can practice them Excel. When opening Excel, you see a window that is by opening the Mousing Practice.xls file on the Student CD. 1f vou do not know how to open a file, ask a friend or use either similar to Figure El.1 if you use Excel 97, 2000, 2002, or 20032 or Figure EI.2 if you use Excel 2007. the instructions available in Section EL.2.) Otherwise, you (Should your Excel window not contain some of the ele are ready to advance to the Get Ready!"" stage. ments shown in the figure appropriate for the Excel version you use, see the "Microsoft Excel FAQs" section in Get Ready! Appendix F. As youbegin using Excel, you will encounter When you start Microsoft Excel, you see a window that con dialog boxes, special windows that display messages to tains the Excel user interface and a workspace area that dis allow you to make entries or selections. Because you will plays open workbooks. If you start any Excel version other be frequently interacting with dialog boxes, you should be than 2007, you will see a window very much like the one familiar with the objects you will commonly see in these shown in Figure E1.1, the actual window for Excel 2003. special windows (see Figure E1.3 on page 20). If you start Excel 2007, you will see the very different The rest of this section defines the objects labeled in looking window, similar to the one shown in Figure E1.2 on Figures E1.1, EI.2, and El.3. You should be familiar with page 20. the objects that apply to the Excel version you use before In designing Excel 2007, Microsoft tried to minimize continuing to the next section. the find-and-seek process that many users of earlier versions Excel Window Elements (all versions) experience trying to find a particular command or Excel fea Minimize, resize, and close buttons minimize (that is, hide ture. Generally, Excel 2007 displays all relevant commands without closing), resize, and close windows. When you for a particular task at the top of the window. If you are new click the sets of labeled buttons in Figures E1.1 and El.2, to Microsoft Excel and have a choice of using Excel 2007 or you affect the Excel window itself; other, similar buttons an earlier version, you will most likely benefit from choos operate on other elements in the Excel window, such as the ing Excel 2007 because displaying all relevant commands currently opened workbook. lessens the initial training you need. However, Excel 2007 is Workspace area displays the currently opened workbook functionally equivalent to earlier Excel versions, and every or workbooks. (Although you will not need to open more Excel activity in this text can be done with any version of than one workbook at a time when using thistext, you can Excel, starting with Excel 97, although sometimes there are open multiple workbooks and view all of them by resizing special instructions specific to Excel 2007. them to fit in the workspace area.) Get Set! 2in the remainder of the book, the labels "Excel 97-2003" or lo get started using Microsoft Excel, you need to be famil "97-2003" are used when collectively referring to one of these lar with the objects you will commonly see when using Excel versions. MiCTOsoftExcel Book1 Title bar rsert Forma Raa HashPape Wndow TITools 100% Aris 10 Minimize, resizé, and close buttons D G H Getting Started Menu barStandard toolbar Formula bar Formatting toolbar Open pen. )Oeste angw otkbock. Workspace area with Task pane opened workbook Scroll bars 16 16 Sheet tabs 17 4\Sheet1 Shset2sheet3/ Ready FIGURE E1.1 The Excel 2003 window 20 EXCELCOMPANION to Chapter l Quick access toolbar EookiAicr Osoff Eecel Title bar Home Page layout Foreulaioa Revtew Caibri NConditionat Formstting nsert Paste General Forinat ai Tsbie Deiete Söt r ind & cei Styies Format Ftes Selert homent Numüe Ces Formula bar Minimize, Resize, Tabs Group Launcher button Office (Home tab selected) and Close buttons Button Workspace area with opened wookbook 20 21 Sheet tabs Scroll bars 12 Sheetl Sheét2 Sheet3 Reat FIGURE E1.2 The Excel 2007 window Formula bar shows the name of the currently selected worksheet cell (see Section E1.3) and the current contents Title bar displays the name of the currently active work of that cell. book and contains the minimize, resize, and close buttons for the Excel window. You drag the Sheet tabs display the name of each sheet in the opened the Excel window on your title bar to reposition workbook. You click a sheet tab to select a sheet and make screen. it the currently active sheet. You double-click a sheet tab to renarme the sheet. Additional Window Menu Bar The horizontalObjects (Excel 97-2003) Scroll bars allow you to travel horizontally or verti list of words at the top of the cally through parts of a worksheet that are offscreen window that represent sets of commands. You click a menu bar word and pull down lists of (for example, row 100or column T in Figures E1.l or E1.2). command which lead to further menu choices. choices, some of Open Look in: Textboak Data Fles My Recert Nane Sze Documerts Type Date Modfied hapter 2.ds 269 KB Microsoft office Exc... 2/1j2007 12:05 PM GDesktop Cost of Lving.s 14 KB Microsoft Office Exc.. 2/1/2007 12:05 PM My aMsua Funds.ds 284 KB Microsoft office Exc... 2/i/2007 12:05 PM Question-mark buttons Documerts Print My Conputer Drop-down lists Prrte yetwor. Places Nane: SBrother HL-5140 series Status: Type: Properties. List Brother HL6140 serles box Wherei Comment: USBOO1 Edit box (with Find Printer.. Set of option spinner buttons Prit range OPrint to fle buttons Copies O Page(s) rom: Number of copies:I Lo: Fle tame O Selecton OEntre workboo. Fles of tYpe:A Maosoft office Excel F Aciye sheet(s) yCglote Jlgnore orit areas Check box Toos Prevle OK & Cancel buttons FIGURE E1.3 Commonly encountered elements OK Cancel  El.1: Preliminaries: Basic Computing Skills 21 Standard Toolbar The toolbar that contains shortcuts to Option buttons Present a set of mutually exclusive many file-oriented commands, including the common choices. When you click one option button, all the other workbook operations discussed in Section El.4. option buttons in the set are cleared. Formatting Toolbar The toolbar that contains shortcuts to Check boxes Present optional actions that are not mutually many common worksheet cell formatting commands (see exclusive choices. Unlike with option buttons, clicking a Section El.4). check box does not affect the status of other check boxes, Task Pane A closable window that contains clickable links and more than one check boxcan be checked at a time. If that represent shortcuts to menu and you click an already checked check box, you clear the toolbar operations (Excel 2002 and 2003 only). check from the box. Additional Windows Objects Excel 2007 OK buttons (in dialog boxes) Allow you to execute tasks Office Button displays a menu of commonly issued com using the current values and settings of the currently dis mands similar to the File menu in Excel 97-2003. The Office played dialog box. Sometimes the "OK button will have a Button also gives you access to many Excel options settings. different legend, such as Finish, Create, Open, or Save. Ouick Access Toolbar (to the right of the office button) Cancel buttons Close dialog boxes and cancel operations Displays buttons that are shortcuts to commonly used com represented by the dialog boxes. In most contexts, clicking the Cancel button is equivalent to clicking the Close button mands. When you first see Excel 2007, the Save, Undo, in the title bar of the dialog box. and Redo buttons are displayed, but you can add or remove buttons in this toolbar (see "Microsoft Excel 2007-Specific Question-mark buttons Display Excel help messages. FAQS" in Appendix F to add or remove buttons.) Many dialog boxes contain a button with the legend Help that also displays help messages. Tabs Displays groups of commands and features associ ated with a single type of Excel task. The Home Tab (seen in Figure El.2) displays all commands and features associ Go! ated with making worksheet cell entries. To start using Microsoft Excel, you need to understand the Tab Groups Display named collections of related commands conventions used in Excel menus and be familiar with and features. Some tab groups, such as the Font, Alignmnent, commonly used special keys and keystroke combinations. and Number groups shown in Figure El.2 contain launcher Excel menus use these conventions (labeled in buttons that open related dialog boxes or task panes. Figure El.4): Contextual Tabs Additional tabs that appear only when An underlined letter in a menu choice represents an you are doing a specific task (that is, working in a specific "context"), such as creating charts. Contextual tabs are dis accelerator key, a keystroke that is equivalent to played with a title (for example, "Chart Tools") that selecting the choice with your mouse. appears in the title bar. - An ellipsis indicates that when you select a menu choice, a dialog box will appear. Ribbon is the collective name for the tab-and-group user A triangle marker indicates that when you select that interface exclusive toExcel 2007. (See "Microsoft Excel menu choice, you will see either another menu of 2007-Specific FAQs" in Appendix F if your Excel 2007 choices or a gallery of choices. window does not display the ribbon.) You can execute a number of frequently used menu Common Dialog Box Objects choices or Excel operations by using a combination List boxes Display lists of choices available to you. Should keystroke that is, holding down one or more keys while a list exceed the dimensions of a list box, you will see pressing another key. In Excel versions other than Excel Scroll buttons and a slider that you can click in order to 2007, many of these combinations appear next to the menu see the other choices available. choices they represent. You can print your currently active Drop-down lists Display lists of commands or choices worksheet by pressing the combination Ctrl+P (that is, when you click over them. Many drop-downs in Excel while holding down the Ctrl key, press the P key) and save 2007 display galleries, which are illustrated (and some your currently active workbook by pressing Ctrl+S. You times annotated) sets of choices. can copy (or cut) and paste worksheet cell entries by press Edit boxes Areas into which you type entries. Some edit ing Ctrl+C (or Ctrl+X) and Ctrl+V. You can use some of the special keys to execute fre boxes also contain drop-down lists or spinner buttons that you can use to complete the entry. Cell range edit boxes quently used operations. When you type an entry, pressing the typically include a button that allows you to point to a cell Escape key usually cancels that entry. Pressing the range instead of typing the range. Backspace key when typing an entry erases typed characters 22 EXCELCOMPANION to Chapter I versions othes bol to link selections. For example, in Excel New.. than 2007, "select Tools ’ Data Analysis" means that you Crl+N bar and then Open... Ctrl+0 New wouldfirst select Tools from the Excel menu choices that select DataAnalysis from the submenu of Keyboard shortcuts Sose The equivalent Excel 2007 appears after you select Tools. Analysis" Save Ctr+5 Qpen sequence "select Data ’ Data means select the (from the Save As. Accelerator Formulas tab and then select Data Analysis Save keys Save as Web Page.s. Solutions group). Ellipses Remove Hidden Data... Save Workspace... E1.2 BASIC WORKBOOK OPERATIONS File Search... Print As you work with Microsoft Excel, you will need to open Pernission Triangle workbooks to use data and results created by you or others at We Page Preview markers an earlier time. You will also need to save workbooks to Prepare ensure their future availability and to protect yourself against Page Setyp... any computer system failures that might occur as you work Print Area Send with Excel. You may also need to create new workbooks and may want to print out individual sheets for your workbooks Print Preyiew Publish for later study or use in projects and assignments. Print... Cori+P In all Excel versions, including Excel 2007, these oper Send To ations involve dialog boxes that differ only in minor ways. Properties Close While the dialog box of the version you are using may sub tly differ from the ones shown in Figure E1.3 (see page 21), Ext the instructions in this section apply to all Excel versions, FIGURE E1.4 Excel menu conventions except when otherwise noted. Opening and Saving Workbooks to the left of the cursor, one character at a time. Pressing the You open and save workbooks by selecting the storage Delete key when typing erases characters to the right of the to use and then specifying the file name of the folder cursor, one character at a time. Pressing either the Enter or You begin the process by selecting File from the workbook. Tab keys finalizes a typed worksheet cell entry. bar in Excel 97-2003 or by Excel menu When viewing a dialog box, pressing Escape usually clicking Excel 2007. In either case, a menu of the Office Button in cancels its operation. After clicking a workbook object commands appears, as such as a chart, pressing Delete deletes that object. If you partially shown in Figure E1.4. While get stuck while using Excel, you can move the mouse menus differ (the Excel 2007 menu is onthe the contents of the pointer over an object and then pause to see if Excel dis E1.4), they both have Open and Save As right in Figure plays a Tool Tip, a pop-up help message, about the object. choices lead to similar dialog boxes, shown choices. These in Figure El.3 At any time, you can also press the F1 key to display either and Figure E1.5. a help message or help search box. You select the at the top of these storage folder using the drop-down list dialog bOxes.You enter (or select the list box) a file name for the fom Textbook Conventions box. You click workbook in the File name Open (obscured in Figure El.3) or In this book, dialog box objects are usually referred to by complete the task. Save to their names or labels. You wilI find instructions such as «Click Labels" or "Click OK when referring to check Sometimes the file when saving files, youwill type before you want to chang and want to save click Save. If you use boxes and command buttons. You will find instruction your workbook in the Excel 2007 such as "Select the Lower-Tail option" when referring to lier Excel format option boxes or drop-doWn ists. When object names (*.xls) fromversions, you the Save asselect used Excel 97-2003 by e labels can vary due to context, the book uses italics click Save (shown in type drop-down list Workboos "Select variable name." of Excel and want to before you Figure E1.5). If you use any To describe a sequence of menu (Excel 97-2003) or by save data ina form ves ribbon(Excel 2007) choices, the book uses an arroW sym- programs select that cannot either Text (Tab openExcel that can be opened delimited) (*.csv) as thedelimited) workbooks, (*,txt) or CSV you might save type. (Comn  EL2: BasicWorkbook Operations 23 o tetbock bata Fles chater 2.s Cost of Lvng. xe Mual Prds, Fle pane Honework for chapter 1.x Save as (yeel 0-2003 Workbook (.) Erel Wbook (.lax) Toots Excel Macro Enabled Worlbook (.lsm) Excel inayWorkbook (xsb) XML Data (.xn) Sngle FleWeb'age (nt onbtr) FIGURE E1.5 Save As dialog box (Excel 2007 version) Likewise, if you are opening a data file that is not an Printing Worksheets Excel workbook format, you can change the file type When you want to print the contents of a workbook, you (labeled as File of type in Figure El.3). If you cannot find a file that you are sure is in the current Look in folder, should print one sheet at a time to get the best results. You changing the file type to AllFiles (**) can reveal an inad print sheets by first previewing their printed form onscreen vertent misspelling or missing file extension (the part of and then making any adjustments to the worksheet and/or the file name after the period) that otherwise prevents the to the print setup settings. file from being displayed. Toprint a specific worksheet, you first click on the sheet tab of that worksheet to make the worksheet Although all versions of Microsoft Excel include a Save command, you should avoid this choice until you gain expe the currently active one. Then you display the Print rience. Using Save makes it too easy for you to inadvertently Preview window. If you use a version of Excel other than Overwrite your work, and in Excel 2007, it saves your work 2007, select File ’ Print Preview. If you use Excel book in the new.xlsx workbook format that cannot be used 2007, click Office Button,move the mouse pointer over by Excel 97-2003. In contrast, using Save As always pre Print (do not click) and select Print Preview from the Preview and Print gallery. sents you with an opportunity to name your file and choose The Print Preview windows for all Excel versions are its file type, and using Save As is also the simplest way you similar to one another. Figure El.6 shows a partial window can create a backup copy of your workbook as you work. for Excel 2003 (top) and Excel 2007 (bottom). If the pre If youopen a workbook from a nonmodifiable source, such as a CD-ROM, Excel marks the workbook "read view contains errors or displays the worksheet in an unde only." Youmust use Save As to save a modified version of such a workbook, which is another good reason for always El Nicroso [Xet Matuat Funcb. xl, using this command. Naxt ZomPt.Setup.Margina Pogn Oroah Areve Creating New Workbooks Mtvel Fyne s Corneanbiaty osej erosot You create a new workbook through a straightforward Pnt Previge process that varies depending on the version of Excel you Pint Paga Close Piet are using. In Excel 97 or 2000, you select File ’ New. In Setuo nt Shew kiatgles Excel 2002 (also known as Excel XP) or 2003, you select File -’ New and then click Blank workbook in the New Workbook task pane. In Excel 2007, you click Office button New and in the New Workbook dialog box, you first click Blank workbook and then Create. New workbooks are created with a fixed number of Worksheets. You can delete extra worksheets or insert more sheets by right-clicking a sheet tab and clicking either FIGURE E1.6 Partial Print Preview windows (Excel Delete or Insert. 2003 and 2007 versions)  Enter or 24 EXCELCOMPANION to Chapter cellby You either pressing Tabbar. enterthe formula individual numeric and label (someim or clicking theche Preview in buttonin mark cells. You can also Close Print sirable manner, click Close (ornecessary, andreselect by the values into perform a enter form Excel 2007), make the changescustomize your printout calledtext)instructions to use calculation found values preview command. You can and making the appropri- which are Usually,formulas Formulas can in other oe result. clicking Setup (or Page Setup) ate entries in the Page Setup dialog for all Excel versions. For example, to bOx, print which is your similar worksheet column to othertask. displayed change theadisplayedresult when the values in the suppon produce automatical row and letteredworksheet ingcellschange. with grid lines and numbered a formula, you use a cell headings (similar to the appearance of the dialo8 tab in the Page Setup head- To to a cellin Sheetname!ColumRow. refer For example, addrDateas!. Onscreen), youclick the Sheet column the form worksheet that is in box and then click Gridlines and Row and the cellin the Data column Figure El.7). (You can find morof refers to use just the ColumnRow ings and click OK (see Setup in Section F4 2. You can also portion information about using Page and row for example A2, if you are to ael referrring Appendix F) a full address,worksheet as the one into which you are ente. onthe same formula. ing a Sometimes you need to refer to a group of cells,call the group of cells forms a rectangul Page Setup Page Margins HeaderFooter Sheet a cell range. If composed of cells area-for example, a group an address in the f Rrint es two adjacent columnsyou use Options.. Sheetname!Upperleftcell:Lowerrightcell. For example Data!Al:B10 refers to the 20 cells that are in roWs through 10 in columns A and B of the Data worksheet I= Prnt gidines DRow and counn headings DSlack and whte your group forms two or more rectangular areas, you enter Doreft gualty Page order Cel errors as: dsplayed the range as a list of rectangular areas separated by com ODown, then over mas, for example, Data!Al:A10,Data!C1:C10. If the nan: O Oyer, then don of the sheet contains spaces or special characters, such as "City Data" or "Figure-1.2", you must enclose the shet Cancel name in a pair of single quotes, as in City Data'!Al:AIO FIGURE E1.7 or 'Figure-1.2"!Al:A10. Sheet tab of the Page Setup dialog box (Excel (Because you use names of sheets in formulas. 2003 version) you may want to rename sheets from their detauit names. As mentioned in Section EI.1, you can sheet by renau When you are ready to print, you can click Print in the Print Preview window, but to gain maximum control over ing a new, more double-clicking the sheet tab descriptive for the sheet,typ- name, and then pressing your printout, click Close (or Close Print Preview) and Enter.) then select File > Print (Excel 97-2003) or Office ’ Print (Excel 2007). In the Print Button dialog box that (see Figure E1.3 on page 20), you can select the use, make sure you are printing only the printer to appears Entering You enter Formulas worksheet, or print multiple copies at once.currently active by some formulas by typing the equal sign (=)followed cessing , *,1, combi nat operations.ion For of simple formulas,or the symbols t, mathematical other data pro- E1.3 WORKSHEET ENTRIES and ^ are used for the operations addition,sub- traction, As first discussed in Section 1.6 entries into worksheet cells. the (see columns and numbered rows. Youintersections use the lettered number raisedmul t page 11), you make of itoplicaation, division, and power), respectively. For exponentiation(3 example.the your pointing device move cell to cursor keys a or 3For worksheet and to select a cell for pointer through cases in which type an dif erently located cellsyouon need to distinguish between two similar entry. As a entry, it appears in the formula bar (see you thatFigures intwo E1.2on pages 19 and 20), and you place entry EI.1 and workbooks, you two similarly named worksheets into the Wo"[Crkhbaoptekrn1a]mDealtSa'h!Ae1tname' use Data worksheet in the to use an address in the form refer1Coltouthe mnRow. example,you For first cell on the  El.4: Worksheet Formatting 25 Insert Function Search for a function: Type a brief descriotkon of what you want to do and then G clck Go Or select a çategory: Most Recently Used Select a functiog: Function Argunents SM FREQUENCY NORMSDIST TF Number1 {4;2;2;5; AVERAGE HYPERLINK Number2 COUNT SUM(nunmber 1,un 13 Adds all the numbers i Adds allthe numbers in a range of cells. Number1: numberl,number2,... are 1to 255 nunbers to sun. Logical vahues and tet are ignored in cells, included if typed as argurnerks. Help gn this hunstan Formula result 13 Helh on ths fungten OK Carcei FIGURE E1.8 Insert Function dialog box of the Function Wizard formula =Data!B2 + Data!B3 + Data!B4 + Data!B5 To restore the original view, the results of the formulas, adds the contents of cells B2, B3, B4, and BS5 of the Data press Ctrl+\ a second time. worksheet and displays the sum as the value of the cell Asyou create and use more comnplicated worksheets, containing the formula. You can also use worksheet you may want to visually examine the relationships functions to simplify formulas. For example, the formula among a formula and the cells it uses (called the prece =SUM(Data!B2:BS) uses the worksheet SUM function dents) and the cells that use the results of the formula (the to create a shorter equivalent to the example in this para dependents). To display arrows that show these relation graph. ships, use the formula auditing feature of Excel. For Excel When you are using worksheet functions, you can use 97 or 2000, select Tools ’ Auditing and for Excel the Function Wizard to minimize your typing. To use this 2002-2003, select Tools > Formula Auditing. Then wizard, which is similar in all Excel versions, you select select one of the choices on the auditing submenu. To use Insert ’ Function (Excel 97-2003) or Formulas ’ this feature In Excel 2007, select Formulas and then Function Wizard (Excel 2007) and then make entries and select one of the choices from the Formula Auditing selections in one or more dialog boxes. For example, to group. The Remove All Arrows choice restores your enter the formula =SUM(Data!B2:B5) using this wizard, display by removing all auditing arrows when you are fin you select the function SUM and click OK in the Insert ished auditing the formulas. Function dialog box (obscured in Figure E1.8). You then type or point to the cell range B2:B5 in the Numberl box and click OK in the Function Argument dialog box (also shown in Figure E.8). The wizard then enters the com E1.4 WORKSHEET FORMATTING pleted formulas in the currently active cell. You can use various formatting conmmands to enhance the As Figure El.8 shows, the Function Wizard also pre appearance of your worksheets. If you use Excel 97-2003, views the results ofthe SUM function (13) and displays the you will find many of the common formatting operations contents of the range B2:B5 (4, 2, 2,and 5). on the Formatting Toolbar (see Figures El.1 andE1.9) and the rest inside the Format Cells dialog box (select Format ’ Cells to view this dialog box). If you use Excel 2007, Verifying Formulas most formatting operations are visible on the Home tab Whether you enter formulas on your own in a new work (see Figures El.2 and El.9), and the rest are available sheet,open a workbook that contains formulas, or use com through the launcher buttons of several groups or the mands that add formulas to worksheets, you should review Format choice of the Cells group. and verify formulas before you use their results. To view Use Figure El.9 as a visual guide for locating the com the formulas in a worksheet, press Ctrl+' (backtick key). mon formatting operations. 26 EXCEL COMPANION to Chapter 1 -Fill & Font Color Arial Font Face Increase & -Borders & Size Boldface Left Merge-and-Center Percent Decrease Decimal Viookt Microsot Exee! ome hseit Poetayot Prmuas Dsta Revieve View Calibrt General Conditional Fornatting Insert 11 Paste % Format sTsble Delete Celi Styles Format - Sort & Find & Cpbosd Fitter Setet Aikgniert Nisnber Styes Edting Borders Fill & Font Color rigoRE E1.9 Formatting Toolbar (Excel 97-2003) and Home tab groups (Excel 2007) Common Formatting Operations worksheet examples in this text use the color "Light Font Face & Size Drop-down lists set Turquoise" to tint areas that contain user-changable data font size for the currently selected the font face and values and the color Light Yellow to tint areas that con. cells. You will get best results if you select entire rows before tain results. These colors are available in the Excel face or size. changing the font 2002-2003 galleries and by clicking More Colors in the Boldface Displays the values of the currently selected Excel 2007 gallery. using boldface type. Consider using this for cells, cells that con In addition to these operations, you may want to tain titles, column headings, or important results. You can also use the Italic and adjust the width of a worksheet column so that its column Underline buttons to the immediate heading and all its values are clearly visible. To do right of Boldface for additional type select the entire column and then this, effects. select Left Displays the values of the Column ’ AutoFit Selection (Excel Format ’ currently selected cells left justified in their cells. The Center 97-2003) or Home and Right buttons next ’ Format (in the Cells to Left, center, and (Excel 2007). group) ’ AutoFit Selection right-justify values, worksheet with many filled columns, using respectively. In a values of categorical variables can Center for the readability. sometimes improve E1.5 COPY-AND-PASTE OPERATIONS Merge-and-Center Combines the currently selected cells, merges them There will be times that you will into one, and displays the want to copy cell entries cell of the group, centered across thevalue in the first to another part of the Consider using this for a worksheet title merged cells. sheet as well as copy ansame worksheet or to another work or a title that another workbook. Copying entire worksheet for applies to several contiguous rows or columns. insertion into Percent Displays numeric values in the currently selected entirely composed of numericandor pasting cell entries cells as percentages. The value 0.01 displays straightforward. You select the cellstext values is fairly value 1 as 100%o, and the value 100 as 10000%. as 1%, the Ctrl+C, move to the first cell of the to be copied, press want to paste the copy, and range in which you Inerease Decimal and Decrease Decimal you will copy cells press Ctrl+V. decreases the number of decimal places that willIncreases/ that contain one or However, often be used to Copying cells that contain formulas is more display numeric values in the currently selected cells. as straightforward not formulas. Particularly useful to align the decimal points in a column cells or entire as you might expect. necessarily Likewise, of numeric values. issues you need worksheets between copying Borders Drops down a gallery of border effects fro results possible. to understand inworkbooks raises some order to get the bes which you can choose to change the borders for the cur rently selected cells. Many worksheet examples in this text use one or more of these border effects: AIl Qutside Borders, and Top Border. Borders, Copying Formulas and Font Color Drop down a gallery Copying entries that contain tion because Fill you can chooseto change the cell background from whichColor color exact formulas requires extra atte type color for the currently selected cells. Many depending on how youduplicates entered addresses usinghavetheenteredmnaycellor may not lfresult, and the ColumnRow form, as introduced in addresses. you Sheetname!SectionColumnkow E1.3, Excel  El.5: Copy-and-Paste Operations 27 considers them relative references that will change to worksheet named Results to cell AS in the worksheet reflect the difference, or offset, between the original named Summary, youcan enter the formula =Results!B10 (source) cell and the cell into which you are pasting the in cell AS of the Summary sheet instead of copying formula (the target cell). For example, when you copy the the original formula. You can also use this technique to formula =A2 + B2 in cell C2down to cell C3, an offset of transfer column headings or other labeling information one row, Excel pastes the formula =A3 + B3 in C3 to between worksheets in order to maintain consistency across reflect that one-row offset. Cell ranges also get changed, so worksheets. if youcopy the formula -SUM(A1:A4) from cell A5to cell If you need to transfer information from a large B5,the formula is changed to -SUM(B1:B4). range of cells, you should consider using the Paste You typically want Excel to make these changes when Special command. To use this command, you first select you are copying a formula down a column or across a row. the cellrange to be copied and press Ctrl+C. Then select This allows you to enter the formula once and then use the first cell of the cell range in the second worksheet copy-and-paste to fill in the similar formulas in the col that is your target and right-click. Next, select Paste umn or row. Sometimes, especially in cases in which you Special from the shortcut menu that appears. In the want to copy and paste a single formula, you may not Excel Paste Special dialog box (similar for all Excel ver want Excel to make a change. You can stop Excel from sions; Figure E1.10 shows the Excel 2003 dialog box). making changes in the column or row offset by inserting a you select the Values and number formats option and dollar sign ($) before either the column letter or row num click OK. Selecting this option places the current values ber (or both) of a cell address. Addresses written with of all formulas in the second worksheet, so if inserted dollar signs, such as SAS2, are called absolute you change the underlying data, you need to repeat this references and do not change during a copy operation. procedure to update the values shown on the second For example, if you copy the formula from one cell to worksheet. another, the formula that appears in the target cell will be the same, =$A$2 + SB$2. You can also use addresses such as SA2 or A$2, if you want Excel to change only the row or column, respectively. For example, when you copy the Paste Special cell C2 formula =A2/B$10to cell C3, the formula that Paste appears in C3 is =A3/B$10. However, if you copy the OValidation cell C2 formula to cell D3, the formula that would appear O Formulas O All except borders is -B3/CS10. Do not confuse the use of the dollar sign with format O yalues O Column wicths ting cell values for currency display. To format cell values OFormas O Formulas and number formats O Valyes and number formats for currency display (in dollars and cents, in the U.S. ver O Comments sion of Microsoft Excel), you use the Currency ($) format Operation button, located to the left of the Percent button in either the O None O Multiply Formatting toolbar or the Home tab group (see Figure O Add O Diide El.9). OSubtract Copying Formulas Between Worksheets D Skip blanks Transpose When you copy and paste formulas between worksheets, Paste Link OK Cancel yougenerally want to make sure that all cell addresses con tain a worksheet name and are absolute referencesfor example, Data!SAS1:$AS12-in order to ensure consistent FIGURE E1.10 results. Paste Special dialog box (Excel 2003 version) results If you only need to transfer the calculated from one sheet to another (for example, to create your should consider 0Wn summary or report worksheet), you between Copying Worksheets using one of two methods to transfer information You can copy worksheets to a new workbook or to any Worksheets. If you need to transfer only one or a few cells' opened workbook, including the source of the copy. worth of information, consider entering formulas in the To copy a worksheet to a new workbook, you first select form =Sourcesheetname! Cellreference to transfer the infor the worksheet by clicking its sheet tab. Then you right mation into the second worksheet. For example, if youwant click the tab and select Move or Copy from the shortcut to transfer the results of a formula found in cell B10 of the  28 EXCELCOMPANION menu that to Chapter 1 However, the líttle extra effort you need appears. In tings. installed and enabled is towe (similar the for all Excel the Move versions; or Copy dialog box Figure El.11 shows add-ins are that the right add-ins bring to Excel. well woensu the ToExcel 2007 book dialog box), select (new book) from the features that In this book, you usethe Analysis ToolPak thy OK. drop-down, click Create a copy, and click is included PHStat2, a ad with Microsoft Excel. You may make Prentice Hall add-in that is d-in included on th Analysis ToolPak add-in CD. The (which Student calls simply the "ToolPak" from this point forward) the bosk Move or Copy statistical procedures to Excel, but creates Move selected sheets contain only text and numbers and no the worksheetas.s formul tha Io book: means that the results you create using (new book) ToolPak will change if you change the underlying data. (To get Before sheet: results, you would have to use the ToolPak a This is in contrast to worksheets that contain secondupdattimeej which update themselves automatically when dat formula changed.) PHStat2 adds a PHStat menu of procedures to the E.. menu bar (Excel 97-2003) or the Add-ins tab (Excel Unlike the ToolPak, PHStat2 usually creates worksheets th 2007), contain formulas and that will produce new results as th Dgeate a copy underlying data changes. Sometimes, though, PHStat2 aske DMatch destination theme the ToolPak to create sheets on its behalf, and the resulting sheets are similar to the no-formulas sheets that the ToolPak OK Cancel creates. In many such cases, PHStat2 enhances the sheets i asks the ToolPak to create, correcting FIGURE E1.11 errors the ToolPak makes or adding new formula-based Move or Copy dialog box (Excel 2007 calculations. Before you continue, you should check to see if version) the ToolPak is already installed and active in your copy of You use the same procedure, Microsoft Excel. If you are tion in the To book drop-down changing only your selec than Excel 2007, select Tools using an Excel version other and then see if Data Analysis list, to copy a worksheet to appears on the Tools menu. If you see any opened workbook, including the source of the copy. (You may want to duplicate ToolPak is installed and Data Analysis, the a worksheet in the same work book so that you can have a copy enabled. 2007, the best way to check the If you are using Excel that contaíned in the worksheet or that shows the formulas the Office Button and ToolPak status is to click then click information in an alternate format.) Whenpresents the same Office menu. Finally, you Excel Options in te of a worksheet and place it in the you make a copy Options dialog box. If click Add-Ins in the Excel same workbook, Excel Active Applications Analysis assigns the copied worksheet the sheet, plus a number, in parentheses.name For of the original and enabled. Add-Ins list, ToolPak appears the ToolPak In s is instaleu example, copy a sheet named Calculations, the copied sheet if you If the named Calculations (2). (You can and should will be need to ToolPak is not installed and consider use DVD and go your original enabled, you may renaming copied worksheets to give them more descriptive Microsoft add the through the Microsoft Ofice Office or Excel CD or use the ToolPak add-in to your copy of setup procss names.) part of PHStat2 add in, you Excel. If you plan to E1.6 ADD-INS: MAKING THINGS Appendix Fand read theshould reviewreadme book's CD. the appropriate EASIER FOR YOU PHStat2 filee on the Add-ins can simplity the task of creating something to add How to Use the to a w workbook. Add-ins are programming components not Once erly you have ToolPak rminedyouthatusetheAdd-in included in the main Excel program and may need to be installed, or added, to your computer system available for you to use separately. instal eDatad deteactive, Tools ’ and ToolPak add-in is prop Add-ins are not always because Data Analysis Analysis (Excel the ToolPak by selecting they can be disabled by other users or system security set- Data (Excel In either you will see a E1.1y2.sis dialog 2007). Figure Anal 97- -2003) or Formulas ’ box case, shown in similar to the one  El.6: Add-ins: Making Things Easier for You 29 Data Analysis Escel Optios gnalysis Tools Poputar OK Hetp keep your documents safe and your computer secure and heal Anova: Single Factor Formuts Anova: Two-Factor With Replication Trust Center Cancel Proofing Anova: Two-Factor Without Replication Teusted Pubkhers Correlation Add-i Covariance Help Adaned frusted tocabon: Descriptive Statistics OBeaurp Apgthcation k44.ins to be sgred by Trusted Pubisner Customre Addns Exponential Smoothing Dsabte alf Applicstion Add ins imy impar functionalityt F-Test Two-Sample for Variances Add Ins Acttrex Settings Fourier Analysis 1eCsnte Macr Trust Center Hstogram Resources frusted Pybh;hert Maro Settings Trutted toatins For mscros in otuments not in a trusted ocatign FIGURE E1.12 Data Analysis dialog box Drrv Ada.n Oisable al maeros vthout notfrstion O Dsable atl macros wth notification Actiye f Setting O Disabte atf macros entept digitalty signed macros To use a ToolPak procedure, you select the name of the aco Setingt Enable all macros inot recommnended, potentiafy procedure that you want to use from the Analysis Tools Aessage Ba Deveioper Maxso Settings list and then click OK. (The Histogram procedure has Externai Content Prvacy Options DTrut scces to the yBA grojed obyect modes been selected in Figure E1.12.) A second dialog box then appears, in which you make entries and selections appro FIGURE E1.14 Excel Options and Trust Center priate for the selected procedure. Click OK in this second panes (Excel 2007) dialog box to execute the procedure. issues. Then click Trust Center Settings (obscured in Figure E1.14) to display the Trust Center