Microsoft 77-420 Exam Dumps, Microsoft 77-420 Practice Test Questions

Question #1

Insert the instructor's name for column B.
Cell B5.
Use Function RIGHT
Text: B2
Absolute reference
Num_chars: "16"

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

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

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

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.

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

b
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 #4

Rename the title of the chart.
Chart
Text "All Zero Scores"

Explanation:
Step 1:Click on the Chart.
Step 2:Click on the Chart title.
Step 3:Changeby typingthe title to:All Zero Scores

Question #5

Modify the document property.
MathTracker.xlsx
Subject "Homework"

Explanation:
Step 1:Click the File menu.

Step 2:Select Info (if necessary) and click Show All Properties,

Step 3:In the Subject textbox type: HomeWork and click theReturn button.

Question #6

Create a hyperlink to another worksheet.
Cell A2.
Cell reference "A3"
Sheet reference "Section 3" worksheet.

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.

Question #7

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"

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

Create a new named range.
Cell range C7:C29.
Name: "Homework"

Explanation:
Step 1:Click cell C7, and then Shift-click cell C29
Step 2:Click the Name box at the left end of the formula bar.
Step 3:Type: Homework. Press ENTER.

Question #9

Configure page layout options.
Cell range B3:S25.
Set Print Area.
Enable the Gridlines Print option.

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.

Question #10

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.

Explanation:
Step 1:Click a cell in the table.
Step 2:On the Home tab select the Sort & 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 #11

Modify the cell format to date.
Cell range C2:S2
Type: 14-Mar
Locale (location): English (United States)

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, choose Format Cells.

Step 5: In the Format Cells dialog box, choose Date, 14-Mar, and Locale (location): English
(United States). Click OK.

Question #12

Crop the picture.
Books.jpg
Crop to Shape Snip Same Side Corner Rectangle.

Explanation:
Step 1:Click on the picture.
Step 2:In the Format menu (which is displayedautomatically) click the Crop Command,
select Crop to Shape, and click Snip Same Side Corner Rectangle.

Question #13

Insert data from a text file.
Cell A1.
File source Autumn.txt
Tab-delimited

Explanation:
Step 1:Click cell A1.
Step 2:Click the Data tab, and click the From Text button.

The Text Import Wizard starts.
Step 3:Make sure Delimited is select, and click Next.

Step 4:Make sure Delimiters: Tab is selected. Then click Next.

Step 5:Finish the wizard by click the Finish Button.
Step 6: Confirm with OK in the Import Data dialog box.

Question #14

Formula.
Find themaximum homework score for each student.
Cell rangeE7:E29
Use Function MAX
Number 1: maximum homework score for each student on "Section 3" worksheet.

Explanation:
Step 1: Click cellE7, and the click the Insert Function Button.

Step 2: In the Insert Function dialog box select Category Statistical, select function MAX,
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 cellE7 downwards until cellE29.

Result will be like:

Question #15

Add a header and the date for each of the columns (assignments) in the range.
Cell B2.
Text "Date".
Cell Range C2: S2
Text: "22-Aug, 29-Aug,12-Dec"

Explanation:
Step 1:Click Cell B2. Type the text: Date
Step 2: Click cell C2. Type the text: 22-Aug
Step 3: Click cell D2. Type the text: 29-Aug
Step 3:Click cell C2, then shift-click cell D2.

Step 4:Copy until cell S2(by dragging from cell D2 to cell S2).

Question #16

Change the color theme.
MathTracker.xlsx
Slipstream

Explanation:
Step 1:Click the PAGE LAYOUT tab, click Colors, scroll down and select Slipstream.

Question #17

Rename a table.
Cell range B6:F29
Name: "Overview"

Explanation:
Step 1:Click cell B6, and shift-click cell F29.
Step 2: Click the Name box at the left end of the formula bar.
Step 3: Type: Overview. Press ENTER.

Question #18

Add conditional formatting.
Color Scales: Green White-Red Color Scale
Midpoint: Percentile, "70"
Maximum: Number, "25"

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

Add a worksheet
After other worksheets
Rename "Section 5"

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

Modify the cell alignment settings.
Cell range B3:B25
Horizontal: Right (Indent)

Explanation:
Step 1:Click cell B3.
Step 2:Shift-click cell B25
Step 3:Right-click somewhere in the B3-B25 cell range, and choose Format Cells from the
context menu.

Step 4:In the Format Cells dialog box click the Alignment Tab, change Horizontal: to Right
(Indent), change Indent: to 1, and then click the OK button.

Question #21

Create a table and modify the table styles.
Cell range B6:F29
Table Style Medium 4
Enable the First Column Style

Explanation:
Step 1:Click cell B6, and Shift-click cell F29.
Step 2:Click theInserttab, and click the Table button.

Step 3:In the Create Table dialog box click OK.

Step 4:In the Design tab, Table Styles select Table Style Medium 4.

Step 5:In the Design tab enable First Column.

Question #22

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

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

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"

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

Apply a style to the chart.
Chart.
Chart Styles Style 7

Explanation:
Step 1:Click on the chart.
Step 2:On the Design tab, in the Chart Styles group, click theStyle 7chart style.

Question #25

Insert a row.
Directly below current row 1.

Explanation:
Step 1: Open the correct worksheet (Section 3 Worksheet)
Step 2: Click on a cell in row 2.
Step 3: Right-click in the cell, and select Insert from the context menu.
Step 4: In the Insert Dialog box select Entire row, and click OK.

Question #26

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

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

Formula.
Find the minimum homework score for each student.
Cell range D7:D29
Number 1: minimum homework score for each student on "Section 3" worksheet.

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

Step 2: In the Insert Function dialog box select Category Statistical, select function MAX,
and click OK.
Formula.
Find the minimum homework score for each student.
Answer:
Step 1: Click cell D7, and the click the Insert Function Button.

Step 2: In the Insert Function dialog box select Category Statistical, select function MIN,
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 D7 downwards until cell D29.

Result will be like:

Question #28

Hide columns.
Column A.
Columns O through R.

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

Modify the text in the title.
Cell A1.
Text "Math 1080 - Section 3 Assignments"

Question #30

Formula.
Insert text using a formula
Cell K2
Use Function CONCATENATE
Text1 : "Students with no "
Text 2: header of column F
Absolute Reference

Explanation:
Step 1:Click K2andClick on the insert function button.
Step 2:Select Category: Text, the CONCATENATE function, and click OK.

Step 3:In the Function Arguments dialog box in the Text1 box type: Students with no , and
then click the Text2 reference button, and click on the header of column F in the worksheet,
press the enter Key

Step 4:Press the OK button in the Function Arguments dialog box.