Microsoft Microsoft Certifications 77-727 Questions & Answers

• Question 31:

  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.

  Correct Answer: See explanation below.

  Explanation:

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

  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.

  Correct Answer: See explanation below.

  Explanation:

  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-6904425D-AA98-02FFB7F9A17B

• Question 33:

  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, enter a formula in cell N2 that uses an Excel function to return the average age of the customers based on the values in the “CurrentAge” column.

  Correct Answer: See explanation below.

  Explanation:

  1.

  Select cell N2 on the “Customers” worksheet, which is where you want the Excel function to return the result to.

  2.

  In the Address bar, type the average function syntax; =AVERAGE(K1:K101).

  References:

  https://support.office.com/en-us/article/AVERAGE-function-047BAC88-D466-426C-A32B-8F33EB960CF6

• Question 34:

  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.

  Correct Answer: See explanation below.

  Explanation:

  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-a6ae2e545c4a4654

• Question 35:

  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, format the table so that every other row is shaded. Use a technique that automatically updates the formatting if you insert a new row.

  Correct Answer: See explanation below.

  Explanation:

  1.

  On the “Customers” worksheet, click the Select All button OR press Ctrl+A.

  2.

  Click the arrow next to Conditional Formatting from the Home tab in the Styles group, then select New Rule.

  3.

  Click Select a Rule Type in the New Formatting Rule dialog box, then select Use a formula to determine which cells to format.

  4.

  In the Format values where this formula is true box, enter =MOD(ROW(),2)=0.

  5.

  Click on Format.

  6.

  Click the Fill tab from the Format Cells dialog box.

  7.

  Select the desired shade background and then click OK, you are able to view how the background would look in the Preview window in the New Formatting Rule dialog box.

  8.

  Once satisfied, apply the formatting to the cells by clicking OK.

  References: https://support.office.com/en-us/article/Apply-shading-to-alternate-rows-in-a-worksheet-A443B0F5-202542F6-9099-5DE09C05E880