1 / 60

Office Excel 2003 Lab 3

Office Excel 2003 Lab 3. Managing and Analyzing a Workbook. Objectives. Correct worksheet errors. Use absolute references. Copy, move, name, and delete sheets. Use AutoFill. Reference multiple sheets. Use Find and Replace. Objectives cont. Zoom the worksheet.

florenceroberts
Download Presentation

Office Excel 2003 Lab 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Office Excel 2003Lab 3 Managing and Analyzing a Workbook

  2. Objectives • Correct worksheet errors. • Use absolute references. • Copy, move, name, and delete sheets. • Use AutoFill. • Reference multiple sheets. • Use Find and Replace.

  3. Objectives cont. • Zoom the worksheet. • Split windows and freeze panes. • Use What-If analysis and Goal Seek. • Control page breaks. • Change page orientation. • Add custom headers and footers. • Print selected sheets and areas.

  4. Absolute Reference Sheet Name AutoFill Sheet and 3-D References Find and Replace Split Windows Freeze Panes What-If Analysis Goal Seek Concept Preview

  5. Outline • Correcting Worksheet Errors • Using the Formula Error Checker • Calculating an Average • Correcting Formula Errors Individually • Using Absolute References

  6. Outline cont. • Working with Sheets • Copying Between Sheets • Renaming Sheets and Coloring Sheet Tabs • Filling a Series • Referencing Multiple Sheets • Deleting and Moving Sheets

  7. Outline cont. • Finding and Replacing Information • Finding Information • Replacing Information • Saving to a New Folder

  8. Outline cont. • Managing Large Worksheets • Zooming the Worksheet • Going to a Specific Cell • Splitting Windows • Freezing Panes • Watching Cells

  9. Outline cont. • Forecasting Values • Performing What-If Analysis Manually • Using Goal Seek • Changing Values Using a Chart • Customizing Print Settings • Controlling Page Breaks • Changing Page Orientation • Displaying Gridlines and Centering on a Page

  10. Outline cont. • Customizing Print Settings cont. • Adding Custom Headers and Footers • Printing Selected Sheets • Printing Selected Areas • Key Terms • FAQs • Discussion Questions

  11. Formula errors identified Correcting Worksheet Errors

  12. Excel Error Values

  13. Correcting Errors • Can correct errors individually • Can use Excel's formula error checker tool • Checks all errors one at a time • Identifies the problem • Suggests corrections

  14. Asterisks will change entry type to text Location and cause of error Error Checking Tool

  15. Identified range Total value is included in range and needs to be removed Calculating an Average

  16. Errors Options button Correcting Formula Errors Individually

  17. Formula references a blank cell Formula was copied and the relative reference was adjustedresulting in an error Using Absolute References

  18. Concept 1 Absolute Reference • An absolute reference is a cell or a range in a formula whose location does not change when the formula is copied • To avoid relative adjustment of cell references, enter a $ (dollar sign) before the column letter and row number • Changes cell reference to absolute • Cell reference does not change when copied

  19. Absolute reference stopped adjustment of the cell reference when the formula was copied Absolute Reference Cells

  20. Working with Sheets

  21. Copying Between Sheets • Select the source to copy • Switch to the new sheet to specify the destination • All worksheet data and formatting are copied • Column width settings are not copied

  22. Sheet 2 active How to Copy Between Sheets • Select range A1 through G23 • Click on Edit/Copy • Click on the Sheet 2 tab • Click Edit/Paste

  23. Concept 2 Sheet Name • Can be up to 31 characters • Can be entered in uppercase, lowercase, or a combination • Can contain any combination of letters, numbers, and spaces • Cannot contain the characters : ? * / \ • Cannot be enclosed in square brackets

  24. Changing Tab Colors

  25. Fill handle Concept 3 AutoFill • Helps easily enter a series of headings • Recognizes trends • Automatically extends data and alphanumeric headings as far as you specify • Dragging the fill handle activates the AutoFill feature

  26. Month headings extended by dragging fill handle Dragging the Fill Handle

  27. + Referencing Multiple Sheets With Excel, you can create a formula that references cells in other worksheets

  28. Concept 4 Sheet and 3-D References • Formulas can reference cells in other sheets of a workbook • Formula contains a sheet reference • Name of the sheet followed by an exclamation point and the cell or range (=Sheet2!B17) • 3-D reference is used for the same cell or range of cells on multiple sheets

  29. 3-D References

  30. 3-D Example 3-D reference includes sheet names and cell references Calculated income for first half Text entry right-aligned

  31. Deleting and Moving Sheets • Choose Edit/Delete to delete a worksheet • Move a sheet to rearrange the worksheet position • Move a sheet by clicking on the sheet tab and dragging it

  32. Concept 5 Find and Replace • Helps you quickly find specific information • Automatically replaces with new information • The Find command locates all occurrences of the text or numbers you specify • The Replace command works with the Find command

  33. Find and Replace Options

  34. Finding & Replacing

  35. Saving to a New Folder

  36. Managing Large Worksheets • Zoom to change how much information is displayed • Locate a specific cell by entering the cell reference in the Name box • Split windows to view different areas of the same sheet at the same time

  37. Makes comparing distant areas of a large worksheet easier Split Window

  38. Concept 6 Split Windows • Allows you to divide a worksheet window into sections • Sections of window are called panes • The worksheet can be divided into … • Two panes (horizontally or vertically) • Four panes • Each pane can be scrolled independently

  39. Concept 7 Freeze Panes • Prevent the data in the pane from scrolling as you move to different areas in a worksheet • Can freeze information in the top and left panes only • Useful when your worksheet has row and column headings

  40. Frozen panes Freeze Panes

  41. Moving border shows selected cells Watch window toolbar Specify cells to watch Watching Cells

  42. Concept 8 What-If Analysis • A technique used to evaluate the effects of changing selected factors • You can substitute different values in cells and quickly see the effects of the changes

  43. Chart shows change in profit margin Performing What-If Analysis Manually • Enter different values • Usually takes several tries to find the appropriate value

  44. Concept 9 Goal Seek • Used to find the value needed in one cell to attain the desired result in another cell • Varies the value in a cell you specify until a formula returns the desired result • The value of only one cell can be changed

  45. Location of cell containingformula to be solved Specify cell that can be adjusted Enter desired result Goal Seek

  46. Changing Values Using a Chart

  47. Customized Print Settings • Control page breaks • Change the page orientation • Portrait • Landscape • Center the worksheet on a page

  48. Customized Print Settings • Hide gridlines • Add custom header and footer • Print Selected Areas

  49. Control Page Breaks

  50. Page Orientation

More Related