Power Point Presentation to Accompany GO with Microsoft

Objectives • Nest One Function within Another • Use 3 -D References and Nested

Nest One Function within Another • A function placed within another function to create

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

Use 3 -D References and Nested Lookups • When using nested functions, you can

Use 3 -D References and Nested Lookups Logical test refers to a cell in

Check Accuracy with Excel’s Auditing Tools • Excel’s auditing tools provide assistance to help

Check Accuracy with Excel’s Auditing Tools • Precedent cells are referred to in a

Check Accuracy with Excel’s Auditing Tools • Excel uses certain rules, called error checking,

Check Accuracy with Excel’s Auditing Tools Error Checking button with Microsoft Excel 2007 Comprehensive

Check Accuracy with Excel’s Auditing Tools • The Watch Window is a small window

Consolidate Workbooks • To summarize and report results from separate worksheets, you can consolidate

Consolidate Workbooks • Worksheet structure refers to the data range in all worksheets, which

Share and Merge Workbooks • A shared workbook allows more than one user to

Share and Merge Workbooks Track Changes button with Microsoft Excel 2007 Comprehensive 1 e

Share and Merge Workbooks • When you use Open as Copy, a duplicate file

Covered Objectives • Nest One Function within Another • Use 3 -D References and

Slides: 17

Download presentation

Power. Point Presentation to Accompany GO! with Microsoft® Excel 2007 Comprehensive 1 e Chapter 11 Nesting Functions and Consolidating Worksheets with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 1

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 with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 2

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 3

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 5

Use 3 -D References and Nested Lookups Logical test refers to a cell in the Busch Landing Worksheet Value if true Value if false with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 6

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 7

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 8

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 9

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 11

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 12

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 13

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 14

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. with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 16

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 with Microsoft Excel 2007 Comprehensive 1 e © 2008 Pearson Prentice Hall 17