SECTION 4 SKILLS Working with Multiple Worksheets, Tables, and Other File Formats

2. SECTION 4 SKILLSWorking with Multiple Worksheets, Tables, and Other File Formats 4.1 Insert, Delete, and RenameWorksheets 4.1 Format Sheet Tabs 4.2 Move and Copy Worksheets 4.2 Group and Ungroup Worksheets 4.3 Use 3-D References in Formulas 4.4 Link Cells Between Worksheets 4.4 Print Multiple Worksheets CHECKPOINT 1 4.5 Use Page Break Preview 4.6 Format Data as a Table 4.6 Apply Table Design Options 4.7 Sort a Table 4.8 Filter a Table CHECKPOINT 2 4.9 Use Comments 4.10 Create a Workbook from aTemplate 4.11 Open a Different File Format 4.11 Save in a Different File Format 4.12 Create a PDF/XPS Copy CHECKPOINT 3

3. Insert, Delete, and Rename Worksheets To insert a worksheet: • Click the Insert Worksheet button located at the end of the sheet tabs. Insert Worksheet button

4. Insert, Delete, and Rename Worksheets…continued To insert a worksheet using the shortcut menu: • Right-click a sheet tab. • Click Insert in the worksheet shortcut menu. • Click OK. worksheet shortcut menu

5. Insert, Delete, and Rename Worksheets…continued To rename a worksheet: • Right-click the sheet tab. • Click Rename in the worksheet shortcut menu. • Type the new sheet tab name. • Press Enter. renamed sheet tab

6. Insert, Delete, and Rename Worksheets…continued To delete a worksheet: • Right-click the sheet tab. • Click Delete in the worksheet shortcut menu. • Click Delete if a confirmation message box appears. worksheet shortcut menu confirmation message box

7. Insert, Delete, and Rename Worksheets…continued tab scrolling buttons tab split box

8. Format Sheet Tabs To change the sheet tab color: • Right-click the sheet tab. • Point to Tab Color in the worksheet shortcut menu. • Click the desired color in the Tab Color palette. Tab Color palette colored sheet tabs

9. Move and Copy Worksheets To move a worksheet: • Drag the sheet tab to the desired location. To copy a worksheet: • Hold down the Ctrl key and drag the sheet tab to the desired location. move icon copy icon

10. Move and Copy Worksheets…continued To use the Move or Copy dialog box: • Right-click the sheet tab to be moved or copied. • Click Move or Copy at the worksheet shortcut menu. • Click the worksheet in front of where you want to place the moved or copied worksheet in the Before sheet list box. • Click OK to move or click the Create a copy check box, and then click OK to copy. Move or Copy dialog box

11. Group and Ungroup Worksheets To group worksheets: • Click the first sheet tab. • Hold down Shift and click the last sheet tab or hold down Ctrl and click the other sheets. To ungroup worksheets: • Right-click any of the grouped sheet tabs. • Click Ungroup Sheets at the worksheet shortcut menu. grouped sheets worksheet shortcut menu

