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

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.

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 “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. 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-cfaa4332-ad02-6e148e01aa0a

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.

Select the data you would like to use, in this case it would be the data in the “Products” and “Total” columns from the “Summer Sales” worksheet.

2.

Click on Insert Pie Chart situation on the Insert tab then pick the 3-D Pie chart as desired.

3.

Format the chart as desired by using Chart Elements, the Chart Styles, or the Chart Filters.

4.

Drag the Pie Chart to the desired location which is to the right of the column charts.

References:

https://support.office.com/en-us/article/Add-a-pie-chart-1A5F08AE-BA40-46F2-9ED0-FF84873B7863