Microsoft Microsoft Certifications 77-727 Questions & Answers

• Question 11:

  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.

  

  Add the Alternative Text Title “Adult Enrollment” to the “Adult_Program” table.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Right-click the text title “Adult_Program” and click Format Object then click Alt Text.

  2.

  Type “Adult Enrollment” in the Title box as desired.

  3.

  Click OK.

  References:

  https://support.office.com/en-us/article/add-alternative-text-to-a-shape-picture-chart-smartart-graphic-orother-object-44989b2a-903c-4d9a-b742-6a75b451c669#bkmk_o2016_2013

• Question 12:

  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.

  Correct Answer: See explanation below.

  Explanation:

  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 13:

  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.

  Correct Answer: See explanation below.

  Explanation:

  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 14:

  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 “Adult Program” worksheet, insert a Column sparkline for each sport that shows the enrollment for the past five years.

  Correct Answer: See explanation below.

  Explanation:

  1.

  On the “Adult Program” worksheet, select the area you want the sparkline to be inserted.

  2.

  Click Column in the Sparklines group from the Insert tab.

  3.

  Enter the range of cells in the Data Range box, in this case A10:A19.

  OR

  3.

  Select the range of cells by temporarily collapsing the dialog box and manually selecting the cells.

  4.

  Click OK and the Sparkline Tools will appear, the Design tab can be used to customize the sparklines.

  References:

  https://support.office.com/en-us/article/analyze-trends-in-data-using-sparklines-be6579cf-a8e3-471a-a459873614413ce1

• Question 15:

  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.

  

  Apply the Rose, Table Style Light 17 (Table Style Light 17) to the “Inventory” table.

  Correct Answer: See explanation below.

  Explanation:

  1.

  On the “Inventory” table, click on any cell.

  2.

  Click on Format as Table from the Home tab.

  3.

  Select the Rose, Table Style Light 17 as desired.

  References:

  https://support.office.com/en-us/article/Format-an-Excel-table-6789619F-C889-495C-99C22F971C0E2370

• Question 16:

  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.

  

  Modify the cell formatting of cell J9 to display the text on two lines.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Select the cell J9 where you want to modify the formatting.

  2.

  On the Home tab in the Cells group click on Format.

  3.

  Specify a row height by clicking Row Height and then typing in the desired height of 2 lines in the Row Height box.

  References:

  https://support.office.com/en-us/article/Wrap-text-in-a-cell-2A18CFF5-CCC1-4BCE-95E4-F0D4F3FF4E84

• Question 17:

  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.

  Correct Answer: See explanation below.

  Explanation:

  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 18:

  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.

  

  Simultaneously replace all instances of the text “Pickup” with the text “Truck”.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Click Find and Select from the Editing group situated on the Home tab.

  2.

  Click Replace.

  3.

  Type the text “Pickup” in the Fine what box.

  4.

  Click Options to further define the search, specify the “Car Inventory” worksheet select Sheet in the Within box.

  5.

  Type “Truck” in the Replace with box.

  6.

  Click Find All, and then click Replace All.

  7.

  Finalize by clicking OK.

  References:

  https://support.office.com/en-us/article/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef4808-b90f-fdb42f892e90

• Question 19:

  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.

  

  Configure the “Car Inventory” worksheet so the column headings in row 9 appear on all printed pages.

  Correct Answer: See explanation below.

  Explanation:

  1.

  On the “Car Inventory” worksheet, click Print Titles from the Page Setup group, situated on the PageLayout tab.

  2.

  Under Print Titles, on the Sheet tab, type the reference of the row you want to reappear (row 9) in the Rows to repeat at top box.

  References:

  https://support.office.com/en-us/article/Print-rows-with-column-headers-on-top-of-every-page-D3550133F6A1-4C72-AD70-5309A2E8FE8C

• Question 20:

  SIMULATION

  Project 3 of 7: Tree Inventory Overview

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

  

  Check the spreadsheet for accessibility problems. Correct the error by adding “Tree Nursery Inventory” as an alternative text file. You do not need to fix the warning.

  Correct Answer: See explanation below.

  Explanation:

  1.

  To check the accessibility select the Review tab from the ribbon.

  2.

  Select Check Accessibility.

  3.

  Review the results.

  4.

  Exit the Accessibility Checker.

  5.

  Right-click on the worksheet then click Format and then click Alt Text.

  6.

  Type “Tree Nursery Inventory” in the Description box.

  7.

  Click OK.

  References:

  https://support.office.com/en-us/article/Use-the-Accessibility-Checker-to-find-accessibility-issuesa16f6de0-2f39-4a2b-8bd8-5ad801426c7f