MOS - Microsoft Office Specialist Excel: Managing Worksheets & Workbooks Questions and Answers

Question 1

A user is working with a large dataset and wants to view the column headers in row 1 and the record identifiers in column A simultaneously, even when scrolling deep into the worksheet. Which Excel feature allows for this type of view?

Accepted Answer

Freeze Panes

Answer

The 'Freeze Panes' feature is specifically designed to lock specific rows and columns in place, making them visible while you scroll through the rest of the worksheet. 'Split' divides the window into different panes but doesn't lock rows/columns in the same way. 'New Window' and 'Arrange All' are for viewing multiple workbooks or worksheets at once.

Question 2

You have created a standardized budget workbook that your team will use as a starting point for all new projects. To prevent accidental overwriting of the original file, you need to save it in a format that opens as a new, untitled copy each time. Which file type should you choose in the 'Save As' dialog box?

Accepted Answer

Excel Template (.xltx)

Answer

Saving a file as an Excel Template (.xltx) creates a master copy. When a user opens a template file, Excel automatically creates a new, unsaved workbook based on the template, leaving the original template file untouched.

Question 3

A project manager needs to compare sales data from two different workbooks simultaneously. What is the most efficient way to display both workbooks on the screen side-by-side for comparison?

Accepted Answer

Open both workbooks and use the 'Arrange All' command on the View tab.

Answer

The 'Arrange All' command on the View tab is designed to organize all open workbook windows on the screen. The user can choose from options like Tiled, Horizontal, or Vertical to view them simultaneously.

Question 4

To prevent other users from adding, deleting, renaming, or hiding worksheets in a shared workbook, which protection option should be applied?

Accepted Answer

Protect Workbook Structure

Answer

The 'Protect Workbook Structure' option specifically controls the ability to make changes to the workbook's layout, such as adding, deleting, or renaming sheets. 'Protect Sheet' applies to the contents of a single sheet, 'Encrypt with Password' restricts opening the file, and 'Mark as Final' is a soft protection that can be easily bypassed.

Question 5

A financial analyst needs to display a summary total from a cell in a 'Source.xlsx' workbook within a cell in a 'Report.xlsx' workbook. The value in 'Report.xlsx' must update automatically whenever the data in 'Source.xlsx' changes. Which of the following methods creates this dynamic link?

Accepted Answer

In the 'Report' workbook, type '=' in a cell, navigate to the 'Source' workbook, click the desired cell, and press Enter.

Answer

Creating an external reference by starting a formula with '=', then clicking the cell in the source workbook, is the standard method for linking cells between workbooks. This creates a formula that automatically pulls the current value from the source cell.

MOS - Microsoft Office Specialist Certification Practice Test

MOS - Microsoft Office Specialist Certification Practice Test

MOS - Microsoft Office Specialist Excel: Managing Worksheets & Workbooks Questions and Answers