Guided Project 5-2

  1. Group and format worksheets.
  2. Build static and dynamic data consolidation by position.
  3. Use built-in number formats.
  4. Assign a data type to extract information.
  5. Link workbooks with a formula.
  6. Insert a screenshot in a worksheet.
  7. Size and format a screenshot.
  8. Insert an object hyperlink.
  9. Mark a workbook as final.
  10. Open Eller-05.xlsx from your student data files and save it as [your initials] Excel 5-2a.
  11. Edit grouped worksheets.
    1. Select the 2022 sheet tab.
    2. Press Shift and click the 2020 sheet tab. Three sheets are selected.
    3. Select cells A4:A8.
    4. Click the Fill Color drop-down arrow [Home tab, Font group] and choose No Fill.
    5. Select cell A9 and right-align it.
    6. Select cells B9:F9 and click the AutoSum button [Home tab, Editing group].
    7. Select cell A2 and change the label to Number of Service Billings.
    8. Select cell A3, type Service, and center it.
    9. Select cell A1.
    10. Right-click the 2020 tab and choose Ungroup Sheets.
  12. Prepare a consolidation sheet.
    1. Make a copy of the 2020 sheet before the Recruitment sheet.
    2. Name the copy as Static.
    3. Select cells B4:E8 on the Static sheet and press Delete.
  13. Create a static data consolidation by position.
    1. Confirm that cells B4:E8 are selected on the Static sheet.
    2. Click the Consolidate button [Data tab, Data Tools group].
    3. Select and delete references in the All references box, if any.
    4. Verify that Sum is the selected function.
    5. Click the Reference box and select the 2020 sheet tab.
    6. Select cells B4:E8 on the 2020 sheet.
    7. Click Add in the Consolidate dialog box.
    8. Click the 2021 sheet tab, verify that the same cell range is selected, and click Add.
    9. Click the 2022 sheet tab and add the same range to the Consolidate dialog box (Figure 5-75).
    10. Click OK. The total number of billings is 953.
    11. Press Ctrl+Home.
  14. Prepare a consolidation sheet.
    1. Make a copy of the Static sheet before the Recruitment sheet.
    2. Name the copy as Dynamic.
    3. Select cells B4:E8 on the Dynamic sheet and press Delete.
    4. Select and delete column F.
    5. Edit the label in cell A9 to display Average Billings.
    6. Select cells B9:E9 and click the Find & Select button [Home tab, Editing group].
    7. Choose Replace.
    8. Type sum in the Find what box.
    9. Type average in the Replace with box.
    10. Click Options to expand the dialog box.
    11. Confirm that the Look in choice is Formulas.
    12. Click Find All (Figure 5-76). There are four cells found.
    13. Click Replace All and click OK in the message box.
    14. Close the Find and Replace dialog box. The formulas display #DIV/0! as an error because the data is missing.
  15. Create a dynamic data consolidation by position.
    1. Select cells B4:E8 on the Dynamic sheet.
    2. Click the Consolidate button [Data tab, Data Tools group].
    3. Click the Function arrow and choose Average.
    4. Review the All references list. If you just completed step 4, the correct references are listed. If they are not listed, select the same cell ranges as those shown in Figure 5-77 and detailed in step 4.
      1. Select the Create links to source data box and click OK (Figure 5-77). The results are formatted as General.
      2. Edit the label in cell A2 to insert the word Average before “Number.”
    2. Apply a built-in number format.
      1. Select cells B7:E24 on the Dynamic sheet. Additional rows result from the outline format.
      2. Click the Number drop-down list [Home tab, Number group] and choose Number. The Number format automatically formats values with two decimal places.
      3. Expand row 15 for Web site development. All values have been reformatted.
      4. Select cell B14. The cell refers to the January data on the 2022 sheet (Figure 5-78).
      5. Select cell A1.
    3. Assign the Universities data type. Office 365/2021 Note: The Universities Data Types feature may not be available in all versions of Office 365 or Office 2021.
      1. Click the Recruitment sheet tab.
      2. Select cells A4:A8.
      3. Click the Universities button [Data tab, Data Types group]. Not all schools are recognized, and the Data Selector offers suggestions for schools with similar names (Figure 5-79).
    1. Click Select for the first (top) suggestion in the Data Selector pane to assign the data type to cell A4, Clark College.
    2. Confirm that Howard University in cell C6 is selected and that the Data Selector displays the name with the 1867 seal.
    3. Click Select for Howard University.
    4. Select the first option for Lewis & Clark College.
    5. Select cells A4:A8 and click the Insert Data button.
    6. Scroll the field list and click location. When data is extracted, you may see a #BUSY message. The location names are data types and display a different icon (Figure 5-80).
    7. Select cells A4:A8 and click the Insert Data button again.
    8. Select the website field to insert that information in column C.
    9. AutoFit columns A:C.
    10. Select cells A1:A2 and set the font size to 14.
    11. Apply a Bottom Border to cells A2:C2.
    12. Apply All Borders to cells A4:C8.
    13. Turn off the display of Gridlines and select cell A10 (Figure 5-81).
  2. Save the workbook and leave it open.
  3. Link workbooks with a formula.
    1. Open EllerLinked-05.xlsx from your student data files and save it as [your initials] Excel 5-2b.
    2. Click the Arrange All button [View tab, Window group].
    3. Choose Vertical and click OK. It does not matter which workbook is in the left pane.
    4. Select cell B5 in the [your initials] Excel 5-2b workbook and type =.
    5. Double-click the Static sheet tab in [your initials] Excel 5-2a to activate it and select cell F4.
    6. Press Enter. The focus returns to [your initials] Excel 5-2b and the formula displays an absolute reference. The result is 160.
  4. Copy and edit a linked formula.
    1. Select cell B5 in [your initials] Excel 5-2b.
    2. Click within $F$4 in the Formula bar and press F4 (FN+F4) three times to remove the dollar signs.
    3. Click after F4, type *108% to multiply by 108%, and press Enter (Figure 5-82). The first result is 172.8.
    4. Copy the formula in cell B5 to cells B6:B9 without formatting to preserve the bottom border.
    5. Format cells B5:B9 as Number with zero decimal places.
    6. Maximize the [your initials] Excel 5-2b window.
  5. Insert and format a screenshot in a workbook.
    1. Switch to the [your initials] Excel 5-2a workbook and maximize the window.
    2. Select the Static sheet tab.
    3. Deselect the Gridlines box [View tab, Show group] and select cell M1.
    4. Switch to [your initials] Excel 5-2b and select cell A11. (It is easier to take screenshots when only the two windows of interest are open, so if you have other programs running, close them and return to [your initials] Excel 5-2b.)
    5. Click the Take a Screenshot button [Insert tab, Illustrations group].
    6. Choose Screen Clipping from the gallery. The focus switches to [your initials] Excel 5-2a and the screen dims after a few seconds.
    7. Draw a rectangle with the crosshair pointer to frame the data in cells A1:F9 without row or column headings and release the pointer.
    8. Click the More button [Picture Format tab, Picture Styles group].
    9. Find and select Center Shadow Rectangle (Figure 5-83).

