Microsoft Microsoft Certifications 77-420 Questions & Answers

• Question 11:

  Insert a picture. To the right of the data File source Books.jpg

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Click in a cell to the right of the data.

  Step 2:Click the INSERT tab, and then the Pictures command.

  

  Step 3:From the Insert Picture dialog box select the Books.jpg file.

• Question 12:

  Create a chart. To the right of the data Chart 3-D Clustered Column Exclude all filtered rows Horizontal Axis Labels: "IDs" column in table Series 1: "Zero Scores" column in table.

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Click in a cellin the data table, and then click on the INSERT tab.

  

  Step 2:Click Insert Column Chart, and select 3-D Clustered Column.

  

  Step 3:If necessary move the chart to the right of the table. Step 4:Click the Select Data button.

  

  Step 5:In the Select Data Source dialog box click the Switch Row/Column button, Deselect everything except IDs for Horizontal Axis Labels, deselect everything excepts Series1 for Legend Entries (series), and click OK.

  

• Question 13:

  Insert the instructor's name for column B.

  Cell B5.

  Use Function RIGHT

  Text: B2

  Absolute reference

  Num_chars: "16"

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Open the correct worksheet (Summary Worksheet).

  Step 2:Click in cell B5.

  Step 3:Click on the insert function button.

  

  Step 4:In the Insert Function Dialog box select the Category Text, scroll down and click on the function RIGHT, and click OK.

  

  Step 5:In the Function Arguments Dialog box in the Text box type B2, in the Num_chars type 16, and click OK.

  

• Question 14:

  Add conditional formatting.

  Color Scales: Green –White-Red Color Scale

  Midpoint: Percentile, "70"

  Maximum: Number, "25"

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Click cell C3

  Step 2:Shift-Click cell S25.

  Step 3:On the Home tab, under Format, choose Conditional Formatting, and choose New Rule...

  

  Step 4:In the New Formatting Rule dialog box set Format Style to: 3-Color Scale, and set Minimum Color to Green. Step 5:In the same dialog box set Midpoint type to Percentile, set Midpoint Value to 70, and set Midpoint Color to White. Also set Maximum Type to Number, Maximum value to 25, and Maximum Color to Red. Finally click OK.

  

  

• Question 15:

  Apply a cell style Cell range A2:S2 Style 40% - Accent3

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1: Open the correct worksheet(Section 3 Worksheet).

  Step 2: Click in cell A2.

  Step 3: Press down the Shift key and click in cell S2.

  Step 4:On the Home tab, under Format, scroll down until you see 40% . Accent3, and click on it.

  

• Question 16:

  Formula. Find the average of each student's homework scores.

  Cell range C7:C29

  Use Function AVERAGE

  Number 1: all homework for each student on "Section 3" worksheet "22-Aug 12-Dec"

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Click cell C7, and the click the Insert Function Button.

  

  Step 2:In the Insert Function dialog box select Category Statistical, select function AVERAGE, and click OK.

  

  The function Arguments Dialog box appears: Step 3:Click on the Section 3 tab (left bottom part of the window).

  

  

  Step 4:Locate the data for the first student. This well be cell range C2 to N2. Click C2 and then Shift-click cell N2. Then click the OK button.

  

  Step 5:Copy cell C7 downwards until cell C29.

  

  Result will be like:

  

• Question 17:

  Add a worksheet After other worksheets Rename "Section 5"

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Click on the New sheet button (in the left bottom corner).

  

  Step 2:Right-click on the new worksheet, and click Rename from the context menu.

  

  Step 3:Type: Section 5 Step 4:Press the enter key.

• Question 18:

  Formula Find the average score of all student ids without a zero homework score. Cell L2 Use Function AVERAGEIF Range F7:F29 Criteria: "0" Average_range: "Homework"

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Click cell L2, and the click the Insert Function Button.

  

  Step 2: In the Insert Function dialog box select Category Statistical, select function AVERAGEIF, and click OK.

  

  Step 3:In the function Arguments dialog box click the reference button.

  

  Step 4:Click cell F7, shift-click cell F29, and press the enter key. Step 5:In the Function Arguments dialog box in the Criteria box type: 0, in the Average_range box type: Homework, and click the OK button.

  

  

• Question 19:

  Sort and Filter. Apply a sort and a filter to the table. Cell range B6:F29 Sort Column Zero Scores Order Largest to Smallest Column IDs Order Smallest to Largest Filter Hide students ids with no zero scores.

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Click a cell in the table.

  Step 2:On the Home tab select the Sort and Filter button, and select Custom sort (needed to sort on more than one column at a time).

  

  Step 3:In the Sort dialog box select Sort by: Zero Scores, Order: Largest to Smallest, and click the Add Level button.

  

  Step 4:Select then by: Ids, Order: Smallest to Largest, and click the OK button. Step 5:Click the Zero Scores column header header arrow, select Number Filters, and clickDoes not Equal.

  

  

  Step 6: In the Custom AutoFilter enter 0, and click OK

  

  The result will look like:

  

• Question 20:

  Configure page layout options.

  Cell range B3:S25.

  Set Print Area.

  Enable the Gridlines Print option.

  Correct Answer: Use the following steps to complete this task in explanation:

  Step 1:Click in cell B3, and then shift-click in cell S25.

  Step 2:On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.

  

  Step 3:Still in the Page Layout tab, Enable the Gridlines Print option.