Home Microsoft Exams 77-420

Microsoft 77-420 Dumps

Exam: Excel 2013

77-420 Premium VCE File
77-420.vce - Exam-Labs Verified - Instant Download Get Latest & Verified 77-420 Exam Questions with 30-Days Free Updates 30 Questions & Answers
	$39.99

Microsoft 77-420 Exam Tutorial

---

Question No : 1

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

Show Answer

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

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

Show Answer

Answer:
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 No : 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.

Show Answer

Answer:
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 No : 4

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

Show Answer

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

---

Question No : 5

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

Show Answer

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

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

Show Answer

Answer:
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 No : 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"

Show Answer

Answer:
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 No : 8

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

Show Answer

Answer:
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 No : 9

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

Show Answer

Answer:
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 No : 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.

Show Answer

Answer:
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 No : 11

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

Show Answer

Answer:
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 No : 12

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

Show Answer

Answer:
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 No : 13

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

Show Answer

Answer:
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 No : 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.

Show Answer

Answer:
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 No : 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"

Show Answer

Answer:
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 No : 16

Change the color theme.
MathTracker.xlsx
Slipstream

Show Answer

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

---

Question No : 17

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

Show Answer

Answer:
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 No : 18

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

Show Answer

Answer:
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 No : 19

Add a worksheet
After other worksheets
Rename "Section 5"

Show Answer

Answer:
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 No : 20

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

Show Answer

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

---