Insert an object hyperlink.

  1. Select the screenshot object if necessary.
  2. Click the Link button [Insert tab, Links group].
  3. Select Existing File or Web Page in the Insert Hyperlink dialog box.
  4. Select Current Folder in the Look in group.
  5. Select [your initials] Excel 5-2a in the file name list. If your file is not listed, navigate to the correct folder to locate it.
  6. Click ScreenTip and type Click to open source file (Figure 5-84).
    1. Click OK in the Set Hyperlink ScreenTip dialog box.
    2. Click OK in the Insert Hyperlink dialog box.
    3. Select cell A1.
    4. Switch to [your initials] Excel 5-2a.
    5. Save and close the workbook.
  2. Mark a workbook as final.
    1. Verify that the focus is in [your initials] Excel 5-2b.
    2. Click the File tab and select Info.
    3. Click the Protect Workbook button and select Mark as Final.
    4. Click OK to resave the file.
    5. Click OK to close the message box.
    6. Click Edit Anyway in the warning bar.
  3. Click the hyperlink (the screenshot) in [your initials] Excel 5-2b to test it. The [your initials] Excel 5-2a workbook opens.
  4. Close both [your initials] Excel 5-2a and [your initials] Excel 5-2b without resaving.

Do you need help with this assignment or any other? We got you! Place your order and leave the rest to our experts.

Place Your Order

Quality Guaranteed

Any Deadline

No Plagiarism