12. Use 3-D References in Formulas To create a formula with a 3-D reference: • Make the desired cell active. • Type =sum(. • Click the first sheet tab. • Hold down the Shift key and click the last sheet tab. • Click the cell containing the data to be summed in all sheets. • Press Enter. 3-D formula

13. Link Cells Between Worksheets To link a worksheet: • Make the destination cell active. • Type =. • Click the sheet tab for the source cell. • Click the source cell. • Press Enter. link formula

14. Print Multiple Worksheets To print multiple worksheets: • Click the first sheet tab. • Hold down the Shift key and click the last sheet tab. • Click the File tab. • Click the Print tab. • Click the Print button. page navigation

15. CHECKPOINT 1 • A new workbook initially contains how many sheets? • 1 • 2 • 3 • 4 • Use this key to group adjacent sheets. • CTRL • SHIFT • ALT • F1 Answer Answer Next Question Next Question • To copy a worksheet, hold down this key when dragging. • CTRL • SHIFT • ALT • F1 • Do this to the worksheets to print more than one worksheet at once. • Format • Zoom • Ungroup • Group Answer Answer Next Question Next Slide

16. Use Page Break Preview To use Page Break Preview: • Click the View tab. • Click the Page Break Preview button in the Workbook Views group. OR • Click the Page Break Preview button on the Status bar. Page Break Preview button Page Break Preview example

17. Use Page Break Preview…continued To adjust the position of the page break: • Position the mouse pointer on the blue broken line between the columns until the pointer displays as a left- and right-pointing arrow. • Drag the mouse to the right or left of the desired column. • Release the mouse.Notice that the scaling percent automatically adjusts. manual page break scaling percent

18. Use Page Break Preview…continued To insert or remove a page break in Normal view: • Position the active cell in the row below or the column to the right of where you want the page break to occur. • Click the Page Layout tab. • Click the Breaks button in the Page Setup group. • Click Insert Page Break or Remove Page Break at the drop-down list. Breaks button

19. Format Data as a Table To format data as a table: • Select the desired range. • Click the Format as Table button in the Styles group of the Home tab. • Click the desired table style. • Click OK at the Format As Table dialog box. Format as Table button Format As Table dialog box

20. Apply Table Design Options To add a total row: • Click the Table Tools Design tab. • Click the Total Row check box in the Table Style Options group. • If necessary, choose the desired function in the numeric columns. Total Row check box

21. Sort a Table To sort a table by a single column: • Click in any row within the column by which to sort. • Click the Sort & Filter button in the Editing group in the Home tab. • Click the desired option in the drop-down list. Sort & Filter button

22. Sort a Table…continued To sort a table by multiple columns: • Click the Sort & Filter button in the Editing group in the Home tab. • Click Custom Sort. • Select the first column to sort by in the Sort dialog box. • Select the sort order. • Click the Add Level button. • Repeat Steps 3-5 for each sort column. • Click OK. Sort dialog box

23. Sort a Table…continued sorted worksheet

24. Filter a Table To filter a table: • Click the desired filter arrow button. • Clear check boxes for items that you do not want to view. • Click OK. filter arrow button

25. Filter a Table…continued matching row numbers filter icon

26. Filter a Table…continued To remove a filter: • Click the desired filter arrow button. • Click Clear Filter From (column title). OR • Click the desired filter arrow button. • Click the Select All option. • Click OK. Clear Filter from option Select All option

27. CHECKPOINT 2 • Use this feature to view the worksheet with page numbers behind the cells. • Normal • Page Break Preview • Standard • Page Layout • This feature is used to display only certain records within the table that meet specified criteria. • Sort • Filter • Group • Ungroup Answer Answer Next Question Next Question • Use this feature to arrange cells in a specified order. • Sort • Filter • Group • Ungroup • When you filter a table, the row numbers of the matching items that were found are displayed in this color. • Red • Green • Black • Blue Answer Answer Next Question Next Slide

28. Use Comments To insert a comment: • Make the cell active where you want to insert the comment. • Click the Review tab. • Click the New Comment button in the Comments group. • Type the comment text in the comment box. • Click in the worksheet outside the comment box. New Comment button comment box

29. Use Comments…continued To open the comment box: • Press Ctrl + Home to move the active cell to A1. • Click the Review tab. • Click the Next button in the Comments group. • Continue clicking the Next button to scroll to the next comment box. Next button

30. Use Comments…continued To delete a comment: • Press Ctrl + Home to move the active cell to A1. • Click the Review tab. • Continue clicking the Next button to scroll to the comment box to be deleted. • Click the Delete button in the Comments group. Delete button

31. Use Comments…continued To print comments: • Click the Review tab. • Click the Show All Comments button in the Comments group. • Click the Page Layout tab. • Click the Page Setup dialog box launcher. • Click the Sheet tab in the Page Setup dialog box. • Click the down-pointing arrow to the right of Comments. • Click As displayed on sheet or At end of sheet. • Click OK. • Print the worksheet. comment options

32. Create a Workbook from a Template To create a workbook from a template: • Click the File tab. • Click the New tab. • Click Sample templates or an Office.com category. • Double-click the desired template in the center pane. • Fill in the data or other information as needed. • Save, print, and close. Sample templates

33. Create a Workbook from a Template…continued To create a workbook from a template downloaded from Office Online: • Click the File tab. • Click the New tab. • Browse for an Excel template in one of the categories in the Office.com Templates section. • Double-click the desired template in the center pane. • Fill in the data or other information as needed. • Save, print, and close. Office.com Templates

34. Open a Different File Format To open and convert a file: • Click the File tab. • Click the Open button. • Browse and select the desired file. • Click the Convert button at the Info tab Backstage view. • Click OK at the Microsoft Excel message stating that this action converts the workbook to the current file format. conversion message

35. Save in a Different File Format To save a workbook in Excel 97-2003 file format: • Click the File tab. • Click the Save & Send tab. • Click Change File Type. • Double-click Excel 97-2003 Workbook (*.xls). • Type a file name. • Click Save. • Click Continue at the Compatibility Checker dialog box. Save & Send tab Compatibility Checker dialog box

36. Save in a Different File Format…continued To save a workbook in another file format: • Click the File tab. • Click the Save & Send tab. • Click Change File Type. • Double-click the desired file type. • Type a file name. • Click Save. • Respond to message boxes as they occur. Save & Send tab

37. Create a PDF/XPS Copy To create a PDF copy of a worksheet: • Click the File tab. • Click the Save & Send tab. • Click Create PDF/XPS Document. • Click the Create PDF/XPS button. • Type a file name. • Click the Publish button. Create PDF/XPS button

38. Create a PDF/XPS Copy…continued To create a XPS copy of a worksheet: • Click the File tab. • Click the Save & Send tab. • Click Create PDF/XPS Document. • Click the Create PDF/XPS button. • Type a file name. • Click the down-pointing arrow at the right of the Save as type box and then click XPS Document (.*xps) in the drop-down list. • Click the Publish button. Save as type box

39. CHECKPOINT 3 • Use these to provide instructions, ask questions, or add other explanatory text to a cell. • shapes • clip art • comments • styles • This file format saves the data with a comma separating columns. • XPS • CSV • XLS • PDF Answer Answer Next Question Next Question • This is the name for worksheets that are formatted and have text and formulas created for specific uses. • templates • styles • themes • comments • When this type of worksheet is converted, the published file opens in an Adobe Reader window by default. • XPS • CSV • XLS • PDF Answer Answer Next Question Next Slide