PowerPoint Presentation to Accompany GO! with Microsoft ® Excel 2007 Comprehensive 1e Chapter 11

1. PowerPoint Presentation to Accompany GO! with Microsoft® Excel 2007 Comprehensive 1e Chapter 11 Nesting Functions and Consolidating Worksheets

2. Objectives • Nest One Function within Another • Use 3-D References and Nested Lookups • Check Accuracy with Excel’s Auditing Tools • Consolidate Workbooks • Share and Merge Workbooks

3. Nest One Function within Another • A function placed within another function to create an even more complex formula is called a nested function. • You can nest any of the functions by using the Function Arguments dialog box. • A lookup table can be nested within a function.

4. Nest One Function within Another IF Function Arguments dialog box Insert Function dialog box

5. Use 3-D References and Nested Lookups • When using nested functions, you can use 3-D references within a function. • A 3-D reference is a reference to the same cell or range of cells on multiple worksheets. • A mixed reference is a cell reference in which the column or row reference is absolute and the other reference remains relative.

6. Use 3-D References and Nested Lookups Logical test refers to a cell in the Busch Landing Worksheet Value if true Value if false

7. Check Accuracy with Excel’s Auditing Tools • Excel’s auditing tools provide assistance to help locate possible errors. • Use the Evaluate Formula dialog box to see the different parts of a nested formula.

8. Check Accuracy with Excel’s Auditing Tools • Precedent cells are referred to in a formula. • Dependent cells are referred to by a formula in another cell.

9. Check Accuracy with Excel’s Auditing Tools • Excel uses certain rules, called error checking, to check for errors in formulas: • Does not guarantee that the worksheet is error-free • Helps in finding common mistakes • The Evaluate Formula dialog box is used to review the parts of a complex formula.

10. Check Accuracy with Excel’s Auditing Tools Error Checking button

11. Check Accuracy with Excel’s Auditing Tools • The Watch Window is a small window used to inspect, audit, or confirm formula calculations and results in large worksheets. • The Add Watch button allows you to insert specific information about formulas to watch.

12. Consolidate Workbooks • To summarize and report results from separate worksheets, you can consolidate data. • Combine data from separate worksheets. • Consolidate by position arranges the data in all worksheets in an identical order and location.

13. Consolidate Workbooks • Worksheet structure refers to the data range in all worksheets, which must be identical. • When you consolidate by category, the same column and row titles must be used. • The master worksheet can match the data. • You can enter the defined name of the reference area.

14. Share and Merge Workbooks • A shared workbook allows more than one user to enter data into a workbook. • Use Track Changes to: • Determine who used the workbook • Determine when the workbook was used • Determine what changes were made • Use Compare and Merge workbooks to combine individual workbooks containing similar data into one workbook.

15. Share and Merge Workbooks Track Changes button

16. Share and Merge Workbooks • When you use Open as Copy, a duplicate file is opened and given a new name. • The Compare and Merge Workbooks command is used when you compare similar workbooks and merge the data into one workbook.

17. Covered Objectives • Nest One Function within Another • Use 3-D References and Nested Lookups • Check Accuracy with Excel’s Auditing Tools • Consolidate Workbooks • Share and Merge Workbooks