Excel 2002 Lab 8

1. Excel 2002Lab 8 Auditing Formulas, Validating Data, and Using PivotTables and PivotCharts

2. Objectives • Audit formulas. • Trace precedents, dependents, and formula errors. • Add comments to a cell. • Set up data validation rules.

3. Objectives • Create a message box prompt. • Outline and group data. • Consolidate data. • Create automatic subtotals. • Create PivotTables and PivotCharts.

4. Concepts Overview • Formula Audit A formula audit is used to detect two types of errors: data entry errors and formula creation errors. • Data Validation Data Validation is used to suggest or limit the range of values that are entered into cells.

5. Concepts Overview • Named Ranges Named Ranges are individual or groups of cells that have been given a logical name.

6. Concepts Overview • Form Controls Form Controls are check boxes, option boxes, drop-down lists, combo boxes, and spinners that can be added to worksheets or worksheet forms to create custom areas for data entry and summarizing data.

7. Concepts Overview • Outlining and Grouping Data Outlining and grouping data organize data in a worksheet by showing and hiding different levels of detail. • Consolidating Data Data consolidation table is a way to manage data that is in a number of sections of one worksheet, in multiple worksheets, or in multiple workbooks.

8. Concepts Overview • Automatic Subtotal Automatic subtotals take data that has been entered into a table or sorted list, organize it into groups, and apply subtotals to those groups.

9. Concepts Overview • PivotTable A PivotTable is a three-dimensional representation of long and complicated data arranged into a specialized summary table. • PivotChart A PivotChart is a visual representation of summarized data from a PivotTable.

10. Outline • Auditing Formulas • Tracing Precedents • Tracing Dependents • Hiding Worksheet Comments • Tracing Errors in Cells • Validating Data • Creating a Data Range Message Box Prompt • Checking for Invalid Data

11. Outline cont. • Using Named Ranges in Formulas • Creating a Named Range • Summarizing Data • Creating a Drop-Down Form Control • Using Linked Cells with Check Boxes • Outlining and Grouping Data

12. Outline cont. • Consolidating Data • Consolidating Data by Position • Consolidating by Linking • Creating Automatic Subtotals

13. Outline cont. • Creating PivotTables • Selecting PivotTable Data • Setting the Layout for a PivotTable • Formatting a PivotTable • Displaying Selected Items • Modifying a PivotTable • Moving Fields in a PivotTable • Creating Custom Calculations in a PivotTable • Creating a PivotTable Printed Report

14. Outline cont. • Creating PivotCharts • Concept Summary • Lab Review • Lab Exercises

15. Auditing Formulas

16. Concept 1Formula Audit • Used to detect two types of errors • Data entry errors • Formula creation errors • May be done while creating or after creating a worksheet

17. Formula Auditing Methods • Error checking • Tracing precedents • Tracing dependents • Tracing errors

18. Tracing Precedents • Precedents - source cells for the formula • Dependents – where values (precedents) are used in other sections of the worksheet • Tracing Precedents • Click to activate • Blue line appears with an arrow pointing to the current cell • Line is called "precedent line"

19. First cell of precedent line 2nd cell of precedent line Cell derived from precedent line Tracing Precedents - Example Formula bar

20. GoTo window showing source worksheet Tracing Precedents to Another Worksheet Double-click arrow

21. Tracing Dependents • Dependents are cells that contain formulas that refer to other cells • By tracing dependents, you can follow the path of data from a particular cell to its next use in formula creation

22. Adding Comments to a Cell • Comments can help to explain a calculation and to provide a reminder • A comment is notated by a small red triangle in the corner of the cell • The name of the person making the comment appears in bold type at the top of the comment box • A cell can have only one comment

23. Comments

24. Error message Green triangle indicator Trace Error icon Tracing Errors in Cells • Smart tag is a label that automatically appears when certain data has been entered in a cell • Trace error icon displays a list of options

25. Trace Error List BoxActions

26. Cursor to formula bar Color-coded cells matching first & second parts of formula Edit in Formula Bar Function

27. Validating Data Validate data to ensure the integrity of the data that is entered so that fewer data entry errors occur.

28. Concept 2Data Validation • Used to suggest or limit the range of values that are entered into cells • Handled in two ways • For data entry prompt suggestions • For restricting data entry

29. Creating a Data Range Message Box Prompt

30. Excel Warning Messages

31. Incorrect data Checking for Invalid Data Range of acceptable data

32. Using Named Ranges in Formulas • Named ranges quickly identify portions of a worksheet • Excel provides naming capability instead of using cell addresses • Named ranges can be used in formulas

33. Concept 3Named Ranges • Single or groups of cells that have been given a logical name • Named ranges can be used in: • Creating formulas • Filtering data • Printing • Navigating for charts

34. Named Ranges • Increases the clarity of a formula • Created and stored in the Name Box on the left of the formula bar =E12/I12 becomes =Half_pound_revenues/Total_revenues

35. Name box Cell reference for Half-Pound Revenues Creating a Named Range

36. Form Controls are: Check boxes Option boxes Drop-down lists Combo boxes Spinners Used to create custom areas for data entry and summarizing data Used with worksheet tables, charts, data lists, and PivotTables Concept 4Form Controls

37. Forms Toolbar

38. Link cell display Revenues for Earl Grey coffee Creating a Drop-Down Form Control Drop-down combo box

39. Check box display - (TRUE when checked) Link cell is cell with check box Using Linked Cells with Check Boxes

40. True when checked Link cell is cell with check box Check Boxes Using the IF Function

41. Concept 5Outlining and Grouping Data • Organize data by showing and hiding different levels of detail • Can provide summaries or headings • Groups – created for any level • Outlines – based on a structured list or table

42. Grouping & Outlining Commands

43. Hierarchy number of group Collapsed Jan, Feb, Mar Grouping Grouping Example

44. Consolidating Data

45. Concept 6Consolidating Data • Data consolidation is a way to manage data in a number of sections of one worksheet, in multiple worksheets, or in multiple workbooks • Select data and create a new worksheet with the same structure and format • Formulas are called 3-D formulas

46. Methods of Consolidating Data • Linking • Consolidated by Position • Consolidated by Category • Consolidated by Function

47. Consolidating Data by Position • Takes data from multiple worksheets and combines it • Positions are same in each worksheet

48. Consolidating Data by Linking • Provides automatic updating of the data that is in the consolidated worksheet • Worksheet is linked for new data

49. Creating Automatic Subtotals

50. Concept 7Automatic Subtotal • Takes data that has been entered into a table or sorted list • Organizes data into groups • Applies subtotals to those groups • Subtotal syntax =SUBTOTAL (function_num, ref1,ref2,…)