Microsoft Excel 2010

1. MicrosoftExcel 2010 Chapter 6 Working with Multiple Worksheets and Workbooks

2. Objectives • Use the ROUND function • Use custom format codes • Define, apply, and remove a style • Add a worksheet to a workbook • Create formulas that use 3-D cell references • Add data to multiple worksheets at the same time Working with Multiple Worksheets and Workbooks

3. Objectives • Add a header or footer and change margins • Insert and move a page break • Save a workbook as a PDF or XPS file • Create a workspace file • Consolidate data by linking workbooks Working with Multiple Worksheets and Workbooks

4. Project – Consolidated Expenditures Worksheet Working with Multiple Worksheets and Workbooks

5. Project – Consolidated Expenditures Worksheet Working with Multiple Worksheets and Workbooks

6. General Project Guidelines • Design the consolidated worksheet and plan the formatting • Identify additional worksheets needed in the workbook • Plan the layout and location of the required custom format codes • Examine the options, including headers, margins, and page breaks, that you have for printing worksheets • Identify workbooks to be consolidated into a workspace and then linked to create a consolidated workbook of the initial workbooks Working with Multiple Worksheets and Workbooks

7. Entering Sample Data Using the Fill Handle • Enter the values from which to create a series • Select the cells with the values • Drag the fill handle through the cells to fill Working with Multiple Worksheets and Workbooks

8. The ROUND Function and Entering Formulas • The ROUND function can decrease the number of decimal places that can appear in a cell =ROUND (number, number of digits) The number argument can be a number, cell reference, or formula Working with Multiple Worksheets and Workbooks

9. Assigning a Currency Style Using the Format Cells Dialog Box • Select the cell(s) to format with Currency style • Right click a selected cell • Click Format Cells on the shortcut menu to display the Format Cells dialog box • If necessary, click the Number tab to display the Number tab, click Currency in the Category list to select the type of format to apply, and then click the desired currency format • Click the OK button Working with Multiple Worksheets and Workbooks

10. Assigning a Currency Style Using the Format Cells Dialog Box Working with Multiple Worksheets and Workbooks

11. Format Codes Working with Multiple Worksheets and Workbooks

12. Creating and Assigning a Custom Format Code and a Comma Style Format • Select the range of cells to format, and then click Format Cells on the shortcut menu to display the Format Cells dialog box • If necessary, click the Number tab to display the Number tab and then click Custom in the Category list to begin creating a custom format code • Delete the word General in the Type box, and then enter the desired format code • Click the OK button • Click the Comma Style button to display the numbers in the selected ranges using the Comma style format Working with Multiple Worksheets and Workbooks

13. Creating and Assigning a Custom Format Code and a Comma Style Format Working with Multiple Worksheets and Workbooks

14. Cell Styles Working with Multiple Worksheets and Workbooks

15. Creating a New Style • Click the Cell Styles button (Home tab | Styles group) to display the Cell Styles gallery • Click the New Cell Style button in the Cell Styles gallery to display the Style dialog box • Type the desired name for the new style • Click the Format button to display the Format Cells dialog box • Select the desired category and type • Click the Alignment tab, and then select the desired alignment Working with Multiple Worksheets and Workbooks

16. Creating a New Style • Click the OK button • When the Style dialog box becomes active, click Font, Border, Fill, and Protection to clear the check boxes, indicating that the new style does not use these characteristics • Click the OK button to add the new style Working with Multiple Worksheets and Workbooks

17. Creating a New Style Working with Multiple Worksheets and Workbooks

18. Applying a New Style • Select the desired cell(s) and then click the Cell Styles button (Home tab | Styles group) to display the Cell Styles gallery • Click the name of the new style to assign the style to the selected cell(s) Working with Multiple Worksheets and Workbooks

19. Adding a Worksheet to a Workbook • Click the Sheet2 tab at the bottom of the window and then click the Insert Cells button arrow (Home tab | Cells group) to display the Insert Cells menu • Click Insert Sheet on the Insert Cells menu to add a new worksheet to a workbook, in this case a sheet named Sheet 4 between Sheet 1 and Sheet 2 Working with Multiple Worksheets and Workbooks

20. Adding a Worksheet to a Workbook Working with Multiple Worksheets and Workbooks

21. Copying the Contents of a Worksheet to Other Worksheets in a Workbook • Click the tab for the sheet containing the data to copy • Click the Select All button to select the entire worksheet and then click the Copy button (Home tab | Clipboard group) to copy the contents of the worksheet • Click the tab(s) for the sheet(s) to paste the data • Click the Paste button (Home tab | Clipboard group) to copy the data on the Office Clipboard to the selected sheet(s) Working with Multiple Worksheets and Workbooks

22. Copying the Contents of a Worksheet to Other Worksheets in a Workbook Working with Multiple Worksheets and Workbooks

23. Drilling an Entry through Worksheets • With Sheet1 active, hold down the SHIFT key and then click the remaining tab(s) for the worksheet(s) to contain the entry • Type the entry in the desired cell Working with Multiple Worksheets and Workbooks

