Microsoft 77-420 Online Practice Questions and Exam Preparation

Microsoft 77-420 Online Questions & Answers

• Question 1:

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

  Modify the text in the title.

  Cell A1.

  Text "Math 1080 - Section 3 Assignments"

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

  ---

  Step 1:Click cell A1 Step 2:Change the text by typing to: Math 1080 - Section 3 Assignments

• Question 3:

  Hide columns.

  Column A.

  Columns O through R.

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

  ---

  Step 1:Right-click on the A column header, and select Hide from the context menu.

  

  Step 2:Click on the O columns header, and then Shift-click on the R Column Header.

  Step 3:Right-click in one of the cells in the high-lighted area, and select Hide from the Context menu.

  

• Question 4:

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

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

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

  Formula.

  Count the number of 0 homework scores for each student.

  Cell range F7:F29

  Use function COUNTIF

  Range: all possible homework scores for each student on "Section 3" worksheet.

  Criteria: 0

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

  ---

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

  

  Step 2: In the Insert Function dialog box select Category Statistical, select function COUNTIF, 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.Press Enter.

  

  Step 5:In the Function Arguments Dialog box, in the Criteria field type: 0. Then click the OK button.

  

  Step6: Copy cellF7 downwards until cellF29.

  

  Result will be like:

  

• Question 8:

  Rename the title of the chart.

  Chart

  Text "All Zero Scores"

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

  ---

  Step 1:Click on the Chart.

  Step 2:Click on the Chart title.

  Step 3:Changeby typingthe title to:All Zero Scores

  

• Question 9:

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

  Create a hyperlink to another worksheet.

  Cell A2.

  Cell reference "A3"

  Sheet reference "Section 3" worksheet.

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

  ---

  Step 1:Click cell A2.

  Step 2:On the Insert tab, in the Links group, click Hyperlink.

  

  Step 3:In the Insert Hyperlink dialog box, in the Type the cell reference: type A3, select Cell Reference 'Section 3', and click OK.