MOS: Using Microsoft Office Excel 2007 v6.0
Question 1
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?
- A. 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.
- B. Wrong syntax used.
- C. 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.
- D. 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 2
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?
- A. 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.
- B. 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.
- C. Create custom lists of employees department-wise in Excel. Use the Fill handle to fill the cell with the names of employees.
- D. 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:
1.Create and select the list on the Excel worksheet.
2.Open the Excel option through the Microsoft Office button > Excel Options. This will open the Excel Options window.
3.On the Excel Options Window, click the Edit Custom Lists button. This will open the
Custom Lists dialog box.
4.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.
Reference. Windows Excel Help, Contents: "Fill data by using a custom fill series"
Question 3
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 >
Editing group > Fill.
Click the Down, Right, Up, Left, or Series option to fill cells.
Reference. Windows Help, Contents: "Fill data automatically in worksheet cells"
Question 4
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(E2>=1200,"Improve in Next Quarter","Well Done")
- B. =IF(E2<=1200,"Well Done","Improve in Next Quarter")
- C. =IF(E2>1200,"Improve in Next Quarter","Well Done")
- D. =IF(E2>=1200,"Well Done","Improve in Next Quarter")
Answer : D
Explanation:
In order to accomplish the task, you will have to insert the following formula in the F2 cell:
=IF(E2>=1200,"Well Done","Improve in Next Quarter")
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'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: "IF"
Question 5
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 6
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?
- A. Select the sheets containing charts. Select the Colors option in the Themes group on the Page Layout page.
- B. Select the sheets containing charts. Select the Effects option in the Themes group on the Page Layout page.
- C. Select the sheets containing charts. Right-click on the sheet tab. Choose the Tab Color option from the shortcut menu.
- D. 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 7
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'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?
- A. The document is not protected.
- B. The required add-ins are not installed.
- C. Excel 2007 does not support saving documents in PDF format.
- D. The Save as PDF option is available under the Office button > 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 8
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.
- A. The publisher is not trusted.
- B. The digital signature is expired.
- C. The digital signature is invalid.
- D. The certificate associated with the digital signature was not issued by a certificate authority (CA).
- E. 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.
Reference. Microsoft Excel 2007 Help, Contents: "How to tell if a digital signature is trustworthy"
Question 9
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
Question 10
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?
- A. Select all the five sheets. Write a disclaimer message in the A1 cell of the active sheet.
- B. 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.
- C. Write a disclaimer message in the A1 cell of the first sheet. Copy the disclaimer message and paste it on the remaining sheets.
- D. 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 multiple sheets.
Reference: Microsoft Excel 2007 Help, Contents: " Select one or multiple worksheets"
Question 11
You work as a Director for Tech Perfect Inc. The company has several branch offices located throughout the United States. Its network spans the whole country. You are creating a sales report in Excel 2007 that will be distributed to all sales managers of the company. However, you are unaware about the versions of Excel these sales managers are using. You want to ensure that your report is visible to all the sales managers irrespective of which version they are using. You also want to save the workbook in the default format and, before saving the document, check the features you have used that are not supported by earlier versions.
Which of the following steps will you take to accomplish the task?
- A. Use the Inspect Document option through the Office button > Prepare.
- B. Use the Mark as Final option through the Office button > Prepare.
- C. Save the report as Excel 97-2003 workbook.
- D. Use the Run Compatibility Checker option through the Office button > Prepare.
Answer : D
Explanation:
In order to accomplish the task, you will have to use the Run Compatibility Checker option through the Office button > Prepare. This option will run Compatibility Checker that will show you a report regarding the compatibility of the document with earlier versions of
Excel.
Question 12
You work as a Sales Manager for Tech Perfect Inc. You have created a report in a workbook in Excel 2007. The report contains lots of headings and titles. You have formatted cells through styles. You delete a heading from a cell. However, you see that the formatting of the cell is not deleted. You want to remove the formatting from the cell. Which of the following steps will you take to accomplish the task with least administrative burden?
- A. Select the cell. Press the Delete button on the keyboard.
- B. Select the cell. Click the Clear option in the Editing group on the Home tab. Choose the Clear Format from the sub-menu.
- C. Select the row containing the cell. Right-click on the row header. Select the Cut option from the shortcut menu.
- D. Select the row containing the cell. Right-click on the row header. Select the Delete option from the shortcut menu.
Answer : B
Explanation:
According to the question, you have deleted the contents of the cell but the format of the cell is still there. In Excel, deleting a cell's contents does not delete the cell's formatting. In order to accomplish the task, you will have to take the following steps:
Select the cell.
Answer option A is incorrect. These steps will delete the contents of a cell.
Answer option D is incorrect. These steps will delete the entire row. It will increase the administrative burden of verifying that all the cells in the row are empty.
Answer option C is incorrect. This option will not work as you have to paste also to move a row from its existing place.
Question 13
You work as an Office Assistant for Tech Perfect Inc. You have converted a data range into a data table as shown in the image below:
You want to add a total row in the table to calculate the total. The total row will include the sum of the Total First Quarter column. You want to perform this in the easiest and best possible way. Which of the following steps will you take to accomplish the task?
- A. Click the E9 cell. Select the Auto Sum option in the Function Library group of the Formulas tab.
- B. Click the E9 cell. Insert the following formula- =E4+E5+E6+E7+E8.
- C. Select a cell in the table. Select the Total Row option in the Table Style Options group of the Design tab.
- D. Select the data range from E4:E9. Select the Auto Sum option in the Function Library group of the Formulas tab.
Answer : C
Explanation:
The question clearly states that you have to add a total row in the table to display the sum of the Total First Quarter column. In order to accomplish the task, you will take the following steps:
Select a cell in the table.
Select the Total Row option in the Table Style Options group of the Design tab.
The total row will be added to the table.
Answer options A, D, and B are incorrect. Although all of these options will calculate the total in the E9 cell, the row will not be added to the table. Hence, if the table is extended, the E9 cell will not change accordingly.
Reference. Microsoft Excel 2007 Help, Contents: "Format an Excel table"
Question 14
You work as a Sales Manager for Blue Well Inc. You are working on Excel 2007 for creating a sales report. The workbook on which you are working contains four worksheets.
You are working on multiple sheets at a time. You are required to copy cells from a worksheet and paste it to another worksheet. For this, you select the cells and copy them by using the shortcut keys Ctrl + C.
Thereafter, you select the sheet where these cells have to be pasted. When you try to paste those cells by using the Ctrl + V keys, Excel displays the following error message.
You ensure that only one cell is selected and try to paste the cells. The same message is again displayed. What is the most likely cause?
- A. The cell in which you are trying to paste the contents is not empty.
- B. While copying cells, multiple sheets are selected.
- C. Ctrl + C and Ctrl + V shortcut combinations does not work in Excel 2007.
- D. While pasting the content, multiple cells are selected.
Answer : B
Explanation:
The most likely cause of the issue is that while copying cells you have selected multiple sheets. When multiple sheets are selected, in an Excel
2007 workbook, the selected sheets are grouped and data that a user enters or edits in the active sheet is reflected in all sheets in the group. When a user copies or cuts data in grouped sheets, it cannot be pasted on a separate sheet, because the size of the copy area includes all layers of the selected sheets and is therefore different from the paste area in a single sheet. In order to resolve the issue, make sure that only one sheet is selected before you copy or move data to another worksheet.
Answer option C is incorrect. Both the shortcut keys work in Excel 2007.
Answer option D is incorrect. The question clearly states that you ensure that only one cell is selected.
Answer option A is incorrect. It is not required to paste contents to an empty cell. You can paste the contents to any cell. However, the pasted information will overwrite the existing contents.
Reference. Microsoft Excel 2007 Help, Contents: " Select one or multiple worksheets"
Question 15
Jack wants the rows containing the name Tom and product Jam. There are thousands of entries in the log. Plowing through all the records will be time taking. Which option can be used to view only the relevant records?
- A. Sort
- B. All filter
- C. Find
- D. Autofilter
Answer : D
Explanation:
Autofilter is used to filter the record as per the condition by hiding the rest. Defining different filters, you can compare data in multiple ways, so you can glean vital information without wasting vital time.
Answer options C and B are incorrect. There are no such options defined in Excel to filter the records. Answer options A is incorrect. The Sort function is used to arrange the records in ascending or descending order.