1 / 45

CHAPTER 7

CHAPTER 7. Managing Worksheets and Workbooks. 7.1 Viewing and Printing Large Worksheets. Excel provides several solutions that make it easier to use large worksheets Freeze the titles of a worksheet so that certain columns and rows are always visible

savannah-lopez
Download Presentation

CHAPTER 7

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. CHAPTER 7 Managing Worksheets and Workbooks

  2. 7.1 Viewing and Printing Large Worksheets • Excel provides several solutions that make it easier to use large worksheets • Freeze the titles of a worksheet so that certain columns and rows are always visible • Divide a worksheet window into two or four independent panes • Outlining lets you view your data differently by displaying or hiding worksheet details

  3. 7.1.1 Freezing and Unfreezing Panes Figure 7.1 Opening the EX0710 workbook

  4. 7.1.1 Freezing and Unfreezing Panes Figure 7.2 Freezing titles The row numbering skips from row 3 to 48

  5. 7.1.2 Splitting the Worksheet Window Figure 7.3 Splitting a worksheet window into panes Top or upper window pane Vertical scroll bar for the top pane Vertical scroll bar for the bottom pane Bottom or lower window pane

  6. 7.1.2 Splitting the Worksheet Window Figure 7.4 Updating data in a multi-paned worksheet

  7. 7.1.3 Outlining a Worksheet Figure 7.5 Outlining a worksheet Column-Level buttons Column-Level bar Row-Level buttons Row-Level bar Hide Detail button

  8. 7.1.3 Outlining a Worksheet Figure 7.6 Collapsing and expanding an outlined worksheet

  9. 7.1.4 Specifying Print Titles and Options Figure 7.7 Previewing a large worksheet The Status bar reads “Page 1 of 3,” informing you that more pages are available for viewing.

  10. 7.1.4 Specifying Print Titles and Options Figure 7.8 Selecting print titles

  11. 7.1.5 Using the Page Break Preview Mode Figure 7.9 Setting a page break in Page Break Preview Drag the right blue margin line to set the page border. Watermarking for Page 1 Drag the blue page break line to set the page break. Watermarking for Page 2

  12. 7.2 Working with Data in Large Worksheets • Access provides some ways to edit data in larger and more complex the worksheets • The Find command in Excel lets you search an entire workbook for the existence of a few characters, a word, or a phrase • The Replace command in Excel lets you perform a global find and replace operation to update the contents of an entire workbook • The AutoCorrect feature can correct hundreds of common typographical and capitalization errors as you type • The Spelling Checker lets you perform a spelling check on a cell range, a worksheet, or an entire workbook

  13. 7.2.1 Finding Data and Cell Formats Figure 7.10 Opening the EX0720 workbook

  14. 7.2.1 Finding Data and Cell Formats Figure 7.11 Find and Replace dialog box: Find tab Enter the search parameter that you want to find. Click here for additional search options, including finding cell formats.

  15. 7.2.1 Finding Data and Cell Formats Figure 7.12 Displaying search options in the Find and Replace dialog box Allows you to choose cell formatting as a search parameter. The previous search parameter remains in the Find what drop-down list box. Displays a preview of the cell formatting you want to find. Use these options to specify where and how to search.

  16. 7.2.2 Replacing Data and Cell Formats Figure 7.13 Find and Replace dialog box: Replace tab

  17. 7.2.2 Replacing Data and Cell Formats Figure 7.14 Specifying the replacement cell format

  18. 7.2.3 Spell Checking a Worksheet Figure 7.15 AutoCorrect dialog box: AutoCorrect tab Type a word that you frequently misspell. Type the correct spelling of the word. Click the Add command button to add the correction.

  19. You can tell Excel to ignore the word once or for the remainder of the spelling check. Adds a word to the dictionary. Replaces the misspelled word with the selected word. Adds a word to AutoCorrect. Click to specify further spelling options, including the dictionary language and whether to check uppercase entries. 7.2.3 Spell Checking a Worksheet Figure 7.16 Spelling dialog box

  20. You can tell Excel to ignore the word once or for the remainder of the spelling check. Adds a word to the dictionary. Select the correct spelling and then click the Change or Change All command button. Replaces the misspelled word with the selected word. Adds a word to AutoCorrect. Click to specify further spelling options, including the dictionary language and whether to check uppercase entries. 7.2.3 Spell Checking a Worksheet Figure 7.16 Spelling dialog box

  21. 7.3 Working with Multiple-Sheet Workbooks • Multiple-sheet workbooks enable you to separate related information onto different pages in a single disk file. This three-dimensional (3-D) capability enables you to easily manage and consolidate your information.

  22. 7.3.1 Navigating, Renaming, and Formatting Sheet Tabs Figure 7.17 Opening the EX0730 workbook

  23. 7.3.1 Navigating, Renaming, and Formatting Sheet Tabs Figure 7.18 Displaying the tab menu for the Townhouse worksheet

  24. 7.3.2 Inserting, Arranging, and Deleting Worksheets Figure 7.19 Displaying the Move or Copy dialog box for a sheet tab Specify the target location in another workbook. Specify the target location within the current workbook. Change the operation from a Move to a Copy by selecting this check box.

  25. 7.3.2 Inserting, Arranging, and Deleting Worksheets Figure 7.20 Insert dialog box: General tab

  26. 7.3.3 Creating Hyperlinks in a Worksheet Figure 7.21 Creating a table of contents The Summary sheet tab will provide a table of hyperlinks to the other sheets in the workbook.

  27. 7.3.3 Creating Hyperlinks in a Worksheet Figure 7.22 Insert Hyperlink dialog box: Existing File or Web Page option Potential files that you may want to link to. Select the type of link that you want to create using the Link to bar.

  28. 7.3.3 Creating Hyperlinks in a Worksheet Figure 7.23 Insert Hyperlink dialog box: Place in This Document option Worksheets and range names that are available targets for linking.

  29. 7.3.4 Grouping Worksheets for Formatting and Printing Figure 7.24 Formatting worksheets as a group

  30. 7.4 Consolidating Your Data • Excel enables you to consolidate or merge data stored in different worksheets and workbooks in order to better manage, organize, and present your information. . There are two methods for consolidating worksheet data: • Enter formulas and functions that reference cells from other worksheets. • Excel’s Data, Consolidate command

  31. 7.4.1 Consolidating a Multiple-Sheet Workbook Figure 7.25 Opening the EX0741 workbook

  32. 7.4.1 Consolidating a Multiple-Sheet Workbook Figure 7.26 Consolidate dialog box

  33. 7.4.1 Consolidating a Multiple-Sheet Workbook Figure 7.27 Selecting a cell range to consolidate

  34. 7.4.1 Consolidating a Multiple-Sheet Workbook Figure 7.28 Consolidating data from multiple worksheets You must select this check box to enable automatic updating to occur in the workbook.

  35. 7.4.1 Consolidating a Multiple-Sheet Workbook Figure 7.29 Displaying details for the Summary tab Disregard the errant formatting adopted from row 3. The category rows contain sub-total calculations. These rows represent values pulled from the other worksheets.

  36. 7.4.2 Consolidating Multiple Workbooks Figure 7.30 Arrange Windows dialog box Select your preference for viewing multiple workbook windows at the same time. Each workbook is represented by a button on the task bar.

  37. 7.4.2 Consolidating Multiple Workbooks Figure 7.31 Arranging workbook windows The active workbook window appears with the same Title bar coloring as the Excel application window. The inactive workbook windows appear with a dimmed or gray Title bar.

  38. 7.4.2 Consolidating Multiple Workbooks Figure 7.32 Consolidating data from multiple workbooks

  39. 7.4.2 Consolidating Multiple Workbooks Figure 7.33 Displaying details for the Groups workbook

  40. 7.5 Documenting Your Work • Documenting a workbook ensures that you remember the underlying logic and reasoning behind the formulas and functions entered into a worksheet • Makes it easier to decipher entries made months earlier • Document work using cell comments, or cell notes -- special text boxes that you attach to a worksheet cell

  41. 7.5.1 Inserting and Deleting Comments Figure 7.34 Opening the EX0750 workbook

  42. 7.5.1 Inserting and Deleting Comments Figure 7.35 Inserting a new comment

  43. 7.5.1 Inserting and Deleting Comments Figure 7.36 Viewing comments in a worksheet Reviewing toolbar

  44. 7.5.2 Formatting and Printing Comments Figure 7.37 Sizing a comment text box

  45. 7.5.2 Formatting and Printing Comments Figure 7.38 Previewing comments as they will look when printed

More Related