Microsoft 77-727 Online Practice Questions and Exam Preparation

Microsoft 77-727 Online Questions & Answers

• Question 11:

  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, use an automatic formatting method to format cells in the “OrderTotal” column that contain above-average values by applying Green Fill with Dark Green Text. Use a technique that automatically updates the formatting if the column values change.

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

  ---

  1.

  Select the cells in the “OrderTotal” column which is the data range you are required to highlight the cells which are above-average.

  2.

  Click Conditional Formatting from the Home tab, then select Top/Bottom Rules, then select the Above Average option.

  3.

  A popped up dialog box will then appear, select the Green Fill with Dark Green Text option as desired.

  4.

  Click OK.

  References: https://www.extendoffice.com/documents/excel/2195-excel-highlight-above-average.html

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

  

  

  On the “Adult Program” worksheet, insert a Column sparkline for each sport that shows the enrollment for the past five years.

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

  ---

  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-a459-873614413ce1

• Question 13:

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

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

  ---

  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-ecef-4808-b90f-fdb42f892e90

• Question 14:

  SIMULATION

  Project 3 of 7: Tree Inventory Overview

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

  

  Hide rows 29 and 30.

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

  ---

  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-a614-dde8443579f8

• Question 15:

  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.

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

  ---

  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:

• Question 16:

  SIMULATION

  Project 7 of 7: Farmers Market

  Overview

  You are the Director of a local farmers’ market. You are creating and modifying charts for a report which shows the amounts and variety of products sold during the season.

  

  

  On the “Summer Sales” worksheet, use the data in the “Products” and “Total” columns only to create a 3-D Pie chart. Position the new chart to the right of the column charts.

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

  ---

  1.

  Select the data you would like to use, in this case it would be the data in the “Products” and “Total” columns from the “Summer Sales” worksheet.

  2.

  Click on Insert Pie Chart situation on the Insert tab then pick the 3-D Pie chart as desired.

  3.

  Format the chart as desired by using Chart Elements, the Chart Styles, or the Chart Filters.

  4.

  Drag the Pie Chart to the desired location which is to the right of the column charts.

  References: https://support.office.com/en-us/article/Add-a-pie-chart-1A5F08AE-BA40-46F2-9ED0-FF84873B7863

• Question 17:

  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 “Products” worksheet, apply a number format to display the numbers in the “Weight” column to three decimal places.

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

  ---

  1.

  Select the cells in the “Weight” column which you want to apply the number formatting to.

  2.

  Click Increase Decimal till the desired decimal places of three is selected, this is found on the Home tab. OR

  1.

  Select the cells in the “Weight” column which you want to apply the number formatting to.

  2.

  On the Home tab, in the Number group, click the arrow next to the list of number formats, then select More Number Formats.

  3.

  Select the type of data you have in the Category list, in this case it would be Scientific.

  4.

  Enter the desired amount of decimal places you want to display in the Decimal places box, in this case it would be three.

  References: https://support.office.com/en-us/article/Round-a-number-to-the-decimal-places-I-want-49B936F9-6904-425D-AA98-02FFB7F9A17B

• Question 18:

  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.

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

  ---

  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-issues-a16f6de0-2f39-4a2b-8bd8-5ad801426c7f

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

  

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

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

  ---

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

  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” worksheet, sort the table to order the records by the “CountryOrRegion” field, with customers in the United States first and customers in Canada second.

  Then sort the customers in each country alphabetically (from A to Z) by the “StateOrProvince” field.

  Finally, sort the customers in each state or province in ascending order by the ‘PostalCode” field.

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

  ---

  1.

  Select a cell in the “CountryOrRegion” field on the “Customers” worksheet.

  2.

  Select Sort from the Sort and Filter group in the Data tab.

  3.

  In the Sort dialog box, under Column in the Sort by box, Select the “CountryOrRegion” field.

  4.

  Under the Sort On option, select the type of sort desired, in this case Values, to sort by text.

  5.

  Under Order, select Custom List, then set it for customers in the United States to appear first, then the customers in Canada.

  6.

  Then to add another column to sort by, click Add Level.

  7.

  In the Sort dialog box, under Column in the Sort by box, Select the “StateOrProvince” field.

  8.

  Under the Sort On option, select Values.

  9.

  Under Order, select A to Z.

  10.

  Add a final column by clicking Add Level.

  11.

  In the Sort dialog box, under Column in the Sort by box, Select the “PostalCode” field.

  12.

  Under Sort On, select Values.

  13.

  Under Order, select Smallest to Largest.

  14.

  Click OK.

  References: https://support.office.com/en-us/article/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654