MOS - Microsoft Office Specialist Excel: Managing Worksheets & Workbooks Questions and Answers — 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?
- Freeze Panes (Correct answer)
- Split
- New Window
- Arrange All
Correct answer: Freeze Panes
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?
- Excel Workbook (.xlsx)
- Excel Macro-Enabled Workbook (.xlsm)
- Excel Template (.xltx) (Correct answer)
- Excel Binary Workbook (.xlsb)
Correct answer: Excel Template (.xltx)
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?
- Copy and paste the data from one workbook into a new sheet in the other.
- Use the 'New Window' command on the View tab for each workbook.
- Open both workbooks and use the 'Arrange All' command on the View tab. (Correct answer)
- Use the 'Switch Windows' command to toggle back and forth quickly.
Correct answer: Open both workbooks and use the 'Arrange All' command on the View tab.
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?
- Protect Sheet
- Encrypt with Password
- Mark as Final
- Protect Workbook Structure (Correct answer)
Correct answer: Protect Workbook Structure
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?
- In the 'Report' workbook, copy the desired cell from the 'Source' workbook and use the Paste Special > 'Values' option.
- In the 'Report' workbook, type '=' in a cell, navigate to the 'Source' workbook, click the desired cell, and press Enter. (Correct answer)
- Use the Data > 'From File' > 'From Workbook' feature to import the entire worksheet.
- In the 'Report' workbook, use the Insert > 'Object' command and select the 'Source' workbook.
Correct answer: In the 'Report' workbook, type '=' in a cell, navigate to the 'Source' workbook, click the desired cell, and press Enter.
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.
Question 6: While analyzing a large worksheet, a user wants to view four different sections of the same sheet at once, scrolling independently in each section. Which command on the 'View' tab should be used to achieve this?
- Freeze Top Row
- New Window
- Split (Correct answer)
- Arrange All
Correct answer: Split
The 'Split' command divides the worksheet into two or four adjustable panes, each with its own scroll bars, allowing the user to view and work on different parts of the same sheet simultaneously.
MOS - Microsoft Office Specialist Excel: Managing Worksheets & Workbooks Questions and Answers
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?