Microsoft 77-420

Excel 2013

(Page 1 out of 2)
Showing 15 of 30 Questions
Exam Version: 6.0
Question No : 1 -

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

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

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.

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"

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"

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.

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"

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"

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.

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.

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)

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.

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

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.

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"

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



(Page 1 out of 2)
Showing of 30 Questions
Exam Version: 6.0