Chapter 2 : PowerPoint Presentation

1. Chapter 2: PowerPoint Presentation GO! with Microsoft Excel® 2007 Comprehensive 1e - Original Chapter 2 Chapter 2 Using Multiple-Sheet Workbooks

2. Objectives • Work with a Multiple-Sheet Workbook • Enter a Series • Copy and Paste Cell Contents • Copy and Paste with the Office Clipboard • Total the Worksheet Range and Enter a Grand Total

3. Objectives • Format a Multiple-Sheet Workbook Group • Insert Columns and Rows in Multiple Worksheets • Copy a Worksheet • Create Formulas with Absolute Cell References and Copy Formats

4. Objectives • Find and Replace Text and Hide and Unhide Columns • Conduct a What-If Analysis and Use Statistical Functions • Create Accurate Worksheets with Accuracy Tools

5. Work with a Multiple-Sheet Workbook • By default, a workbook contains three worksheets. Newly inserted worksheet (active) Insert Worksheet button Worksheet tabs

6. Work with a Multiple-Sheet Workbook • By right clicking you can: • Rename worksheets • Change the color of the worksheet tabs • You can use the tab scrolling buttons to navigate among worksheets.

7. Enter a Series • A series is a group of related items. • Example: Jan, Feb, March or 5, 10, 15 • Auto Fill: refers to a feature that completes a series. • AutoFill enables you to extend a series of values into adjacent cells.

8. Enter a Series

9. Copy and Paste Cell Contents • Data from cells and groups of cells can be copied to: • Other cells in the same worksheet • Other sheets in the same workbook • Sheets in another workbook

10. Copy and Paste Cell Contents • Copy and paste duplicates the cell contents. • If you prefer to move the cell contents, use the Cut button instead of the Copy button. • The Cut command removes the contents from the source.

11. Copy and Paste with the Office Clipboard • The Office Clipboard: • Is temporary storage • Holds up to 24 items • Holds multiple text and graphical items from different Office applications • Clipboard contents can be placed into other Office documents.

12. Copy and Paste with the Office Clipboard • Clipboard task pane: an area at the left of your screen used to collect copied data. Clipboard Dialog Box Launcher Clipboard group on Ribbon Clipboard task pane

13. Total the Worksheet Range and Enter a Grand Total • Excel is designed for numeric data and to provide totals of that data. • Select the range of data and use the SUM button. • Excel reviews formulas. • If an error is suspected, a green triangle— an error indicator—is placed in the upper-left corner of the cell to indicate a potential error.

14. Format a Multiple-Sheet Workbook Group • When worksheets are grouped, formats applied in the active worksheet are also applied to all the worksheets that are grouped. • Grouping Specific worksheets: • To select adjacent worksheets, click one sheet tab and press Shift while you click the last sheet tab you want to group.

15. Insert Columns and Rows in Multiple Worksheets • Columns and rows can be inserted into a worksheet or group of worksheets. • Relative cell references in formulas adjust to reflect the new location. • If Excel detects a possible error, an error indicator displays in the cell.

16. Insert Columns and Rows in Multiple Worksheets • When a column is inserted, the existing columns move one column to the right. Insert Options button Inserted column

17. Copy a Worksheet • Excel allows you to copy the contents of one worksheet to another worksheet. • You can specify which workbook the worksheet will be placed into and the location within the workbook.

18. Copy a Worksheet • Right click on the tab of the sheet you wish to copy. Name of worksheet that new worksheet will be placed before • On the shortcut menu click Move or Copy. Active worksheet Click to create a copy of the current worksheet.

19. Copy a Worksheet • If you do not click Create a copy, the worksheet will be moved to a new location. • It will be deleted from the source location. • If you want the worksheet in two locations, be sure to create a copy of it.

20. Create Formulas with Absolute Cell References and Copy Formats • Relative cell reference • If the position of the cell that contains the formula changes, the reference is changed. • If you copy the formula across rows or down columns, the reference automatically adjusts.

21. Create Formulas with Absolute Cell References and Copy Formats • Absolute cell reference • Formula always remains the same. • To create, type a dollar sign ($) to the left of the column letter and row number of the cell reference—$A$1. • The shortcut to change a cell reference to an absolute is the function key F4.

22. Find and Replace Text and Hide and Unhide Columns Find & Select button Find & Replace dialog box Excel searches for this text. Excel replaces with this text.

23. Find and Replace Text and Hide and Unhide Columns • To Undo an action: • Click Undo on the Quick Access toolbar.  • Alternatively, press Ctrl + Z.

24. Find and Replace Text and Hide and Unhide Columns • You can hide rows or columns in Excel. • Hidden rows and columns are not deleted. • Hiding allows you to print a worksheet without showing unnecessary or confidential data.

25. Find and Replace Text and Hide and Unhide Columns Column “C” hidden Solid line indicates hidden column.

26. Conduct a What-If Analysis and Use Statistical Functions • What-if scenarios can help you answer questions. • What-if also can project future values. • You can change cell values and Excel recalculates the results. • Thus, you see what would happen if you tried different values.

27. Conduct a What-If Analysis and Use Statistical Functions • Statistical functions are a group of functions that calculate values. • Statistical functions are used to calculate various statistics about a group of numbers. • Examples: MIN, MAX, AVG

28. Conduct a What-If Analysis and Use Statistical Functions Formulas tab Insert Function button Insert Function dialog box List of functions

29. Create Accurate Worksheets with Accuracy Tools • Data and formulas in a workbook must be 100 percent correct. • Excel has tools to help identify potential errors. • An Alert dialog box displays messages to warn of errors.

30. Create Accurate Worksheets with Accuracy Tools Alert dialog box Information about error

31. Create Accurate Worksheets with Accuracy Tools • Excel helps identify potential formula errors. • Data entry errors cannot be detected. • Excel alerts you to potential errors by displaying an error value. • Error values begin with a number sign (#) followed by the error name.

32. Create Accurate Worksheets with Accuracy Tools

33. Create Accurate Worksheets with Accuracy Tools • Excel also uses error indicators to indicate a potential error in a formula. • Error indicators are green triangles placed in the upper left corner of the cell.

34. Create Accurate Worksheets with Accuracy Tools Error indicators—green triangles in cells

35. Create Accurate Worksheets with Accuracy Tools • Another accuracy tool is the Error Checking button. • The Error Checking button is context-sensitive and displays different correction options. • To review a ScreenTip, point to the Error Checking button.

36. Create Accurate Worksheets with Accuracy Tools • You can correct or ignore any error. • After you review an error and indicate the formula is correct, the error indicator is removed from the cell. • You can remove multiple error indicators from selected adjacent cells at the same time.

37. Covered Objectives • Work with a Multiple-Sheet Workbook • Enter a Series • Copy and Paste Cell Contents • Copy and Paste with the Office Clipboard • Total the Worksheet Range and Enter a Grand Total

38. Covered Objectives • Format a Multiple-Sheet Workbook Group • Insert Columns and Rows in Multiple Worksheets • Copy a Worksheet • Create Formulas with Absolute Cell References and Copy Formats

39. Covered Objectives • Find and Replace Text and Hide and Unhide Columns • Conduct a What-If Analysis and Use Statistical Functions • Create Accurate Worksheets with Accuracy Tools