77-727 Online Practice Questions and Answers

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

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 cell A5 of the “$5,000 Donors” worksheet where you want the source file to be imported to.

2.

Click on File, then select Open.

3.

Select Text Files from the Open dialog box.

4.

Locate the contributors.txt file in the Documents folder, then double-click it.

5.

Click Finish to complete the operation.

References:

https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b339e391393ba

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 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. In cell C8 on the “Summer Bookings” worksheet, insert the following CONCAT function: “=CONCAT(E6 “, “D6)” OR “=E6and “, “, D6”.

References:

https://support.office.com/en-us/article/Combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce7842ed-b3c3-21340eb164a6