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

Select the cell J9 where you want to modify the formatting.

2.

On the Home tab in the Cells group click on Format.

3.

Specify a row height by clicking Row Height and then typing in the desired height of 2 lines in the Row Height box.

References:

https://support.office.com/en-us/article/Wrap-text-in-a-cell-2A18CFF5-CCC1-4BCE-95E4-F0D4F3FF4E84

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

Correct Answer: See explanation below.

Explanation:

1.

Click on the 3-D Pie Chart on the “Spring Sales” worksheet.

2.

This would display the Chart Tools, click on the desired style which is Style 3 Monochromatic Color 2 from the Chart Styles group situated on the Design tab.

References:

https://support.office.com/en-us/article/Change-the-layout-or-style-of-a-chart-A346E438-D22A-4540-AA87BCE9FEB719CF

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