Microsoft Microsoft Certifications 77-727 Questions & Answers

• Question 21:

  SIMULATION

  Project 3 of 7: Tree Inventory Overview

  You are updating the inventory worksheet for a local tree farm.

  

  Configure the worksheet so that row 9 and the WordArt remain visible as you scroll vertically.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Select the cells below the rows and to the right of the columns you want to keep visible, which in this case is row 10 and column H.

  2.

  Click Freeze Panes from the Freeze Panes group in the View tab.

  References: https://support.office.com/en-ie/article/Freeze-panes-to-lock-rows-and-columns-dab2ffc9-020d-4026-812167dd25f2508f

• Question 22:

  SIMULATION

  Project 3 of 7: Tree Inventory Overview

  You are updating the inventory worksheet for a local tree farm.

  

  Hide rows 29 and 30.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Select the rows you want to hide, in this case rows number 29 and 30 by clicking on them.

  2.

  Right-click on the selected rows then click Hide.

  References:

  https://support.office.com/en-us/article/Hide-or-show-rows-or-columns-659c2cad-802e-44ee-a614dde8443579f8

• Question 23:

  SIMULATION

  Project 3 of 7: Tree Inventory

  Overview

  You are updating the inventory worksheet for a local tree farm.

  

  Change the margins to 1.0’’ (2.54 cm) on the top and bottom, 0.75’’ (1.90 cm) on the left and right, with a 0.5’’ (1.27 cm) header and footer.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Select the worksheet you want to apply the margin changes to.

  2.

  Select Margins in the Page Setup group from the Page Layout tab.

  3.

  Click Custom Margins, then in the Top and Bottom dialog box enter 1.0’’ (2,54cm), in the Left and Right dialog box enter 0.75’’(1.90cm), and enter 0.5’’(1.27cm) in the Header and Footer dialog box.

  References:

  https://support.office.com/en-us/article/Set-page-margins-before-printing-a-worksheet-353F52FC-0D2C4958-9166-99FBD22B963A

• Question 24:

  SIMULATION

  Project 2 of 7: Donor List

  Overview

  You are an executive assistant for a non-profit organization named Bellows College. You are updating a workbook containing lists of donors.

  

  Arrange the worksheets so that “$5,000 Donors” is first.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Click Sort Sheets from the Worksheet group in the Kutools Plus tab.

  2.

  Click the Alpha sort button OR the Alpha Numeric Sort button in the Sort Sheets dialog box, the results can be previewed in the New sheets tab order list box.

  3.

  Use the Move up or Move down button to manually change the order of the sheets so that the “$5,000 Donors” worksheet is first in the New sheet tabs order list.

  4.

  Click OK once satisfied with the results.

  References:

  https://www.extendoffice.com/product/kutools-for-excel/sort-worksheets-in-one-workbook-in-alphabeticalorder.html

• Question 25:

  SIMULATION

  Project 2 of 7: Donor List Overview

  You are an executive assistant for a non-profit organization named Bellows College. You are updating a workbook containing lists of donors.

  

  On the “$5,000 Donors” worksheet, hyperlink cell C3 to the email address “[email protected]”.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Click on cell C3.

  2.

  Enter the following formula: “=HYPERLINK([email protected], C3)

  References:

  https://support.office.com/en-us/article/HYPERLINK-function-333C7CE6-C5AE-4164-9C477DE9B76F577F

• Question 26:

  SIMULATION

  Project 2 of 7: Donor List Overview

  You are an executive assistant for a non-profit organization named Bellows College. You are updating a workbook containing lists of donors.

  

  Add a new worksheet named “$1,000 Donors” to the workbook.

  Correct Answer: See explanation below.

  Explanation:

  1. Select the plus icon at the bottom of the screen. OR

  1. Select Insert from the Home tab, then click Insert Sheet. References:

  https://support.office.com/en-us/article/Insert-or-delete-a-worksheet-19d3d21e-a3b3-4e13-a422d1f43f1faaf2

• Question 27:

  SIMULATION

  Project 2 of 7: Donor List

  Overview

  You are an executive assistant for a non-profit organization named Bellows College. You are updating a workbook containing lists of donors.

  

  Navigate to the range named “Oregon”, and remove the contents of the selected cells.

  Correct Answer: See explanation below.

  Explanation:

  1. Click Find and Select from the Home tab then click on Go To. OR

  1.

  Press Ctrl+G on the keyboard.

  2.

  Double-click the name range you want, in this case “Oregon”.

  3.

  Delete the contents in the selected cells by pressing Delete on your keyboard. References:

  https://support.office.com/en-us/article/Find-named-ranges-14513DF5-0D79-4CD5-9A03-968905B244E6

• Question 28:

  SIMULATION

  Project 2 of 7: Donor List Overview

  You are an executive assistant for a non-profit organization named Bellows College. You are updating a workbook containing lists of donors.

  

  Beginning at cell A5 of the “$5,000 Donors” worksheet, import the data from the tab-delimited source file, contributors.txt, located in the Documents folder. (Accept all defaults.)

  Correct Answer: See explanation below.

  Explanation:

  1.

  Select cell A5 of the “$5,000 Donors” worksheet where you want the source file to be imported to.

  2.

  Click on File, then select Open.

  3.

  Select Text Files from the Open dialog box.

  4.

  Locate the contributors.txt file in the Documents folder, then double-click it.

  5.

  Click Finish to complete the operation.

  References:

  https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b339e391393ba

• Question 29:

  SIMULATION

  Project 1 of 7: Tailspin Toys

  Overview

  You recently opened an online toy store. You have sold products to 500 individual customers. You are evaluating customer data and order data.

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  On the “Customers by Order” worksheet, use an Excel data tool to remove all records with duplicate “CustomerID” values from the table. Do not remove any other records.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Select the cells in the “CustomerID” column which you want to have checked for duplicates.

  2.

  Select Remove Duplicates from the Data tab.

  3.

  Select the “CustomerID” column which you want to have checked under the Columns options.

  4.

  Click OK.

  References:

  https://support.office.com/en-us/article/Find-and-remove-duplicates-00E35BEA-B46A-4D5D-B28E66A552DC138D

• Question 30:

  SIMULATION

  Project 1 of 7: Tailspin Toys Overview

  You recently opened an online toy store. You have sold products to 500 individual customers. You are evaluating customer data and order data.

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  On the “Orders” worksheet, enter a formula in cell J2 that uses an Excel function to return the “OrderTotal” value of the individual order that has the highest “OrderTotal” value.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Select the cell J2 where you want the highest “OrderTotal” value to be.

  2.

  Type in the formula: =MAX(G1:G550).

  References:

  https://support.office.com/en-us/article/calculate-the-smallest-or-largest-number-in-a-range-45fe249f-96c3443b-8e9f-87f16c48462c