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

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.

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

Correct Answer: See explanation below.

Explanation:

1.

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

Correct Answer: See explanation below.

Explanation:

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-ecef4808-b90f-fdb42f892e90

Correct Answer: See explanation below.

Explanation:

1.

In cell J10 on the “Car Inventory” worksheet enter “=G10 *.95” OR “=G10 * 95%”.

2.

Select cell J10, hold the Shift key on your keyboard, and use the down arrow key until all the cells the formula is required to be applied (J40).

3.

Hold down the Ctrl key on your keyboard and press D which would “copy down” the formula for the rows below.

OR

1.

In cell J10 on the “Car Inventory” worksheet enter “=G10 *.95” OR “=G10 * 95%”.

2.

Select cell J10, then Right-click and select Copy, OR click Copy from the Clipboard group situated on the Home tab.

3.

Paste the formula by selecting Paste from the Clipboard group situated on the Home tab, OR Right-click then select Paste, OR click Paste from the Clipboard group situated on the Home tab, then click Paste Special and select Formulas, OR you could select Values for the formula results instead.

4.

Verify that the cell references in the formula are as desired.

References:

https://www.excelforum.com/excel-general/745891-calculate-discount-for-full-column.html

https://support.office.com/en-us/article/move-or-copy-a-formula-1f5cf825-9b07-41b1-8719-bf88b07450c6

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

Correct Answer: See explanation below.

Explanation:

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-forMac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede