# [Free] 2018(Mar) EnsurePass Passguide Microsoft 77-602 Dumps with VCE and PDF 21-30

Ensurepass.com : Ensure you pass the IT Exams
2018 Mar Microsoft Official New Released 77-602
http://www.EnsurePass.com/77-602.html

MOS: Using Microsoft Office Excel 2007

#### Question No: 21 HOTSPOT

You work as an office assistant for Brain Brigade Inc. You are creating an excel sheet. You use the fill handle quite often to fill or copy series of values to cells. Every time you use the fill handle, the Auto Fill button appears. You do not want to display the Auto Fill button. You

open the Excel Options window. Mark the check box that you will clear to accomplish the task.

Explanation:

In order to accomplish the task, you will clear the Show Paste Options buttons check box in the Cut, copy, and paste section of the Advanced Excel Options window.

#### Question No: 22

You work as a Sales Manager for Maini Industries. Your company manufactures and sells construction equipment. You have to create a report that will show the highest selling items of each month. The report will be in the following format:

For the third column of the sheet, you want to use formulas to fill in the values. You want the cells in the third column to get automatically filled with the names corresponding to ItemID in the second column. You want the exact values to be filled in the column. You select the C2 cell in the third column.

Which of the following formulas will you enter in order to accomplish the task?

A. =VLOOKUP(2,Item_table,B2,TRUE)

B. =VLOOKUP(2,Item_table,B2,FALSE)

C. =VLOOKUP(B2,Item_table,2,TRUE)

D. =VLOOKUP(B2,Item_table,2,FALSE)

In order to accomplish the task, you will use the following formula:

=VLOOKUP(B2,Item_table,2,FALSE)

Answer option C is incorrect. According to the question, you want the exact values in the column.

The TRUE value in the range_lookup argument provides the approximate match.

Answer options B and A are incorrect. The VLOOKUP functions used in the formulas have wrong syntaxes.

Reference: Microsoft Excel 2007 Help, Contents: quot;VLOOKUPquot;

#### Question No: 23

Alan works as a Sales Manager for MVP Technologies. He uses Microsoft Excel XP to perform official tasks. He prepares a workbook that contains names of Sales Executives and sales (in dollar) target achieved by them in two consecutive months. The first worksheet of the workbook contains data of the first month and the second worksheet contains data of the second month. The sales values are contained in cell B2 through cell B5 in the worksheets 1 and 2. He wants to find the total sales occurred in the two consecutive months. Which of the following cell references will he use to accomplish the task?

1. Relative cell reference

2. Absolute cell reference

3. Mixed cell reference

4. 3-D cell reference

Alan will use 3-D cell reference to accomplish the task.

A 3-D reference in Microsoft Excel is a reference that refers to the same cell or range of cells on two or more worksheets in a workbook. It creates link among the same cell or range of cells existing on different worksheets in a workbook to manipulate data. For example, the SUM function can be used to add the values of cell A1 existing on different worksheets in a workbook.

Take the following steps to refer to the same cell or range on multiple worksheets:

1.Click the cell where function has to be entered in a worksheet.

Answer option B is incorrect. An absolute cell reference refers to a cell in a specific location in a formula. When a formula that uses the absolute cell reference is copied from one cell to another cell, the absolute cell reference remains unchanged. The #39;\$#39; symbol is inserted before the row and the column of the cell reference to identify the absolute cell reference in a formula. For example, the formula =\$A\$1*\$A\$2 in cell A3 is written by using the absolute cell Reference. When this formula is copied from cell A3 to cell B3, the absolute cell reference remains unchanged and the formula in cell B3 also remains the same, i.e.

=\$A\$1*\$A\$2.

#### Question No: 24

You work as a Sales Manager for Rainbow Inc. Your responsibility includes preparing sales report of the Sales department. You use Excel 2007 to prepare sales reports. You have created a quarterly sales report of the department. After entering required data and inserting charts, you want to give a professional look to the document. You want to produce the document with soothing graphic effects, soft fonts, and light colors.

Which of the following steps will you take to accomplish the task with least administrative burden?

1. Use the Smart Art option.

2. Create a new workbook through a template.

Copy all data and charts of the report to the new workbook.

3. Choose one of the pre-built themes.

4. Select all the text of the document.

Change font style to Verdana and font color to light gray. Use vibrant colors for charts.

In order to accomplish the task, you will have to choose one of the pre-built themes. Document themes work on the overall design of the entire document. It enables users to specify colors, fonts, and variety of graphic effects in a document. Themes change the look and feel of the document. Excel 2007 comes with a large collection of pre-built themes.

Users can create their own themes for a document. Themes can be specified through Page Layout gt; Themes.

Answer option A is incorrect. The Smart Art option does not produce soothing graphic effects, soft fonts and light colors.

#### Question No: 25

You work as a Help Desk Technician for Dreams Unlimited Inc. Martha, a Sales Manager, is creating a sales report in Microsoft Excel 2007. The report contains many worksheets. Martha has used many formulas in her report. She wants to monitor a cell which contains a formula. The formula refers to many other cells that are located in different worksheets in

the report. She wants to know the effects on the cell when the values in different sheets changes. She requests you to help her out to accomplish the task. Which of the following steps will you advice her to accomplish the task?

1. Click the New Window option in the Window group on the View tab. Click the Arrange All option in the Window group on the View tab. Select the Cascade option.

2. Select the cell which is to be monitored.

On the Formulas tab in the Formula Auditing group, click Watch Window. Click Add Watch.

3. Click the New Window option in the Window group on the View tab. Click the Arrange All option in the Window group on the View tab. Select the Vertical option.

4. Select the cell which is to be monitored.

On the Formulas tab in the Formula Auditing group, click Evaluate Formula.

In order to accomplish the task, she should take the following steps:

Select the cell which is to be monitored.

On the Formulas tab in the Formula Auditing group, click Watch Window. Click Add Watch. What is the Watch Window?

The Watch Window is a toolbar available in Excel 2007 to monitor the value in a cell, regardless of its location in a workbook. The Watch Window can be moved or docked like other toolbars available. It keeps track of the following properties of a cell: workbook, sheet, name, cell, value, and formula. The Watch Window provides users a convenient way to inspect, audit, or confirm formula calculations and results in large worksheets/workbooks.

Only one watch per cell can be configured in the Watch Window. To set a watch, click the cell to be monitored and then, on the Formulas tab, in the

Formula Auditing group, click Watch Window. Click Add Watch to monitor the selected cell. Answer option D is incorrect. Evaluate Formula is the formula examination tool provided by Excel 2007. This tool is useful for examining formulas that do not produce an error but are not generating the expected result.

Answer options C and A are incorrect. Clicking the New Window option in the Window group on the View tab opens an instance of the workbook within the Excel window. This is useful when you work on different worksheets, and at the same time you want to navigate among them.

Reference: Windows Excel 2007 Help, Contents: quot;Watch a formula and its result by using

the Watch Windowquot;

#### Question No: 26

You work as a Sales Manager for Dreams Unlimited Inc. Your computer runs Windows Vista Home Premium. You have recently installed Microsoft Office 2007 on your computer. You use Excel to create your reports. As the interface has been changed in the new version, you often use Excel 2007 Help to find out your required commands.

Sometimes, when you look at the Ribbon, you might not see all of the icons or the text that is used in some Help topics to describe the commands on the Ribbon.

What is the most likely cause?

1. The Home Premium edition of Vista does not support some of the Office 2007 commands.

2. The Help file of the Office suite is not current.

3. You are using Beta version of Excel 2007.

4. The program window is shrinked.

This is a common phenomenon for programs in Office 2007 suite. The size of the Ribbon is optimized for a screen resolution of 1024 786 pixels when the Microsoft Office program is maximized on your screen. The Help topics were written by using that standard size for the Ribbon.

Reference. Microsoft Excel 2007 Help, Contents: quot;I can#39;t see a command on the Ribbonquot;

#### Question No: 27

You work as an Office Assistant for Tech Perfect Inc. You have created a complex workbook on Excel 2007. The workbook is a consolidated sales report that contains sales data for the last five years. This report will be circulated to all the company managers having different versions of Excel. You have to ensure the following:

The optimal performances of the workbook backward compatibility with earlier versions of Excel which of the following steps will you take to accomplish the task? Each correct answer represents a part of the solution. Choose two.

1. Use the Run Compatibility Checker option through the Office button gt; Prepare.

2. Save the file in the Excel 2007 Binary file format.

3. Use the Inspect Document option through the Office button gt; Prepare.

4. Save the file in the Excel 2007 XML-based file format.

In order to accomplish the task, you will have to take the following steps:

Use the Run Compatibility Checker option through the Office button gt; Prepare. This option will run Compatibility Checker, which will show you a report regarding compatibility of the document with earlier versions of Excel.

#### Question No: 28

You work as an Office Assistant for Dreams Unlimited Inc. You use Excel 2007 for creating various types of reports. You have created a report in the format given below:

In the A7 cell, you are required to put a formula so that it can fulfill the description provided in the B7 cell. Which of the following formulas will provide the required result?

1. COUNTIF(B2:B5,quot;=Yesquot;,C2:C5,quot;=Yesquot;)

2. COUNTIFS(B2:B5,quot;=Yesquot;,C2:C5,quot;=Yesquot;)

3. COUNTIF(B2:C5,quot;=Yesquot;)

4. COUNTIFS(B2:C5,quot;=Yesquot;)

In order to get the required result, you will have to insert the following formula in the B7 cell: COUNTIFS(B2:B5,quot;=Yesquot;,C2:C5,quot;=Yesquot;)

Only Sarah and David have exceeded their January and February quotas, therefore the formula will provide 2 as the result.

Answer option A is incorrect. The COUNTIF function of Excel 2007 does not support multiple criteria.

Answer options C and D are incorrect. This formula will count all cells that have the value quot;Yesquot; in the range B2:C5. As multiple criteria are not applied in the formula, it will provide 6 as the result.

Reference. Microsoft Excel 2007 Help, Contents: quot;COUNTIFquot;

#### Question No: 29

You work as an Office Assistant for Peach Tree Inc. Your responsibilities include creating sales reports of the company. You have created a report in a workbook in Excel 2007. One

of the worksheets in the report contains some formulas. In order to finally verify formulas used in the worksheet, you have displayed the formulas as shown in the image given below:

You select the E3 cell and want to know the current value of the formula in the cell. You do not want to hide formulas in the sheet. Which of the following steps will you take to accomplish the task with least administrative burden?

1. Click the Evaluate Formula option in the Formula Auditing group on the Formulas tab. Click the Evaluate button.

2. Click the Calculate Now option in the Calculation group on the Formulas tab.

3. Click the Watch Window option in the Formula Auditing group on the Formulas tab. Click the Add Watch option and select the E3 cell.

4. Watch the value displayed at the status bar.

According to the image displayed in the question, formulas are displayed instead of values in cells. You are required to find the result of a formula in the E3 cell that you have already selected. In order to accomplish the task with minimum administrative burden, you will have to take the following steps:

Click the Evaluate Formula option in the Formula Auditing group on the Formulas tab. This will open the Evaluate Formula dialog box.

Answer option C is incorrect. Although these steps will also accomplish the task, the administrative burden will be more.

Answer option D is incorrect. The Status bar does not evaluate the formula.

Answer option B is incorrect. The Calculate Now option is used to calculate the entire worksheet at the current point of time. It is very useful for calculating values when automatic calculation is turned off. This step will not accomplish the task, as it displays the calculated value in their respective places when the Show Formula option is disabled.

Reference. Microsoft Excel 2007 Help, Contents: quot;Evaluate a nested formula one step at a timequot;

#### Question No: 30

You work as an Office Assistant for Blue Well Inc. You have created a workbook in Excel 2007. This workbook will be shared with other users who will collaborate to enter data in the workbook. You want to enable Excel in such a manner that if you have a question regarding the editing done by any collaborator, you can identify who made the changes and verify that it is correct. Which of the following steps will you take to accomplish the task?

Each correct answer represents a complete solution. Choose two.

1. Enable the Show Comments feature.

2. Turn on the Change Tracking feature.

3. Share the workbook.

4. Add a History worksheet to the workbook.