Chapter 5

1. Chapter 5 Consolidating Data

2. Agenda • Three dimensional workbook • Worksheet reference • Grouping multiple worksheets for formula and formatting • Use the AutoFormat command • Documentation worksheet • Linking workbook

3. Three-Dimensional Workbook • Tabs at bottom display worksheet names • Active worksheet is highlighted • Click a different tab to make that worksheet active • Displaying multiple windows • Window menu, new window • Window menu, arrange • Tiled: each window occupying part of the screen • Cascade: windows overlaying each other with the title bar of each window visible • Clicking a window to make it active • Only one active window • Commands only applying to the active window

4. Copying Worksheets • Copying cells from one workbook and pasting in another • Copying worksheet from one workbook to another • Clicking the worksheet tab for copying • Pressing and holding the Ctrl key and dragging the worksheet to the new workbook

5. Worksheet References • Referencing cells in other worksheets of the same workbook • Requiring the name of the worksheet before the cell range • Exclamation mark separating worksheet and cell reference • =Atlanta!B3 +Boston!B3+Chicago!B3 • Worksheet reference is absolute • Cell reference canbeabsolute, relative, or mixed

6. 3-D References • Spanning two or more worksheets in a workbook • Worksheet names separated by colon • Exclamation mark separating worksheet reference from cell reference • =SUM(Atlanta:Chicago!B3) • Includes every worksheet between Atlanta and Chicago • The cell range must be identical in every worksheet

7. Grouping Worksheets • Grouping worksheets for enter or formatting data in multiple worksheets in a workbook • Clicking the first worksheet tab for grouping • Pressing and holding the Shift key and clicking the last worksheet for adjacent grouping • Pressing and holding the Ctrl key and clicking the other worksheet for nonadjacent grouping • Ungrouping the sheets before doing something in one sheet only

8. AutoFormat Command • Applying a predefined format to selected cells • Selecting cells, Format menu, AutoFormat, selecting a specific format

9. The Documentation Worksheet • Containing vital descriptive information • Making the workbook easier to use for the future • Cell formulas for more documentation • Naming a view (value or formula): View menu, custom view, add

10. Linking Workbooks • Including external reference, worksheet reference, and cell reference • External reference: reference to another workbook • Surrounded by brackets: [SF]sheet1!$B$4 • Absolute for workbook reference and worksheet reference • Absolute, relative, or mixed for cell reference

11. Dependent and Source Workbooks • Source workbooks containing referenced data used by dependent workbooks • Dependent workbooks containing external references to those source workbooks • A broken link: moving or deleting a source workbook • Changing link: Edit menu, links

12. Workspace • Enabling to open multiple workbooks in one step • Do not contain the workbooks • File menu, save workspace

13. MIS • Properties of the workbook: File menu, properties • Edit within a cell: Tools menu, options, edit, edit directly in a cell • Spelling check: spelling button • Creating a custom series: Tools menu, options, custom list, new list, enter items

14. Points to Remember • Three dimensional workbook • Worksheet reference • Grouping multiple worksheets for formula and formatting • Use the AutoFormat command • Documentation worksheet • Linking workbook

15. Assignments • Exercises: 4 & 7 • Due date: