77-888 Microsoft Excel 2010 Expert Questions and Answers

A macro is defined as a tool that permits a user to automate tasks and add several functionalities

to forms, reports, and controls. For example, if a user adds a command button to a form, he

associates the button's OnClick event to a macro, and the macro consists of the commands that are to be performed by the button each time it is clicked.

The macro records the user's mouse clicks and keystrokes while he works and lets him play them back later. The macro can be used to record the sequence of commands that the user uses to perform a certain task. When the user runs the macro, it plays those exact commands back in the same order.

Answer option B is incorrect. Evaluate Formula is a 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 options A and C are incorrect. The benefit of connecting to external data from Microsoft

Excel is that a user can automatically update Excel workbooks from the real data source wheneverthe data source is updated with new information. It is possible that the external data connection might be disabled on the computer. For connecting to the data source whenever a workbook is opened, it is required to enable data connections by using the Trust Center bar or by putting the workbook in a trusted location.

What-if analysis is defined as the process of changing values in cells to observe the way in which

those changes are affecting the outcome of formulas in the worksheet. There are three types of

what-if analysis tools that are named as scenarios, data tables, and Goal Seek. A user can also

install the Solver add-in to accommodate more variables than Goal Seek.

Answeroption A is incorrect.Aformcontrolis an original controlthat iscompatiblewith

old versions of Excel, beginning with Excel version5.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 an existing macro to a control, or write or record a new macro. These controls cannot be added to UserForms, used to control events, or modified to run Web scripts on Web pages.

Answer option C is incorrect. 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 useof months. Answer option B is incorrect. Trust Center is where a user can find security and privacy settings for Microsoft Office 2010 programs.

The PivotChart report is used to provide a graphical representation of data in a PivotTable report in an interactive way. Whenever a user creates the PivotChart report, PivotChart report filters are displayed in the chart area. These are used for sorting and filtering the underlying data of the PivotChart report. Changes made to the layout and data in the associated PivotTable report are immediately reflected in the layout and data in the PivotChart report. The PivotChart report shows data series, categories, data markers, and axes in the same way as the standard charts do. It is possible to change the chart type and other options such as titles, legend placement, data labels, and chart location.

Answer option A is incorrect. Stand-alone slicers are referenced from Online Analytical Processing (OLAP) Cube functions. These stand-alone slicers can be connected with any PivotTable in future.

They can be added by using the slicer button on the Ribbon. They need to be manually linked into grids as required.

Answer option C is incorrect. A form control is an original control that is compatible with old versions of Excel, beginning with Excel version5.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 an existing macro to a control, or write or record a new macro. These controls cannot be added to UserForms, used to control events, or modified to run Web scripts on Web pages.

Answer option B is incorrect. A PivotTable report is used to summarize large amounts of data, to analyze numerical data, and to answer unexpected questions about data.

The following figure shows the option used to insert a signature line that specifies the individual

who must sign.

C:\Documents and Settings\Administrator\Desktop\1.JPG

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 markedas final, various options such as typing, editing commands, and proofing marks are disabled orturned off and the spreadsheet becomes read-only. This command helps a user to communicate that heis sharinga completed version ofa spreadsheet.This command also preventsreviewersor readers from making inadvertent modifications to the spreadsheet.

2. Encrypt with Password: When a user selects the Encrypt with Password option, theEncrypt Document dialog box appears. In the Password box, it is required to specify a password.Microsoftis not able to retrieve lost or forgotten passwords, so it is necessary for a user to keep a listof 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 torestrict permissions. A user is required to use a Windows Live ID or a Microsoft Windows account torestrict permissions. These permissions can be applied via a template that is used bythe organization in which the user is working. These permissions can also be added by clickingRestrict Access.

6.Add a Digital Signature: This option is used to add a visible or invisible digital signature. It

authenticatesdigitalinformationsuchasdocuments,e-mailmessages,andmacrosby

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.