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 “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 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-96c3443b-8e9f-87f16c48462c

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.

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

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.

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.

On the “Adult Program” worksheet, select the area you want the sparkline to be inserted.

2.

Click Column in the Sparklines group from the Insert tab.

3.

Enter the range of cells in the Data Range box, in this case A10:A19.

OR

3.

Select the range of cells by temporarily collapsing the dialog box and manually selecting the cells.

4.

Click OK and the Sparkline Tools will appear, the Design tab can be used to customize the sparklines.

References:

https://support.office.com/en-us/article/analyze-trends-in-data-using-sparklines-be6579cf-a8e3-471a-a459873614413ce1

Correct Answer: See explanation below.

Explanation:

1.

On the “Youth Program” worksheet, select Format as Table from the Home tab.

2.

Choose the desired style for the table.

3.

Select the desired cell range of A9:G19 in the Format as Table dialog box.

4.

Mark row 9 as a header.

5.

Click OK.

References:

https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664

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.

Select the “Top Sellers” column chart.

2.

Click on the particular data point you want to label, click Add Chart Element +, from the upper right corner next to the chart, then select Data Labels.

3.

Then click Format Data Labels.

4.

The Data Label Range dialog box would appear, select the data range which are the cell values you want to appear in the labels, in this case it would be the “Total” colunn for “Fruits”, “Vegetables”, and “Jams and Honey”, then click OK.

References:

https://support.office.com/en-us/article/add-or-remove-data-labels-in-a-chart-884bf2f1-2e29-454e-8b42f467c9f4eb2d