Microsoft Excel Managing Data Cells and Ranges Questions and Answers

Question 1

A user wants to copy a range of cells containing formulas (A1:A10) to a new location (B1:B10), but needs to paste only the calculated values, not the formulas themselves. Which Paste Special option should be used?

Accepted Answer

Values

Answer

The 'Values' option in Paste Special is used to paste the static, calculated results of formulas. This action removes the underlying formulas, leaving only the visible data.

Question 2

You are working with a large dataset and want to quickly select all the blank cells within a specified range to either delete them or fill them with specific data. Which feature would be most efficient for this task?

Accepted Answer

Go To Special

Answer

The Go To Special feature allows you to select cells that meet specific criteria, such as being blank. By selecting 'Blanks' in the Go To Special dialog box, you can highlight all empty cells in a range at once for further action.

Question 3

A user has a table of sales data where the months are listed in Row 1 (B1:M1) and product names are in Column A (A2:A20). For a new report, the user needs to switch the orientation so that the months are in Column A and the product names are in Row 1. What is the most direct way to accomplish this?

Accepted Answer

Use the Transpose option in Paste Special

Answer

The Transpose feature is specifically designed to rotate data, switching rows to columns and columns to rows. This can be done via the Paste Special dialog box or by using the TRANSPOSE function.

Question 4

Which of the following is an invalid character for use within a named range in Excel?

Accepted Answer

Space ( )

Answer

Named ranges in Excel cannot contain spaces. While underscores, periods, and backslashes are permissible as part of a name, spaces will result in an error when trying to create the named range.

Question 5

You frequently need to enter a specific, non-sequential list of department names into various worksheets (e.g., 'Sales', 'Marketing', 'HR', 'IT', 'Finance'). To save time and ensure consistency, you want to be able to type the first department name and use the Fill Handle to automatically populate the rest. What Excel feature should you use to set this up?

Accepted Answer

Custom List

Answer

Excel's Custom List feature allows you to define your own non-alphabetic or non-numeric series for use with the AutoFill (Fill Handle) functionality. Once a custom list is created, typing any entry from the list and dragging the fill handle will populate the rest of the series in the defined order.

Excel Practice Test

Excel Practice Test

Microsoft Excel Managing Data Cells and Ranges Questions and Answers