Circling Back 1
CircBack1.indd Page 146 7/30/11 12:40 PM user-s138
146
/Users/user-s138/Desktop/11
Circling Back 1
Circling Back 1 Cross-Cultural Solutions is a nonprofit organization that is recognized by the United Nations as an expert in the field of international volunteering. Whereas well-known programs such as the Peace Corps and Volunteer Service Overseas (VSO) require a two-year commitment, Cross-Cultural Solutions provides volunteer opportunities ranging from one to twelve weeks. The organization offers a choice of three programs with year-round start dates. The goal is to provide a balance of volunteer work, cultural activities, and learning activities with time to explore the host country. As an employee in the organization’s home office, part of your job is to create, edit, and format workbooks related to the programs and the individuals who volunteer.
Project 1: Creating and Formatting a Workbook The two- to twelve-week Volunteer Abroad program is Cross-Cultural Solutions’ most popular program because it offers the greatest flexibility in terms of locations and start dates. Volunteer work is personalized to the volunteer’s skills and interests. In this exercise, you will create a worksheet that contains details about this program. GET READY. LAUNCH Excel if it is not already running. 1. Click the File tab. Click New in the navigation area. Click the Create button. The Home tab should now be active. Add your header. Save as Volunteer Abroad - # username 2. Select cell A2 and key Volunteer Abroad. You have now created your worksheet header. 3. In row 4, key the following column labels: Country Location Language 4. CREATE a folder named Circling Back 1, and save the document as Volunteer Abroad. 5. Select columns A and C. Click Format, then click Column width, and then change the column width to 20 characters. 6. Change the width of column B to 25 characters. 7. Enter the following data for the countries and locations where the Volunteer Abroad program is available: Ghana, Volta Region Tanzania, Arusha Tanzania, Kilimanjaro China, Xi’an Thailand, Bangkok Thailand, Trang India, New Delhi India, Dharamsala Brazil, Salvador Costa Rica, San Carlos Costa Rica, Cartage Guatemala, Guatemala City Peru, Lima Peru, Ayacucho Russia, Yaroslavl 8. Click any cell in column A. In the Cells group, click the Insert arrow, then select Insert Sheet Columns. A new column is inserted to the left of the original column A. 9. Select cell A4 and key Continent.
CircBack1.indd Page 147 7/30/11 12:40 PM user-s138
/Users/user-s138/Desktop/11
Circling Back 1
147
10. Select the cell range A5:A7. Click Merge & Center in the Alignment group, then key Africa. 11. Select the cell range A8:A12. Click Merge & Center in the Alignment group, then key Asia. 12. Select the cell range A13:A18. Click Merge & Center in the Alignment group. Key Latin America. Next, click Wrap Text. 13. Select A19, and key Europe. Click Center in the Alignment group. 14. Using the Home tab and its groups, format column A as follows: Click Middle Align. Increase the font size to 14. Set the font color to blue. Double-click the column A boundary to AutoFit the contents. 15. Select a continent and the three columns associated with that continent. Click Format, then click Format Cells. Click the Border tab. Place an outline around each continent’s data. 16. Using the Alignment group, merge and center A2:D2. Click Cell Styles, then apply Heading 2 style. 17. With A1 active, click Insert, then select Insert Sheet Rows. 18. In A1, key Cross-Cultural Solutions. Merge and center the title above the columns and apply Heading style 1. 19. Merge and center A2:D2, then merge and center A4:D4. 20. Apply Heading 3 style to the column labels and add a light blue fill color. 21. Click the File tab, click Print, and view the Print Preview. 22. In the Page Setup area, make the following selections: Portrait Orientation Center Vertically and Horizontally 23. PRINT the worksheet. using Adobe PDF as Volunteer Abroad - # username in CBA Printing. 24. SAVE the changes. CLOSE the workbook. LEAVE Excel open for the next project.
Project 2: Formatting Cells and Ranges In addition to its Volunteer Abroad program, Cross-Cultural Solutions offers a two- to twelveweek Intern Abroad program for students interested in an international internship or academic credit and a one-week Insight Abroad program. You have prepared a workbook with data related to the volunteers who will depart for their assignments within the next two months. After preparing the workbook, you learned the volunteers’ assignments from the organization’s on-site coordinators. Therefore, you need to edit and format your worksheet. The Cross Cultural Volunteers file for this lesson is available on the book companion website or in WileyPLUS.
OPEN Cross Cultural Volunteers from the student data files.in the CirclingBack01 Add your header. Save as Volunteers, Project 2 - # username 1. With Sheet1 active and the Home tab displayed, delete column E.
folder.
2. Click the row 18 heading to select the entire row. Press Ctrl and select row 23. Continue to scroll through the worksheet, selecting the blank rows. 3. Click Delete, then click Delete Sheet Rows. 4. On the Insert tab, click Header & Footer. Key July and August Departures in the center Header pane. 5. Click Go To Footer, and key Prepared by (your name) in the left Footer pane. 6. Tab to the center Footer pane, then click the Current Date option. 7. Tab to the right Footer pane, then click the Page Number button. 8. Right-click the Sheet1 tab, and change the tab color to purple. 9. Right-click the Sheet2 tab and change the tab color to green. 10. With Sheet1 active, select column E, click the Home tab, and display the Number dialog box. Format the dates to display as day-month (i.e., 20-Jul).
CircBack1.indd Page 148 15/12/12 7:00 PM user-f409
148
Circling Back 1 11. Click the Sheet2 tab to make it the active worksheet. Format columns B and C as Currency and decrease the decimals to zero. Print using Adobe PDF Volunteers, Project 2. as Volunteers, Project 2 - # username in CBA Printing. 12. SAVE the workbook as LEAVE the workbook open for the next project.
Project 3: Preparing a Workbook for Printing and Display Now that you have completed the data entries for the Cross-Cultural Volunteers workbook, you will format and prepare the worksheet for printing. USE the Volunteers, Project 2 workbook from the previous project. Save as Volunteers, Project 3 - # username 1. With Sheet1 active, set a Wide margin. 2. Click Page Break Preview, then move the page breaks so that page 1 ends with row 30 and page 2 ends with row 60. 3. Prepare the workbook for printing as follows: Use Landscape Orientation. Print the Guidelines. Print the titles (row4) at the top of all pages.
The Blue Hills image file for this lesson is available on the book companion website or in WileyPLUS.
4. Examine the Print Preview in Backstage to verify that the printout will be in landscape orientation, that the column labels print on pages 2 and 3, and that only the cells that contain data are included in the printout. 5. Print the entire workbook. 6. Prepare Sheet1 for display at a meeting. On the Page Layout tab, click Background. Select the Blue Hills image from the student data files, and click Insert. Print using Adobe PDF Volunteers, Project 3. as Volunteers, Project 3 - # username in CBA Printing. 7. SAVE the workbook as CLOSE Excel.
146
/Users/user-s138/Desktop/11
Circling Back 1
Circling Back 1 Cross-Cultural Solutions is a nonprofit organization that is recognized by the United Nations as an expert in the field of international volunteering. Whereas well-known programs such as the Peace Corps and Volunteer Service Overseas (VSO) require a two-year commitment, Cross-Cultural Solutions provides volunteer opportunities ranging from one to twelve weeks. The organization offers a choice of three programs with year-round start dates. The goal is to provide a balance of volunteer work, cultural activities, and learning activities with time to explore the host country. As an employee in the organization’s home office, part of your job is to create, edit, and format workbooks related to the programs and the individuals who volunteer.
Project 1: Creating and Formatting a Workbook The two- to twelve-week Volunteer Abroad program is Cross-Cultural Solutions’ most popular program because it offers the greatest flexibility in terms of locations and start dates. Volunteer work is personalized to the volunteer’s skills and interests. In this exercise, you will create a worksheet that contains details about this program. GET READY. LAUNCH Excel if it is not already running. 1. Click the File tab. Click New in the navigation area. Click the Create button. The Home tab should now be active. Add your header. Save as Volunteer Abroad - # username 2. Select cell A2 and key Volunteer Abroad. You have now created your worksheet header. 3. In row 4, key the following column labels: Country Location Language 4. CREATE a folder named Circling Back 1, and save the document as Volunteer Abroad. 5. Select columns A and C. Click Format, then click Column width, and then change the column width to 20 characters. 6. Change the width of column B to 25 characters. 7. Enter the following data for the countries and locations where the Volunteer Abroad program is available: Ghana, Volta Region Tanzania, Arusha Tanzania, Kilimanjaro China, Xi’an Thailand, Bangkok Thailand, Trang India, New Delhi India, Dharamsala Brazil, Salvador Costa Rica, San Carlos Costa Rica, Cartage Guatemala, Guatemala City Peru, Lima Peru, Ayacucho Russia, Yaroslavl 8. Click any cell in column A. In the Cells group, click the Insert arrow, then select Insert Sheet Columns. A new column is inserted to the left of the original column A. 9. Select cell A4 and key Continent.
CircBack1.indd Page 147 7/30/11 12:40 PM user-s138
/Users/user-s138/Desktop/11
Circling Back 1
147
10. Select the cell range A5:A7. Click Merge & Center in the Alignment group, then key Africa. 11. Select the cell range A8:A12. Click Merge & Center in the Alignment group, then key Asia. 12. Select the cell range A13:A18. Click Merge & Center in the Alignment group. Key Latin America. Next, click Wrap Text. 13. Select A19, and key Europe. Click Center in the Alignment group. 14. Using the Home tab and its groups, format column A as follows: Click Middle Align. Increase the font size to 14. Set the font color to blue. Double-click the column A boundary to AutoFit the contents. 15. Select a continent and the three columns associated with that continent. Click Format, then click Format Cells. Click the Border tab. Place an outline around each continent’s data. 16. Using the Alignment group, merge and center A2:D2. Click Cell Styles, then apply Heading 2 style. 17. With A1 active, click Insert, then select Insert Sheet Rows. 18. In A1, key Cross-Cultural Solutions. Merge and center the title above the columns and apply Heading style 1. 19. Merge and center A2:D2, then merge and center A4:D4. 20. Apply Heading 3 style to the column labels and add a light blue fill color. 21. Click the File tab, click Print, and view the Print Preview. 22. In the Page Setup area, make the following selections: Portrait Orientation Center Vertically and Horizontally 23. PRINT the worksheet. using Adobe PDF as Volunteer Abroad - # username in CBA Printing. 24. SAVE the changes. CLOSE the workbook. LEAVE Excel open for the next project.
Project 2: Formatting Cells and Ranges In addition to its Volunteer Abroad program, Cross-Cultural Solutions offers a two- to twelveweek Intern Abroad program for students interested in an international internship or academic credit and a one-week Insight Abroad program. You have prepared a workbook with data related to the volunteers who will depart for their assignments within the next two months. After preparing the workbook, you learned the volunteers’ assignments from the organization’s on-site coordinators. Therefore, you need to edit and format your worksheet. The Cross Cultural Volunteers file for this lesson is available on the book companion website or in WileyPLUS.
OPEN Cross Cultural Volunteers from the student data files.in the CirclingBack01 Add your header. Save as Volunteers, Project 2 - # username 1. With Sheet1 active and the Home tab displayed, delete column E.
folder.
2. Click the row 18 heading to select the entire row. Press Ctrl and select row 23. Continue to scroll through the worksheet, selecting the blank rows. 3. Click Delete, then click Delete Sheet Rows. 4. On the Insert tab, click Header & Footer. Key July and August Departures in the center Header pane. 5. Click Go To Footer, and key Prepared by (your name) in the left Footer pane. 6. Tab to the center Footer pane, then click the Current Date option. 7. Tab to the right Footer pane, then click the Page Number button. 8. Right-click the Sheet1 tab, and change the tab color to purple. 9. Right-click the Sheet2 tab and change the tab color to green. 10. With Sheet1 active, select column E, click the Home tab, and display the Number dialog box. Format the dates to display as day-month (i.e., 20-Jul).
CircBack1.indd Page 148 15/12/12 7:00 PM user-f409
148
Circling Back 1 11. Click the Sheet2 tab to make it the active worksheet. Format columns B and C as Currency and decrease the decimals to zero. Print using Adobe PDF Volunteers, Project 2. as Volunteers, Project 2 - # username in CBA Printing. 12. SAVE the workbook as LEAVE the workbook open for the next project.
Project 3: Preparing a Workbook for Printing and Display Now that you have completed the data entries for the Cross-Cultural Volunteers workbook, you will format and prepare the worksheet for printing. USE the Volunteers, Project 2 workbook from the previous project. Save as Volunteers, Project 3 - # username 1. With Sheet1 active, set a Wide margin. 2. Click Page Break Preview, then move the page breaks so that page 1 ends with row 30 and page 2 ends with row 60. 3. Prepare the workbook for printing as follows: Use Landscape Orientation. Print the Guidelines. Print the titles (row4) at the top of all pages.
The Blue Hills image file for this lesson is available on the book companion website or in WileyPLUS.
4. Examine the Print Preview in Backstage to verify that the printout will be in landscape orientation, that the column labels print on pages 2 and 3, and that only the cells that contain data are included in the printout. 5. Print the entire workbook. 6. Prepare Sheet1 for display at a meeting. On the Page Layout tab, click Background. Select the Blue Hills image from the student data files, and click Insert. Print using Adobe PDF Volunteers, Project 3. as Volunteers, Project 3 - # username in CBA Printing. 7. SAVE the workbook as CLOSE Excel.
