Microsoft 77-727 Online Practice Questions and Exam Preparation

Microsoft 77-727 Online Questions & Answers

• Question 21:

  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.

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

  ---

  1.

  On the “Car Inventory” worksheet, click Print Titles from the Page Setup group, situated on the Page Layout 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-D3550133-F6A1-4C72-AD70-5309A2E8FE8C

• Question 22:

  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 “Spring Sales” worksheet, add the title “New Products” to the column chart. Label the vertical axis “Gross Sales” and the horizontal axis “Months”.

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

  ---

  1.

  Click on the title on the “Spring Sales” worksheet, then type “New Products”.

  2.

  You can edit the Font of the title from the mini-toolbar that appears OR from the Home tab.

  3.

  Click directly on either the x-axis or y-axis in the chart OR click the Chart Elements button.

  4.

  For editing the x-axis which is the horizontal category then type “Months”.

  5.

  Do the same to edit the y-axis which is the vertical category and type “Gross Sales”.

  6.

  You can further edit the text of the data in either of the axis as desired.

  References: https://www.dummies.com/software/microsoft-office/excel/how-to-edit-chart-titles-in-excel-2016/

• Question 23:

  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.

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

  ---

  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-B28E-66A552DC138D

• Question 24:

  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.

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

  ---

  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-99C2-2F971C0E2370

• Question 25:

  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.

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

  ---

  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-96c3-443b-8e9f-87f16c48462c

• Question 26:

  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 “Spring Sales” worksheet, apply Style 3 and Monochromatic Color 2 (Monochromatic Palette 2) to the 3-D Pie chart.

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

  ---

  1.

  Click on the 3-D Pie Chart on the “Spring Sales” worksheet.

  2.

  This would display the Chart Tools, click on the desired style which is Style 3 Monochromatic Color 2 from the Chart Styles group situated on the Design tab.

  References: https://support.office.com/en-us/article/Change-the-layout-or-style-of-a-chart-A346E438-D22A-4540-AA87-BCE9FEB719CF

• Question 27:

  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.

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

  ---

  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-2025-42F6-9099-5DE09C05E880

• Question 28:

  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.

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

  ---

  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-8121-67dd25f2508f

• Question 29:

  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 M9 on the “Summer Bookings” worksheet, insert a function that calculates the number of 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 M9, on the “Summer Bookings” worksheet, insert the following COUNTIF formula: “=COUNTIF(G6:G24, >= 12”)” References: https://support.office.com/en-us/article/count-numbers-greater-than-or-less-than-a-number-453b0ccc-cfaa-4332-ad02-6e148e01aa0a

• Question 30:

  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, switch the rows with the columns on the first column chart.

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

  ---

  1.

  Select the first row OR column on the “Summer Sales” worksheet and Copy either from the Home tab OR by pressing Ctrl+C on the keyboard.

  2.

  Select the first cell where you want to paste the data, then click on the arrow next to Paste situated on the Home tab, then select Transpose.

  3.

  Pick a spot with enough space to paste your data, the copied data will overwrite the already existing data that is already there.

  4.

  Once completed, you can delete the original data if desired.

  References: https://support.office.com/en-us/article/Transpose-data-from-rows-to-columns-or-vice-versa-in-Excel-for-Mac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede