Question No: 31

You work as an Office Assistant for Media Perfect Inc. You have created a report in Excel. You have inserted the following formula in a cell: VLOOKUP (12, A2:C10, 3, FALSE)

Which of the following actions will be performed by the formula?

Search for the value 12 in the first column of the range, and then returns the value that is contained in the third column of the range and on the same row as the lookup value.

Wrong syntax used.

Search the value 12 in the third column of the range and then returns the value that is contained in the 12th column of the range and on the same column as the lookup value.

Search the value 3 in the last column of the range, and then returns the value that is in the column of the range and on the same row as the lookup value.

Answer: A Explanation:

The formula will search for the value 12 in the first column of the range, and then returns the value that is contained in the third column of the range and on the same row as the lookup value.

Question No: 32

Martha works as an Office Executive for Tech Profile Inc. She uses Excel 2007 to create office reports. She often needs to fill names of all employees in each department of the company in her reports. Which of the following steps should she take to ease her task?

Fill different columns with names of employees department-wise, in an Excel worksheet. Copy and paste the columns from the worksheet into a new sheet whenever required.

Fill different columns with names of employees department-wise, in an Excel worksheet. Save the sheet as a template file.

Use this template for creating reports.

Create custom lists of employees department-wise in Excel. Use the Fill handle to fill the cell with the names of employees.

Fill different columns with names of employees department-wise, in an Excel worksheet. Save the sheet and use it as a template.

Answer: C Explanation:

Martha should take the following steps to accomplish the task: Create custom lists of employees department-wise in Excel.

Use the Fill handle to fill the cell with the names of employees.

Microsoft Excel 2007 provides a feature to create a custom list to fill series through Fill handle. Take the following steps to create a custom list for AutoFill:

Create and select the list on the Excel worksheet.

Open the Excel option through the Microsoft Office button gt; Excel Options. This will open the Excel Options window.

On the Excel Options Window, click the Edit Custom Lists button. This will open the Custom Lists dialog box.

In the dialog box, click the Import button. Click the OK button.

Answer options D, B, and C are incorrect. There is no need to take these steps. By using the custom list, Martha can easily accomplish the task.

Question No: 33 HOTSPOT

Rick works as an Office Assistant for Tech Perfect Inc. He is creating a report through Excel 2007. He wants to fill the active cell with the content of the adjacent cell. Mark the option that he will chose to accomplish the task.

Answer:

Explanation:

The Fill command is used to fill the active cell with the content of the adjacent cell. The Fill command is run through Home tab gt;

Question No: 34

You work as a Sales Manager for Tech Perfect Inc. You are creating a report for your sales team using Excel 2007. You want the report to appear in the following format:

Which of the following conditional formulas will you insert to accomplish the task?

A. =IF(E2gt;=1200,quot;Improve in Next Quarterquot;,quot;Well Donequot;)

B. =IF(E2lt;=1200,quot;Well Donequot;,quot;Improve in Next Quarterquot;)

C. =IF(E2gt;1200,quot;Improve in Next Quarterquot;,quot;Well Donequot;)

D. =IF(E2gt;=1200,quot;Well Donequot;,quot;Improve in Next Quarterquot;)

Answer: D Explanation:

In order to accomplish the task, you will have to insert the following formula in the F2 cell:

=IF(E2gt;=1200,quot;Well Donequot;,quot;Improve in Next Quarterquot;)

Answer option A is incorrect. This will display the wrong messages for the given conditions. The first expression after the logical condition is returned by the IF function when the condition is TRUE.

Answer option B is incorrect. This formula will not accomplish the task as the logical condition is not correct. The specified condition in this formula is testing for values less than or equal to 1200. Whereas, the question#39;s requirement is to evaluate values greater than or equal to 1200.

Answer option C is incorrect. This formula will not accomplish the task because of the two reasons. First, the equal sign is missing in the condition. Second, the expressions are not in the correct order.

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

Question No: 35 HOTSPOT

You work as a technician in Tech Perfect Inc. The Network Administrator of the company installs the Microsoft Excel 2007 application on the network. You open the application and see various changes in Excel 2007. You have to open an existing worksheet through the application.

Mark the option that you will click to accomplish the task.

Answer:

Explanation:

You will have to click Office Button to accomplish the task

Question No: 36

You work as an Office Assistant for Tech Perfect Inc. Your responsibility includes preparing sales reports of the company. You have created a sales report in a workbook of Excel 2007. Your workbook includes several worksheets as shown in the image given below:

You want the sheet tabs of the sheets containing charts to appear in red color. Which of the following steps will you take to accomplish the task?

Select the sheets containing charts.

Select the Colors option in the Themes group on the Page Layout page.

Select the sheets containing charts.

Select the Effects option in the Themes group on the Page Layout page.

Select the sheets containing charts.

Right-click on the sheet tab.

Choose the Tab Color option from the shortcut menu.

Select the sheets containing charts. Right-click on the sheet tab.

Choose the Sheet Color option from the shortcut menu.

Answer: C Explanation:

According to the question, you want the sheet tabs of the sheets containing chart to appear in red color. In order to accomplish the task, you will have to take the following steps: 1.Select the sheets containing charts.

Answer options A and B are incorrect. These steps will affect the sheets itself, rather than affecting the appearance of sheet tabs.

Answer option D is incorrect. There is no such option as Sheet Color available in Excel 2007.

Question No: 37

You work as a Sales Manager for Blue Well Inc. You have created a sales report in Excel 2007. You are required to publish it to your company#39;s intranet. Before publishing it to the intranet, you want to save the report in PDF format. However, when you try to use the SAVE AS option, you are unable to find the option for saving the document in PDF format.

What is the most likely cause?

The document is not protected.

The required add-ins are not installed.

Excel 2007 does not support saving documents in PDF format.

The Save as PDF option is available under the Office button gt; Publish.

Answer: B Explanation:

The most likely cause of the issue is that the required add-ins are not installed to save the document in PDF format.

Question No: 38

You work as an Office Assistant for Peach Tree Inc. You receive an Excel file that is digitally signed and a digital certificate is attached with it. When you open the Digital Signature Detail window, the following icon is displayed.

Which of the following are the possible causes of the issue?

Each correct answer represents a complete solution. Choose all that apply.

The publisher is not trusted.

The digital signature is expired.

The digital signature is invalid.

The certificate associated with the digital signature was not issued by a certificate authority

(CA).

The certificate attached is not from your domain.

Answer: A,B,C,D Explanation:

A digital signature that presents issues shows the image with a red X in the Digital Signature detail windows. This icon is displayed due to any of the following listed reasons: The digital signature is expired.

The digital signature is invalid.

The certificate associated with the digital signature was not issued by a certificate authority (CA). The publisher is not trusted.

After verifying the cause of the issue, you can take one of the following steps to resolve it: Contact the source of the signed content, and let the source know that there is a issue with the signature.

If you feel that the macro or other active content associated with the document is trustworthy, you can save the document to a trusted location. Documents in trusted locations are allowed to run without being checked by the Trust Center security system. You can explicitly trust the publisher.

Answer option E is incorrect. The certificate attached is not required to be from your domain to digitally sign a document. This will not display the error icon.

Question No: 39 HOTSPOT

You work as an Office Assistant for Rainbow Inc. You have created a workbook in Excel 2007. You have completed the data entry part on the worksheet. You want to set page breaks before printing the sheet. Choose one of the icons available in the status bar to accomplish the task.

Answer:

Explanation:

In order to accomplish the task, you will have to switch to the Page Break Preview view of the worksheet. The Page Break Preview view is used to view a preview of where pages will break when the document is printed. In this view, you can also set the breaks by dragging break lines. The status bar of Excel 2007 displays the following icons to quickly switch the views:

Question No: 40

Rick works as a Sales Manager for Tech Perfect Inc. He is creating a report in Excel 2007. He has entered data in five worksheets in a workbook. He wants to enter a disclaimer message in the A1 cell of each sheet in the workbook as shown in the image given below:

Which of the following is the easiest way to accomplish the task?

Select all the five sheets.

Write a disclaimer message in the A1 cell of the active sheet.

Write a disclaimer message in the A1 cell of the first sheet.

In the other sheets, insert a formula that points to the A1 cell of the first worksheet.

Write a disclaimer message in the A1 cell of the first sheet. Copy the disclaimer message and paste it on the remaining sheets.

Select all the five sheets. Click the first sheet.

Write a disclaimer message in the A1 cell of the active sheet.

Answer: A Explanation:

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

Select all the five sheets.

Write a disclaimer message in the A1 cell of the active sheet.

Selecting more than one sheet groups those sheets. When multiple sheets are selected, in an Excel 2007 workbook, data that a user enters or edits in the active sheet is reflected in all sheets in the group. To select all sheets, right-click a sheet tabs, and then click Select All Sheets on the shortcut menu.

Answer option D is incorrect. Clicking the first sheet after selecting all sheets will ungroup the sheets and message will only get inserted in the first sheet.

Answer options B and C are incorrect. Both these options will work but Rick will have to work more than required. Grouping sheets is a better option to work simultaneously on