Microsoft 77-727 Online Practice Questions and Exam Preparation

Microsoft 77-727 Online Questions & Answers

• Question 1:

  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.

  A. See explanation below.
  A. See explanation below.

  ---

  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-alphabetical-order.html

• Question 2:

  SIMULATION

  Project 6 of 7: Bike Tours Overview

  You are the owner of a small bicycle tour company summarizing trail rides that have been booked for the next six months.

  

  In cell C8 on the “Summer Bookings” worksheet, insert a function that joins the customer “Lastname” to the customer “Firstname” separated by a comma and space. (Example: Campbell, David).

  A. See explanation below.
  A. See explanation below.

  ---

  1. In cell C8 on the “Summer Bookings” worksheet, insert the following CONCAT function: “=CONCAT(E6 “, “D6)” OR “=E6and “, “, D6”.

  References: https://support.office.com/en-us/article/Combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed-b3c3-21340eb164a6

• Question 3:

  SIMULATION

  Project 5 of 7: City Sports Overview

  The city events manager wants to analyze the enrollment changes over the past five years for various adult and youth sports programs. You have been tasked to prepare tables for the analysis.

  

  

  On the “Youth Program” worksheet, create a table from the cell range A9:G19. Include row 9 as headers.

  A. See explanation below.
  A. See explanation below.

  ---

  1.

  On the “Youth Program” worksheet, select Format as Table from the Home tab.

  2.

  Choose the desired style for the table.

  3.

  Select the desired cell range of A9:G19 in the Format as Table dialog box.

  4.

  Mark row 9 as a header.

  5.

  Click OK.

  References: https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664

• Question 4:

  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.)

  A. See explanation below.
  A. See explanation below.

  ---

  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-937b-339e391393ba

• Question 5:

  SIMULATION

  Project 4 of 7: Car Inventory

  Overview

  You manage the office of a used car business. You have been asked to prepare the inventory list for a big annual sale.

  

  The discount price is 95 percent of the price. Modify column J to show the discount price for each car.

  A. See explanation below.
  A. See explanation below.

  ---

  1.

  In cell J10 on the “Car Inventory” worksheet enter “=G10 *.95” OR “=G10 * 95%”.

  2.

  Select cell J10, hold the Shift key on your keyboard, and use the down arrow key until all the cells the formula is required to be applied (J40).

  3.

  Hold down the Ctrl key on your keyboard and press D which would “copy down” the formula for the rows below. OR

  1.

  In cell J10 on the “Car Inventory” worksheet enter “=G10 *.95” OR “=G10 * 95%”.

  2.

  Select cell J10, then Right-click and select Copy, OR click Copy from the Clipboard group situated on the Home tab.

  3.

  Paste the formula by selecting Paste from the Clipboard group situated on the Home tab, OR Right-click then select Paste, OR click Paste from the Clipboard group situated on the Home tab, then click Paste Special and select Formulas, OR you could select Values for the formula results instead.

  4.

  Verify that the cell references in the formula are as desired.

  References: https://www.excelforum.com/excel-general/745891-calculate-discount-for-full-column.html https://support.office.com/en-us/article/move-or-copy-a-formula-1f5cf825-9b07-41b1-8719-bf88b07450c6

• Question 6:

  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]”.

  A. See explanation below.
  A. See explanation below.

  ---

  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-9C47-7DE9B76F577F

• Question 7:

  SIMULATION

  Project 5 of 7: City Sports Overview

  The city events manager wants to analyze the enrollment changes over the past five years for various adult and youth sports programs. You have been tasked to prepare tables for the analysis.

  

  

  Unhide the “Summary” worksheet.

  A. See explanation below.
  A. See explanation below.

  ---

  1. Select Format from the Home tab then choose Hide and Unhide then select Unhide. OR

  1.

  Hold on the Ctrl button on the keyboard and choose all the worksheet tabs then choose the Unhide option in the right menu.

  2.

  Click on the first worksheet tab, then hold down the Shift key on the keyboard as you click on the last worksheet tab, then choose the Unhide option. OR

  1.

  From the View tab, click Custom Views in the Workbook Views group.

  2.

  Click the “Summary” view which is hidden, then click Show.

  References: https://www.isunshare.com/blog/hide-or-unhide-sheets-in-excel-2016/

• Question 8:

  SIMULATION

  Project 6 of 7: Bike Tours Overview

  You are the owner of a small bicycle tour company summarizing trail rides that have been booked for the next six months.

  

  Insert page numbering in the center of the footer on the “Summer Bookings” worksheet using the format Page 1 of ?.

  A. See explanation below.
  A. See explanation below.

  ---

  1.

  On the “Summer Bookings” worksheet, click Header and Footer from the Text group situation on the Insert tab.

  2.

  Click Click to add footer which would display the Header and Footer tools which gets added to the Design tab.

  3.

  Specify where the page number should be by selecting the Center section box.

  4.

  On the Design tab in the Header and Footer Elements group, click Page Number.

  5.

  The placeholder and[Page] will appear in the selected section, to add the total number of pages type the word of followed by the space in the Header and Footer Elements group after clicking Number of Pages, then the placeholder and[Page] of and [Pages] appear.

  6.

  Click anywhere outside the header or footer area to display the page numbers in Page Layout View.

  7.

  Once you are done working in the Page Layout View, click Normal in the Workbook Views group situated on the View tab. OR You can also click Normal on the status bar.

  References: https://support.office.com/en-us/article/Insert-page-numbers-on-worksheets-27A88FB9-F54E-4AC4-84D7-BF957C6CE29C

• Question 9:

  SIMULATION

  Project 6 of 7: Bike Tours Overview

  You are the owner of a small bicycle tour company summarizing trail rides that have been booked for the next six months.

  

  On the “Summer Bookings” worksheet, remove the table functionality from the table. Retain the cell formatting and location of the data.

  A. See explanation below.
  A. See explanation below.

  ---

  1.

  Click Design from the Ribbon on Table Tools.

  2.

  In the Tools group, click on Convert to Range. OR

  1. Right-click the table then click on Table then press Convert to Range.

  References: https://support.office.com/en-us/article/convert-an-excel-table-to-a-range-of-data-0b326ff1-1764-4ebe-84ea-786265d41c77?redirectSourcePath=%252fen-us%252farticle%252fRemove-a-table-without-losing-the-data-or-table-format-ADF88635-90F5-4FAA-9417-19862F38CCE8andui=en-USandrs=en-USandad=US

• Question 10:

  SIMULATION

  Project 6 of 7: Bike Tours Overview

  You are the owner of a small bicycle tour company summarizing trail rides that have been booked for the next six months.

  

  In cell M10 on the “Summer Bookings” worksheet, insert a function that calculates the total amount of sales from the “Total” column for groups containing 12 or more people even if the order of the rows is changed.

  A. See explanation below.
  A. See explanation below.

  ---

  1. In cell M10 on the “Summer Bookings”, insert the following SUMIF formula: “=SUMIF(G6:G24, “andgt;= 12”, I6:I24).”’

  References: https://support.office.com/en-us/article/SUMIF-function-169B8C99-C05C-4483-A712-1697A653039B