Excel 2010 Expert v1.0
Question 1
HOTSPOT -
You work as an Office Assistant for Blue well Inc. The company has a Windows-based network. You want to create a VBA procedure for the open event of a workbook. You are required to set the security level temporarily for enabling all macros. Mark the option that you will choose first to accomplish the task.
Hot Area:
Answer : 
Question 2
HOTSPOT -
Rick works as an Office Assistant for Tech Perfect Inc. He is creating a report through Microsoft Excel 2010. He wants to fill the active cell with the content of the adjacent cell. Mark the option that he will choose to accomplish the task.
Hot Area:
Answer : 
Explanation:
Question 3
You work as an Office Assistant for Media Perfect Inc. You have created a spreadsheet in Excel 2010 and shared it with the other employees of the company. You want to select password protection and select options to prevent other employees from changing, moving, and deleting important data.
Which of the following options will you choose to accomplish the task?
- A. Mark as Final
- B. Protect Current Sheet
- C. Encrypt with Password
- D. Protect Workbook Structure
Answer : D
Explanation:
The various Protect Workbook options are as follows:
1. Mark as Final: This option is used to make the document read-only. When a spreadsheet is marked as final, various options such as typing, editing commands, and proofing marks are disabled or turned off and the spreadsheet becomes read-only. This command helps a user to communicate that he is sharing a completed version of a spreadsheet. This command also prevents reviewers or readers from making inadvertent modifications to the spreadsheet.
2. Encrypt with Password: When a user selects the Encrypt with Password option, the Encrypt Document dialog box appears. In the Password box, it is required to specify a password. Microsoft is not able to retrieve lost or forgotten passwords, so it is necessary for a user to keep a list of passwords and corresponding file names in a safe place.
3. Protect Current Sheet: This option is used to select password protection and permit or prohibit other users to select, format, insert, delete, sort, or edit areas of the spreadsheet. This option protects the worksheet and locked cells.
4. Protect Workbook Structure: This option is used to select password protection and select options to prevent users from changing, moving, and deleting important data. This feature enables a user to protect the structure of the worksheet.
5. Restrict Permission by People: This option works on the basis of Window Rights Management to restrict permissions. A user is required to use a Windows Live
ID or a Microsoft Windows account to restrict permissions. These permissions can be applied via a template that is used by the organization in which the user is working. These permissions can also be added by clicking Restrict Access.
6. Add a Digital Signature: This option is used to add a visible or invisible digital signature. It authenticates digital information such as documents, e-mail messages, and macros by using computer cryptography. These signatures are created by specifying a signature or by using an image of a signature for establishing authenticity, integrity, and non-repudiation.
Question 4
Rick works as an Office Assistant for Tech Perfect Inc. He is creating a report through Microsoft Excel 2010. Rick wants to trace the project with the actual requirements. For this purpose, he is required to compare month to month, actual v budget, quarter to quarter, year to year variances.
Which of the following will Rick use to accomplish the task?
- A. Evaluate Formula
- B. Accounting template
- C. Form control
- D. Trust Center
Answer : B
Explanation:
The accounting template is used for numbering months of a financial year to period numbering. It is used to compare month to month, actual v budget, quarter to quarter, year to year variances. It is the initial point for other reports that need the use of months.
Answer option C is incorrect. A form control is an original control that is compatible with old versions of Excel, beginning with Excel version 5. 0. It is designed for use on XLM macro sheets. It can be used when a user wants to simply interact with cell data without using VBA code and when he wants to add controls to chart sheets. By using form controls, the user can run macros. He can attach anexisting macro to a control, or write or record a new macro. These controls cannot be added to User Forms, used to control events, or modified to run Web scripts on Web pages.
Answer option A is incorrect. Evaluate Formula is the formula examination tool provided by Microsoft Excel. This tool is useful for examining formulas that do not produce any error but are not generating the expected result.
Answer option D is incorrect. Trust Center is where a user can find security and privacy settings For Microsoft Office 2010 programs.
Question 5
You work as an Office Manager for Blue Well Inc. The company has a Windows-based network. You have a formula that uses one or two variables or multiple formulas that all use one common variable. You want to examine a range of possibilities at a glance. Which of the following will you use to accomplish the task?
- A. Goal Seek
- B. Solver add-in
- C. Data table
- D. Scenario
Answer : C
Explanation:
A data table is used to see all the outcomes in one place. It is defined as a type of what-if analysis tools. It is used if a user has a formula that uses one or two variables or multiple formulas that all use one common variable. It is used to examine a range of possibilities at a glance and since the user focuses on only one or two variables, results are easy to read and share in tabular form. If automatic recalculation is enabled, it is possible to recalculate the data in data tables immediately and as a result, the user always gets fresh data.
Answer option D is incorrect. A scenario is defined as a type of what-if analysis tools. It is a set of values saved by Excel and can be substituted automatically in cells on a worksheet. A user can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. Scenario reports are not automatically recalculated. If the user changes the values of a scenario, those changes will not be displayed in an existing summary report and he must create a new summary report to show the reflection of the changed report.
Answer option A is incorrect. Goal Seek is defined as a type of what-if analysis tools. It is used if a user knows the result that he wants from a formula, but he is not sure what input value the formula needs to get that result. It works with only one variable input value.
Answer option B is incorrect. The Solver add-in is used if a user knows the result that he wants from a formula, but he is not sure what input value the formula needs to get that result. It is used for more than one input value. It works with a group of cells related to the formula in the objective cell.
Question 6
Rick works as a Sales Manager for Tech Perfect Inc. He is creating a report in Excel 2010. 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. Write a disclaimer message in the A1 cell of the first sheet. Copy the disclaimer message and paste it on the remaining sheets.
- 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. Select all the five sheets. Click the first sheet. Write a disclaimer message in the A1 cell of the active sheet.
- D. Select all the five sheets. Write a disclaimer message in the A1 cell of the active sheet.
Answer : D
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.
Grouping of sheets is a feature provided in Microsoft Office Excel 2010. Selecting more than one sheet groups those sheets. When multiple sheets are grouped, 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 tab, and then click Select All Sheets on the shortcut menu.
Users can select multiple sheets by holding the Ctrl key while clicking the sheets.
Answer option C is incorrect. Clicking the first sheet after selecting all sheets will ungroup the sheets and the message will only get inserted in the first sheet.
Answer options B and A 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.
Users can select multiple sheets by holding the Ctrl key while clicking the sheets.
Answer option C is incorrect. Clicking the first sheet after selecting all sheets will ungroup the sheets and the message will only get inserted in the first sheet.
Answer options B and A 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.
Question 7
You work as an Office Assistant for Tech Perfect Inc. You are working in the spreadsheet of the company's high selling products. You want to find out the matching values. You get an unexpected error that when you attempt to look up or match a lookup_value within an array, Excel is not able to recognize the matching value. Which of the following steps will you take to resolve the problem? Each correct answer represents a part of the solution. Choose all that apply.
- A. Remove the unseen spaces at the start or end of the first Macro cell.
- B. Delete the column having the TRIM function.
- C. Copy the contents of the new column and paste them on the top of the original column by using paste > special > values sequence.
- D. Create a new column subsequent to the one with the additional spaces in it.
- E. Use the TRIM function in the new column.
Answer : BCDE
Explanation:
In this situation, you will take the following steps:
1. Create a new column subsequent to the one with the additional spaces in it.
2. Use the TRIM function in the new column to get rid of extra spaces.
3. Copy the contents of the new column and paste them on the top of the original column by using paste > special > values sequence.
4. Delete the column having the TRIM function.
Answer option A is incorrect. This is an invalid answer option.
Question 8
You work as an Office Assistant for Tech Tree Inc. You have created a report in a workbook in
Microsoft Excel 2010. You want to export XMLdata to your Excel 2010 worksheet. On exporting the XML data, the following error message appears:
"XML MAPS IN MY WORKBOOK ARE NOT EXPORTABLE"
Which of the following are the reasons that are causing the above error? Each correct answer represents a complete solution. Choose all that apply.
- A. The maxoccurs attribute is not equal to 1.
- B. The relationship of the mapped element with other elements is not preserved.
- C. The element is a recursive structure.
- D. The XML map associated with this XML table contains one or more required elements that are not mapped to the XML table.
Answer : AB
Explanation:
The various issues with exporting XML data are as follows:
1. MAP CAN BE EXPORTED BUT SOME REQUIRED ELEMENTS ARE NOT MAPPED: This error occurs under the following situations:
The XML map associated with this XML table contains one or more required elements that are not mapped to the XML table. The element is a recursive structure.
The XML table contains a variety of mixed content.
2. EXCEL CANNOT SAVE THE WORKBOOK BECAUSE IT DOES NOT CONTAIN ANY XML MAPPINGS: Excel cannot save the user's workbook in the XML
Data file unless one or more mapped ranges are created.
3. XML MAPS IN MY WORKBOOK ARE NOT EXPORTABLE: An XML mapping cannot be exported if the relationship of the mapped element with other elements is not preserved due to the following reasons:
The mapped element's schema definition is contained within a sequence with the following attributes:
The maxoccurs attribute is not equal to 1.
The sequence has more than one direct child element defined or has an extra compositor as a direct child.
Nonrepeating sibling elements with the same repeating parent element are mapped to dissimilar XML tables.
Various repeating elements are mapped to the same XML table and the recurrence is not defined by an ancestor element.
Child elements from various parents are mapped to the same XML table.
XML schema constructs a list of lists, denormalized data, or a choice construct.
Question 9
You work as a Project Manager for Blue Well Inc. The company has a Windows-based network. You want to rename a worksheet as Important.
Which of the following steps will you take to accomplish the task?
Each correct answer represents a part of the solution. Choose all that apply.
- A. Click the Insert tab and open the name sheet.
- B. Enter the name and click the OK button.
- C. Right-click on the Sheet tab and select the Rename option.
- D. Delete the old name, type the new name, and then click OK.
Answer : BC
Question 10
HOTSPOT -
You work as a Sales Manager for Rainbow Inc. You are working on a report in a worksheet in Excel 2010. You want to insert flow-chart in the sheet. Mark the option that you will choose to insert flow- chart symbols in the report. Explanation: In order to accomplish the task, you will have to choose the Shapes option.
Shapes are simple objects provided by Microsoft Office to add them to a document. A single shape or combined multiple shapes can be inserted in the document.
The Office includes simple shapes, such as lines, basic geometric shapes, arrows, equation shapes, flowchart shapes, stars, banners, and call outs. Users can add text, bullets, numbering, and Quick Styles to these shapes after inserting them in the document. Take the following steps to add a shape in a document:
Hot Area:
Answer : 
Explanation:
Question 11
Rick works as an Office Assistant for Tech Perfect Inc. He is responsible for preparing sales reports of the company. He has created a sales report in a workbook of Excel 2010 and this workbook contains various worksheets as shown in the image given below:
Rick wants the sheet tabs of the sheets containing charts to appear in red color. Which of the following steps will Rick 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. Right-click on the sheet tabs.
- C. Select the sheets containing charts. Right-click on the sheet tabs. Choose the Sheet Color Option from the shortcut menu.
- D. Select the sheets containing charts. Select the Effects option in the Themes group on the Page Layout page.
Answer : B
Explanation:
According to the question, Rick wants the sheet tabs of the sheets containing charts to appear in red color. In order to accomplish the task, he will take the following steps:
1. Select the sheets containing charts.
2. Right-click on the sheet tabs. Choose the Tab Color option from the shortcut menu.
Answer options A and D are incorrect. These steps will not affect the appearance of the sheet tabs.
Answer option C is incorrect. This is an invalid answer option.
Question 12
You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. You want to filter data in a PivotTable report without opening drop-down lists to find the items that you want to filter. For this purpose, you are using a slicer. After filtering the data, you want to delete the slicer. Which of the following steps can you take to accomplish the task?
Each correct answer represents a complete solution. Choose all that apply.
- A. Click the slicer that you want to delete on the Options tab in the Slicer type group.
- B. Click the Macro setting, and then delete the slicer from the Macro-enabled slicers list.
- C. Click the slicer, and then press Delete.
- D. Right-click the slicer, and then click Remove <Name of slicer>.
Answer : CD
Explanation:
Take one of the following steps to delete a slicer:
1. Right-click the slicer, and then click Remove <Name of slicer>.
2. Click the slicer, and then press Delete.
Answer options B and A are incorrect. These are invalid answer options.
Question 13
You work as an Office Assistant for Tech Perfect Inc. You are working in the spreadsheet of the company's high selling products. You are trying to intersect two ranges that do not intersect each other. Which of the following errors will be occurred in this situation?
- A. #VALUE!
- B. #NAME?
- C. #NULL!
- D. #DIV/0!
Answer : C
Explanation:
The #NULL! error is produced by Excel when a user attempts to intersect two ranges that do not intersect each other. This error can be resolved by reviewing the formula and then either by changing the variables to ensure that the user is getting a valid intersection or by using the Excel Iferror function to identify a null range and take the required action.
Answer option D is incorrect. Excel #DIV/0! is produced when a formula attempts to divide by zero and a division by zero produces infinity that cannot be represented by a spreadsheet value. This error can be corrected or overcome by using the Excel IF function to identify a possible division by zero and produce an alternative result.
Answer option A is incorrect. The #VALUE! Excel formula error occurs when any of the variables in a formula is of the wrong type. To correct this error, it is required to check each individual part of the formula to make sure that the correct argument types are used. If the function contains nested functions, the best way is to copy each of the arguments into a separate cell to check what they evaluate to. If the formula contains a number of terms, break down each term further to find out its components, until the source of the error is found.
Answer option B is incorrect. The #NAME? error occurs when Excel encounters text in a formula and tries to interpret that text as a reference, a named range, or a function name but is not able to recognize the text as any of these. The best way to approach the #NAME?
Excel function error is to check the correct spelling of the function names, references, and named ranges and to check that the variables used as text values are entered in double quotes. If the formula contains nested functions, it is required to check the results of these individually, until the source of the error is identified.
Question 14
You work as a Help Desk Technician for Dreams Unlimited Inc. Martha, a Sales Manager, is creating a sales report in Microsoft Excel. 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 change. She requests that you help her out to accomplish the task. Which of the following steps would you suggest she take in order to accomplish the task?
- A. 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.
- B. Select the cell which is to be monitored. On the Formulas tab in the Formula Auditing group, click Watch Window. Click Add Watch.
- C. 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.
- D. Select the cell which is to be monitored. On the Formulas tab in the Formula Auditing group, click Evaluate Formula.
Answer : B
Explanation:
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.
Answer option D is incorrect. Evaluate Formula is the formula examination tool provided by Microsoft Excel. This tool is useful for examining formulas that do not produce an error but are not generating the expected result.
Answer options A and C 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.
Question 15
You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. You want to make a slicer available for use in another PivotTable.
Which of the following steps will you take to accomplish the task?
Each correct answer represents a part of the solution. Choose all that apply.
- A. Click the Insert Slicer arrow, and then click Slicer Connections on the Options tab in the Sort & Filter group.
- B. In the Slicer Connections dialog box, clear the check box of any PivotTable fields for which you want to disconnect a slicer.
- C. In the PivotTable Connections dialog box, select the check box of the PivotTables in which you want the slicer to be available.
- D. Click the slicer that you want to share in another PivotTable.
Answer : CD