Transcription
Microsoft Excel 2007BasicsForWindowsCopyright 2009 by Massachusetts Institute of Technology All Rights ReservedPrinted on 12/8/09
Microsoft Excel 2007 Basics for WindowsTable of ContentsTable of Contents.2Module 1 – Getting Started .5Starting Microsoft Excel .5Creating and Opening Workbooks .5Creating a new workbook .5Opening an existing workbook .6Workbooks and Worksheets .6Elements of a Window .7Title Bar and Menu Bar .7Standard and Formatting Toolbars .9Zoom Control .9Active Cell .9Cell Address .9Horizontal and Vertical Scroll Bars .10Name Box and Formula Bar .10Row and Column Headings .11Sheet Tabs and Tab Scrolling Buttons .11Status Bar .12Exercise - Module 1 .13Module 2 – Saving .14Naming workbooks .14Saving an unnamed workbook .14Saving an existing workbook .14Saving a copy of a workbook .14Saving a workbook to a new location .15Saving a workbook with a new name and to a new location .15Exercise - Module 2 .16Module 3 – Entering and Editing .17Entering Worksheet Data .17Enter numbers, text, a date, or a time .17Ready and Edit modes.17Enter and edit the same data on multiple worksheets .18Enter the same data into several cells at once .18Automatically fill in data based on adjacent cells .18Copyright 2009 Massachusetts Institute of Technology All Rights Reserved2 of 41
Microsoft Excel 2007 Basics for WindowsCreate a Custom List .19Import an existing Custom List .19Editing Worksheet Data .19Edit cell contents .19Cancel , undo or redo an entry .20Clear contents, formats, or comments from cells .20Exercise - Module 3 .21Module 4 – Selecting and Navigating.22Selecting.22Grouping Worksheets .22Navigating .23Using the scroll bars [see page 9, Figure 7] .23Using the keyboard .23Exercise - Module 4 .24Module 5 - Creating Formulas .25How formulas work.25How operators work .25Creating a simple formula .25Using functions .26Automatically sum a range of cells.27Sum multiple rows and columns .27Naming a cell or a range, or cells .27To name a cell or a range: .27Absolute versus relative values .28Exercise - Module 5 .30Module 6 – Formatting .31Basic Worksheet Formatting .31Applying Borders and Shading .31Number Formatting .31AutoFormat .31Using Styles .32Format Painter .32To apply the formatting to adjacent cells: .32Copyright 2009 Massachusetts Institute of Technology All Rights Reserved3 of 41
Microsoft Excel 2007 Basics for WindowsTo apply the formatting to non-adjacent cells: .32Exercise – Module 6.33Module 7 - Printing .34Before you print .34Modify the layout of the printed worksheet.34Change the worksheet area that appears on a printed page .34Print Preview .34Print the active sheets, a selected range, or an entire workbook .34Create custom headers and footers .35Change the font in header and footer text .36Print titles .36Rows to Repeat at Top of Each Worksheet Page .36Columns to Repeat at Left of Each Worksheet Page .36Page Break .36Exercise – Module 7.38Module 8 – Getting Help .39When you have a question .39Office Assistant tool .39Dialog Box Screen Tips .39Toolbar Screen Tips .39MIT Computing Help Desk .39MIT Excel User Group .40Exercise - Module 8 .40Copyright 2009 Massachusetts Institute of Technology All Rights Reserved4 of 41
Microsoft Excel 2007 Basics for WindowsModule 1 – Getting StartedStarting Microsoft ExcelThere are several ways to start Excel. Here are a few. Double-click the Excel program icon or an existing Excel worksheet. Excel 2007 program iconChoose Microsoft Excel from the Microsoft Office Manager. Click on the Windows XP Start button, choose Programs, MS Office, MS ExcelStart buttonCreating and Opening WorkbooksCreating a new workbook1) On the Office button menu, click New.2) In the New Workbook dialog box, click Create.3) It’s much faster to click on the New Workbook tool on the Quick Access toolbar.Copyright 2009 Massachusetts Institute of Technology All Rights Reserved5 of 41
Microsoft Excel 2007 Basics for WindowsOpening an existing workbook1) Click the Open tool on the Quick Access toolbar.The Open dialog box appears.2) In the “Open” dialog box, you can navigate through the drop-down list in the Lookin section, or use the icons in the sidebar to get to a location.3) Once you find the workbook, double-click on it to open.Tip:To open a workbook you've used recently, click its name inthe Recent Documents list of the Office button.Workbooks and WorksheetsIn Microsoft Excel, a workbook is the file where you work and store your data. Becauseeach workbook can contain many sheets, you can organize various kinds of relatedinformation in a single file. In other words, a workbook is a collection of worksheets.Worksheets are for listing and analyzing data. You can enter and edit data oneworksheet or on several worksheets simultaneously and perform calculations based ondata from multiple worksheets. You can add chart sheets to chart your worksheet data,and modules to create and store macros for special tasks you want to perform in theworkbook.The names of the sheets appear on tabs at the bottom of the workbook window. Tomove from sheet to sheet, you click the sheet tabs (e.g., Sheet1, Sheet2 ) and thename of the active sheet is always highlighted and bold. You can rename the sheets,add and delete sheets, and move, copy or link sheets within a workbook or to anotherworkbook. You can also group worksheets.Each Excel workbook by default has 3 worksheets, but you can change the default to ahigher or lower number. Each sheet contains 1,048,576 rows and 16,384 columns –that’s over 17 billion cells! Later on you will learn how to navigate in a very largeworksheet.To understand the distinction between a workbook and worksheet, think of an Excelworkbook as a book containing many chapters. Although the worksheets in a workbooklook like only one page, they can contain many pages of data. Therefore, the sheets aremore like individual chapters in a book.Copyright 2009 Massachusetts Institute of Technology All Rights Reserved6 of 41
Microsoft Excel 2007 Basics for WindowsElements of a WindowTitle Bar and RibbonThe Title Bar is at the top of every window in Windows (Workbook and Application canreside on one title bar) and identifies the name of the program (in this case, MicrosoftExcel) and/or workbook (e.g., Book1). If your window is not maximized, you click andhold the left mouse button while dragging the title bar to move the window to a differentlocation.The Office Button is located on the top left section of the Title Bar and Ribbon. From theOffice Button you can select the appropriate command, such as choosing New, Open,Save, Print, and Close.Note: that each menu item has one character underlined. This is so that you can use thekeyboard rather than the mouse to access menus and menu commands. By pressing the [Alt]key, you place Excel in a keyboard mode, and you would be able to type the specified letter of amenu and menu command. For example, if you wanted to choose Open from the File menuwithout using the mouse, you would press the [Alt] key, followed by F and O. The Open dialogCopyright 2009 Massachusetts Institute of Technology All Rights Reserved7 of 41
Microsoft Excel 2007 Basics for Windowsbox would appear so that you could navigate to the workbook you’d like to open.Copyright 2009 Massachusetts Institute of Technology All Rights Reserved8 of 41
Microsoft Excel 2007 Basics for WindowsHome TabThe Standard and Formatting toolbars from older version of Excel are now included onthe Home Tab in the Ribbon.Quick Access ToolbarThere is only one toolbar in Excel 2007, and by default, it appears above the ribbon onthe Title bar. This is the only customizable toolbar in 2007. You can place it below theribbon, add tools to it from a drop down list on the right end of the toolbar, right-clickingon any tool on the ribbon, and by going into the Customize section in Excel Options.Zoom ControlZoom Control is a tool on the Status bar at the bottom right side of the applicationwindow. This feature enables you to see your worksheet in larger or smaller viewswithout having to change the font size. It is also where the page view buttons are.Active CellWhen you start entering data, it will go into the active cell. When you are not enteringdata, the active cell will have a bold outline around it. When you have selected a rangeof cells, the active cell will be white, whereas the other cells in the selected range will behighlighted. Only one cell can be active at a time.Cell AddressEach cell is referenced by a column letter and row number (e.g., B3). The active celladdress is referenced in the Name Box (described below).Cell AddressActive CellCopyright 2009 Massachusetts Institute of Technology All Rights Reserved9 of 41
Microsoft Excel 2007 Basics for WindowsHorizontal and Vertical Scroll BarsExcel has two scroll bars which enable you to navigate around your worksheet. Thevertical scroll bar on the right side of the worksheet moves up or down the worksheet.The horizontal scroll bar is located on the bottom of the worksheet and allows you tonavigate to the left or right. The size of the scroll button is an indicator of how close; orfar, in proximity to what you are currently viewing that you will have to scroll to see morecells with data in them.Verticalscroll barHorizontalscroll barName Box and Formula BarThe Name Box (see Figure 8 below) is part of the Formula Bar; it has several functions: to reference the active cell (e.g., A1)to go to a specific cell addressto select a range of cellsto name a cell or range of cellsName BoxFormula BarCopyright 2009 Massachusetts Institute of Technology All Rights Reserved10 of 41
Microsoft Excel 2007 Basics for WindowsThe Formula Bar shows the data you have typed into the active cell, and is used to editthe data you enter. Later on you will learn how to edit data in the formula bar, as well asfrom within a cell.Row and Column HeadingsColumns are identified by letters (A, B, C) and rows by numbers (1, 2, 3). When youclick on the column or row heading, you are selecting the entire column or row.Sheet Tabs, Tab Scrolling Buttons, Insert Worksheet buttonNear the bottom of the worksheet are Sheet Tabs (e.g., Sheet1, Sheet2) which enableyou to access a worksheet by clicking on it. Each worksheet by default has 3 sheettabs. Should you have more than 3 sheets and the one you’d like to select is not visible,you must use the tab scrolling buttons to the left of the sheet tabs to navigate. There isalso a button to the far right end of your tabs to insert a worksheet when needed.There are four tab scrolling buttons. The first two tab scrolling buttons have blacktriangles pointing to the left; the second two, black triangles pointing to the right.1 2 3 451) Click on the first button to view (not select) the first sheet.2) Click on the second button to view the previous sheet.3) Click on the third button to view the next sheet.4) Click on the fourth button to view the last sheet.5) Click on the Insert Worksheet button to add a new worksheetCopyright 2009 Massachusetts Institute of Technology All Rights Reserved11 of 41
Microsoft Excel 2007 Basics for WindowsTip:If your workbook contains many sheets and you cannot see the sheetyou wish to select, you can use the following shortcut.Right click on tab scrolling button; then left click to select.This shortcut does not accomplish the same results as simply clickingon the tab scrolling buttons. They actually select the worksheet, whereasclicking on the tab scrolling buttons enables you only to view sheet tabs.Status BarThe Status Bar is at the very bottom of your application window. It is a very useful toolwhich is often overlooked. Below is an example of the status bar indicating that it isready to have you enter data or some other action.If you had entered data in a cell and had not executed it by pressing Enter, the statusbar would show “Edit” instead of “Ready.” When Excel is in the Edit mode, many of thecommands are not available in the menu. This will be explained in more detail later.There are status indicators that need to be turned on to see things like Caps Lock, NumLock, Average, Count, Sum, etc The right side of the status bar is where you will find the view and zoom control toolsCopyright 2009 Massachusetts Institute of Technology All Rights Reserved12 of 41
Microsoft Excel 2007 Basics for WindowsExercise - Module 1Before you begin the exercise below, please review these key terms and make sure youunderstand them before beginning the next topic.Key Terms:workbooksheettitle barname boxactive workbooktab scrolling buttonsmenu baropen and closeactive cellactive sheetstatus barzoom controlcell addresssheet tabsformula barrecent files list1) Create a new (blank) Excel workbook.2) What is the Title Bar’s name?3) What is the cell address of the active cell?4) Open the “Ex1” workbook in the Class folder on the hard disk.5) Use the tab scrolling button to view the last worksheet.6) How many sheets are in this workbook?7) Click on the Window menu. How many workbooks are open?8) How do you switch between open workbooks?9) In Ex1, type your name in cell A15 on Sheet1; press Enter.10) Click on cell A15, then click in the formula bar and add your last name.11) Use the Name Box to select cells A1 through E13. [Hint: click in the Name Box,type “A1:E13” and press Enter.]12) What is the active cell in this range?13) Zoom this selected text (A1 through E13) to “Selection” view.14) Has your font size changed?15) Close all open workbooks. Do not save changes.Copyright 2009 Massachusetts Institute of Technology All Rights Reserved13 of 41
Microsoft Excel 2007 Basics for WindowsModule 2 – SavingNaming workbooksTo make it easier to find your workbooks, you can give them long, descriptive filenames.The complete path to the file, including drive letter, server name, folder path, andfilename, can contain up to 218 characters. Filenames cannot include any of thefollowing characters: forward slash (/), backslash (\), greater-than sign ( ), less-thansign ( ), asterisk (*), question mark (?), quotation mark ("), pipe symbol ( ), colon (:), orsemicolon (;).Saving an unnamed workbook1) Start a new workbook; from the Office button, click Save or Save As.2) In the File Name box, type a name for the workbook (e.g., 1998 Budget).3) Click the Save button.Saving an existing workbook On the Office button, click Save, orClick the Save tool on the Quick Access toolbar.Saving a copy of a workbook1)2)3)4)On the Office Button, click Save As.In the sub menu to the right, choose Excel WorkbookIn the File Name box, type a new name for the workbook.Click the Save button.Note that the other workbookis automatically closed.File name boxCopyright 2009 Massachusetts Institute of Technology All Rights Reserved14 of 41
Microsoft Excel 2007 Basics for WindowsSaving a workbook to a new location1)2)3)4)On the Office button, click Save As.In the sub menu to the right, choose Excel WorkbookNavigate to the folder in which you wish to save a copy (e.g., Class folder).Double-click on the folder, then click the Save button.Saving a workbook with a new name and to a new location1)2)3)4)5)On the Office button, click Save As.In the sub menu to the right, choose Excel WorkbookIn the File Name box, type a new name for the workbook.Navigate to the folder in which you wish to save a copy.Double-click on the folder, then click the Save button.Note: When saving, ALL sheets in a workbook are saved.Copyright 2009 Massachusetts Institute of Technology All Rights Reserved15 of 41
Microsoft Excel 2007 Basics for WindowsExercise - Module 2Before you begin the exercise below, please review these key terms and make sure youunderstand them before beginning the next topic.Key Terms:savesave asnew namenew location1) Open the ”EX2” workbook.2) Save the workbook as “New Name.”3) Is “EX2” still open?4) Where is it?5) Save the New Name workbook in the “Practice” folder.6) Re-open the “EX2” workbook from the Recent Files List.7) Close all windows.8) Is Excel still running?9) How many workbooks are listed in the Recent Files List?10) Re-open the “EX2” workbook, type your full name in B2, and save theworkbook again either by using the toolbar or a keyboard shortcut.Copyright 2009 Massachusetts Institute of Technology All Rights Reserved16 of 41
Microsoft Excel 2007 Basics for WindowsModule 3 – Entering and EditingEntering Worksheet DataEnter numbers, text, a date, or a timeThe standard number and text settings for Excel are for entered text to be left-alignedand numbers right-aligned. Dates and times must be given specific formatting;otherwise you may get unexpected results. Time settings are right-aligned. Standarddate settings are more varied. For example, if you type “1/2/97” in a cell, you will see itas “1/2/1997”. However, if you typed “January 2, 1997” in a cell, you would see “2-Jan97” instead. If you typed “97/1/2” for the date, it would be left-aligned and you’d seewhat you had typed. Again, these changes are due to Excel’s standard settings. Thesesettings can be modified, and different formatting can be applied.Ready and Edit modesA common occurrence for new Excel users is to forget to press Enter after they enterdata or make changes to the contents of a cell. When data or changes have not beenentered, you cannot access some commands from the menus. When in the editingmode, the Status Bar will read “Edit.” After you press Enter, the Status Bar will read“Ready.”When in the Edit mode, your formula bar will show a black X that turns red as you hoverover it, a black check markthat turns blue as you hover over it, and an fx symbol.Clicking on the X will cancel your edit(“Esc” key works too)--]\-, clicking on the checkmark will enter your edit, and clicking on the fx give you the Function Wizard dialog boxwhich will assist you with creating a formula. The figure above shows how the formulabar looks when data has been placed in the cell but has not been entered. Figures onpage 9 and 10 shows the formula bar when Excel is in the Ready mode.Copyright 2009 Massachusetts Institute of Technology All Rights Reserved17 of 41
Microsoft Excel 2007 Basics for WindowsEnter and edit the same data on multiple worksheetsWhenever you have d
Microsoft Excel 2007 Basics for Windows . Opening an existing workbook . 1) Click the Open tool on the Quick Access toolbar. The Open dialog box appears. 2) In the “Open” dialog box, you can navigat