24. Drilling an Entry through Worksheets Working with Multiple Worksheets and Workbooks

25. Referencing Cells in Other Sheets in a Workbook • To reference cells in other sheets in a workbook, you use the sheet name, which serves as the sheet reference, and the cell reference =‘Public Work’s!B5’ =‘Public Works’!B5 + ‘Police and Fire’!B5 + ‘Parks and Recreation’!B5 • You can enter a sheet reference in a cell by typing the sheet reference or by clicking the appropriate sheet tab while in Point mode Working with Multiple Worksheets and Workbooks

26. Referencing Cells in Other Sheets in a Workbook Working with Multiple Worksheets and Workbooks

27. More About Pasting Working with Multiple Worksheets and Workbooks

28. Changing Margins and Centering the Printout Horizontally • Select the worksheet tab(s) • Click the Page Setup Dialog Box Launcher (Page Layout tab | Page Setup group) to display the Page Setup dialog box • When Excel displays the Page Setup dialog box, if necessary, click the Margins tab • Set the top, bottom, left, and right margins as desired • Click the Horizontally check box in the ‘Center on page’ area to center the worksheet on the printed page horizontally • Click the OK button Working with Multiple Worksheets and Workbooks

29. Changing Margins and Centering the Printout Horizontally Working with Multiple Worksheets and Workbooks

30. Adding a Header and Footer • Click the Page Layout button on the status bar to display the worksheet in Page Layout view • If necessary, scroll the worksheet up until the Header area is displayed Working with Multiple Worksheets and Workbooks

31. Adding a Header and Footer • Scroll the workbook down to view the Footer area Working with Multiple Worksheets and Workbooks

32. Selecting and Deselecting Sheets Working with Multiple Worksheets and Workbooks

33. Inserting and Removing a Page Break • Select the cell where the page break should be inserted • Click the Breaks button (Page Layout tab | Page Setup group) to display the Breaks menu and then click Insert Page Break on the Breaks menu to insert a page break Working with Multiple Worksheets and Workbooks

34. Inserting and Removing a Page Break • To remove a page break, click the cell containing the page break, and then click the Breaks button (Page Layout tab | Page Setup group) to display the Breaks menu • Click Remove Page Break on the Breaks menu to remove the page breaks Working with Multiple Worksheets and Workbooks

35. Hiding Page Breaks • Open the Backstage view • Click the Options button in the Backstage view to display the Excel Options dialog box • Click the Advanced button to display Advanced Excel options • Scroll the window until the ‘Display options for this worksheet’ area appears • Click the ‘Show page breaks’ check box to clear the check box • Click the OK button to close the Excel Options dialog box and hide the page breaks Working with Multiple Worksheets and Workbooks

36. Hiding Page Breaks Working with Multiple Worksheets and Workbooks

37. Searching for and Opening Workbooks • Start Excel • Open the Backstage view • Click the Open button to display the Open dialog box and then select the location to search • Type the search text in the Search box • In the File list, click the workbook(s) to open • Click the Open button to open the selected workbook(s) Working with Multiple Worksheets and Workbooks

38. Searching for and Opening Workbooks Working with Multiple Worksheets and Workbooks

39. Searching for and Opening Workbooks • Click the Arrange All button (View tab | Window group) to display the Arrange Windows dialog box • Click Vertical (Arrange Windows dialog box) to arrange the windows vertically • Click the OK button to display the opened workbooks arranged vertically Working with Multiple Worksheets and Workbooks

40. Searching for and Opening Workbooks Working with Multiple Worksheets and Workbooks

41. Creating a Workspace File • With the desired workbooks opened, click the Save Workspace button (View tab | Window group) to display the Save Workspace dialog box • Navigate to the location to save the workspace • Type the desired workspace name in the File name text box • Click the Save button to save the file names of the workbooks open, of the workbooks displaying, and other display settings Working with Multiple Worksheets and Workbooks

42. Creating a Workspace File Working with Multiple Worksheets and Workbooks

43. Consolidating Data by Linking Workbooks • Open the desired workspace • While entering formulas, you can reference cells in other workbooks by clicking those cells • Delete the dollar signs ($) in the formulas so that the references are not absolute Working with Multiple Worksheets and Workbooks

44. Consolidating Data by Linking Workbooks Working with Multiple Worksheets and Workbooks

45. Chapter Summary • Use the ROUND function • Use custom format codes • Define, apply, and remove a style • Add a worksheet to a workbook • Create formulas that use 3-D cell references • Add data to multiple worksheets at the same time Working with Multiple Worksheets and Workbooks

46. Chapter Summary • Add a header or footer and change margins • Insert and move a page break • Save a workbook as a PDF or XPS file • Create a workspace file • Consolidate data by linking workbooks Working with Multiple Worksheets and Workbooks

47. MicrosoftExcel 2010 Chapter 6 Complete