77-727 Online Practice Questions and Answers

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

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

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

Correct Answer: See explanation below.

Explanation:

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

Correct Answer: See explanation below.

Explanation:

1. Click Find and Select from the Home tab then click on Go To. OR

1.

Press Ctrl+G on the keyboard.

2.

Double-click the name range you want, in this case “Oregon”.

3.

Delete the contents in the selected cells by pressing Delete on your keyboard. References:

https://support.office.com/en-us/article/Find-named-ranges-14513DF5-0D79-4CD5-9A03-968905B244E6

Correct Answer: See explanation below.

Explanation:

1. Select the plus icon at the bottom of the screen. OR

1. Select Insert from the Home tab, then click Insert Sheet. References:

https://support.office.com/en-us/article/Insert-or-delete-a-worksheet-19d3d21e-a3b3-4e13-a422d1f43f1faaf2

Correct Answer: See explanation below.

Explanation:

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-alphabeticalorder.html

Correct Answer: See explanation below.

Explanation:

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:

https://support.office.com/en-us/article/Set-page-margins-before-printing-a-worksheet-353F52FC-0D2C4958-9166-99FBD22B963A

Correct Answer: See explanation below.

Explanation:

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-812167dd25f2508f

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

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/

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

Correct Answer: See explanation below.

Explanation:

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

Correct Answer: See explanation below.

Explanation:

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-4ebe84ea-786265d41c77?redirectSourcePath=%252fen-us%252farticle%252fRemove-a-table-without-losingthe-data-or-table-format-ADF88635-90F5-4FAA-9417-19862F38CCE8andui=en-USandrs=en-USandad=US

Correct Answer: See explanation below.

Explanation:

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/