Guided Project 5-1

  1. Group and edit worksheets.
  2. Build a static data consolidation by position.
  3. Create a static data consolidation by category.
  4. Link workbooks using an external reference.
  5. Create a custom number format.
  6. Insert, size, and position an image.
  7. Assign Geography data types.
  8. Insert and format a hyperlink.
  9. Encrypt a workbook with a password.
  10. Open the BlueLake-05 workbook from your student data files and save it as [your initials] Excel 5-1a.
  11. Group worksheets and type a label.
    1. Right-click the FirstQtr tab and choose Select All Sheets. Four sheets are grouped.
    2. Select cell A2 and change the font to Constantia, the Body font for the Flow theme used in this workbook. Theme fonts are at the top of the list.
    3. Type Specialty Sales Revenue by Department.
    4. Change the font for cell A1 to Constantia.
    5. Click the launcher in the Page Setup group on the Page Layout tab.
    6. Select the Landscape radio button on the Page tab.
    7. Select the Fit to radio button and verify that the scaling is 1-page wide and 1-page tall.
    8. Click the Header/Footer tab in the Page Setup dialog box.
    9. Click the Footer drop-down arrow and select [your initials] Excel 5-1a. This option places the file name in the center footer section.
    10. Click OK.
    11. Confirm or select cell A1.
    12. Right-click the FirstQtr tab and choose Ungroup Sheets.
  12. Create a consolidation sheet.
    1. Make a copy of the FirstQtr sheet and name it By Position.
    2. Place the copied sheet as the first sheet in the tabs.
    3. Select cells B5:F18 and press Delete (Figure 5-63).
    1. Build a static consolidation by position for two quarters.
      1. Select cell B5 on the By Position sheet.
      2. Click the Consolidate button [Data tab, Data Tools group].
      3. Choose the Sum function.
      4. Select each reference, if any, in the All references box and click Delete.
      5. Click the Reference box and click the FirstQtr worksheet tab.
      6. Select cells B5:F18.
      7. Click Add in the Consolidate dialog box.
      8. Click the SecondQtr tab. Verify that cells B5:F18 are selected.
      9. Click Add in the Consolidate dialog box.
      10. Click OK to complete the consolidation.
    2. Sort data for the funnel chart.
      1. Select cells B22:B26 on the By Position sheet. For the funnel chart to be accurate, the source data should be sorted in descending order.
      2. Click the Sort Largest to Smallest (Z to A) button [Data tab, Data Tools group]. A Sort Warning message box alerts you that only one column of data is selected.
      3. Confirm or select the Expand the selection radio button to include adjacent columns (Figure 5-64).
    1. Create a consolidation sheet.
      1. Select the ThirdQtr sheet tab. The city names are in alphabetical order moving left to right.
      2. Select the FourthQtr sheet tab. The city names are in different order.
      3. Make a copy of the FourthQtr sheet and name it By Category.
      4. Place the copied sheet as the first sheet in the tabs.
      5. Select cells B4:F18 and press Delete. You will include city names in this consolidation.
    2. Create a static data consolidation by category.
      1. Select cell B4 on the By Category sheet.
      2. Click the Consolidate button [Data tab, Data Tools group].
      3. Choose or verify the Sum function.
      4. Select each reference, if any, in the All references box and click Delete.
      5. Click the Reference box and click the FourthQtr worksheet tab.
      6. Select cells B4:F18.
      7. Click Add in the Consolidate dialog box.
      8. Click the ThirdQtr tab. Verify that cells B4:F18 are selected.
      9. Select the Top row box in the Use labels in group.
      10. Click Add. Since the FourthQtr sheet is first in the All references list, the city names in the By Category consolidation will be in the same order as the FourthQtr sheet (Figure 5-66).
      11. Click OK.
      12. Sort the values in cells B22:B26 in largest to smallest order, expanding the selection so that city names are sorted also (Figure 5-67).
      13. Select cell A1 and save the workbook.
  1. Open the BlueLakeLinked-05.xlsx workbook from your student data files and save it as [your initials] Excel 5-1b. Both workbooks are open.
  2. Tile the windows.
    1. Select the Totals sheet in [your initials] Excel 5-1b.
    2. Click the Arrange All button [View tab, Window group].
    3. Choose Vertical and click OK. It does not matter which workbook is on the left.
  3. Link workbooks with an external reference formula.
    1. Click cell B5 on the Totals sheet in [your initials] Excel 5-1b.
    2. Type = to start the external reference.
    3. Select the By Position sheet tab in [your initials] Excel 5-1a. Your first click selects the window and the second click selects the tab; you can double-click.
    4. Select cell G5. The cell address is absolute.
    5. Type + to add the next value.
    6. Select the By Category sheet tab and select cell G5 (Figure 5-68). The formula sums the value in cell G5 on the By Position and By Category sheets.
    8. Close the [your initials] Excel 5-1a workbook, save it if prompted, and maximize the window for [your initials] Excel 5-1b.
    9. Build a custom format.
      1. Click the Dept Codes sheet tab. You will modify the catalog codes to display “BL-” followed by a four-digit number.
      2. Select cells C5:C18.
      3. Click the Number launcher [Home tab, Number group] to open the Format Cells dialog box.
      4. Click Custom as the Category on the Number tab.
      5. Select the 0 code in the list.
      6. Click the Type box and type 000 so that the Sample code is 0015.
      7. Click in front of the first zero and type “BL”- in the Type box. The text characters must be enclosed in quotation marks, but the hyphen character displays as is.
      8. Click OK. The codes include the prefix and insignificant zeros as needed.
    10. Insert, size, and position a picture.
      1. Select cell B20 on the Dept Codes sheet.
      2. Click the Insert Pictures button [Insert tab, Illustrations group] and choose This Device.
      3. Navigate to the folder with your student data files.
      4. Select BlueLake and click Insert. The image has a white background and no outline.
      5. Click the Shape Height box [Picture Format tab, Size group].
      6. Replace the default height with .75 and press Enter. The width resizes proportionally.
      7. Drag and position the image to cover cells A1:A3 as shown in Figure 5-70. The white background is in front of the labels in rows 1:2.
      8. Change the Shape Height for the picture to .55 to size it to fit.
      9. Position the image as needed and select cell B20 (Figure 5-71).
    11. Assign the Geography data type.
      1. Click the City Demographics sheet tab.
      2. Select cells A4:A8.
      3. Click the Geography button [Data tab, Data Types group]. (There is a Cities data type which extracts similar data but may not be available in your version of Excel).
      4. Select cell A4 for Atlanta and click its Insert Data button.
      5. Select the Population field to insert that information in cell B4.
      6. Select cell A4 and click its Insert Data button again.
      7. Select Time zone(s) field to insert the zone in cell C4.
      8. Select cells B4:C4 and Autofill to row 8. There may be a problem with one of the cities (Figure 5-72).
      9. Select cell C8. The reference in the Formula bar is correct but the data is not available from the cloud source.
      10. Type an uppercase C and press Enter to manually enter the time zone.
      11. AutoFit columns B:C.
      12. Set the width of column A to 12 (Figure 5-73). As demographics update regularly, your results may not match the figures.
    12. Insert a hyperlink.
      1. Click the Totals sheet tab and select cell D4.
      2. Click the Link button [Insert tab, Links group].
      3. Select Place in This Document in the Insert Hyperlink dialog box.
      4. Select City Demographics in the Cell Reference group in the Or select a place in this document list.
      5. Select the default text in the Text to display box and type See Population.
      6. Click OK in the Insert Hyperlink dialog box.
    13. Format and test a hyperlink.
      1. Point to cell D4 to display the white cross pointer.
      2. Right-click to open the context menu and display the mini toolbar.
      3. Change the font size to 16 from the mini toolbar.
      4. Change the font color to Blue, Accent 1 (fifth column).
      5. Click cell D1 (Figure 5-74).
      6. Click the hyperlink to test it.
      7. Return to the Totals worksheet.
    14. Encrypt a workbook with a password.
    15. Click the Protect Workbook button [File tab, Info pane].
    16. Select Encrypt with Password.
    17. Type 123 in the Password box and click OK.
    18. Retype 123 to confirm the password and click OK.
    19. Click Close in the Info pane and save your workbook.

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