MS Excel Questions and Answers

Answer: A

Correct answer: Pressing the Alt key

A cell is edited by clicking the formula bar, pressing F2, or double-clicking the cell. Alt activates the menu bar and does nothing to the cell.

Answer: D

Correct answer: Ctrl key

Dragging a selection moves it by default; holding Ctrl while dragging copies it instead, and the pointer shows a small plus sign to say so.

Answer: B

Correct answer: The row stays fixed when the formula is copied, the column adjusts

In B$4 the dollar sign precedes the row number, so the row is fixed at 4 while the column adjusts as the formula is copied sideways. This is a mixed reference. The four forms are worth holding together: B4 is relative, both parts adjusting; $B$4 is absolute, neither adjusting; $B4 fixes the column; B$4 fixes the row. The dollar always applies to whatever follows it. Mixed references are what allow a single formula to fill an entire grid. In a multiplication table, =$A2*B$1 copied across and down always reads the header column and the header row, because the column is fixed in one and the row in the other. Writing that with absolute or relative references alone is impossible. F4 cycles through the four forms while editing. The dollar sign here has nothing to do with currency formatting, which is set separately.

Answer: D

Correct answer: 65,535

A worksheet up to Excel 2003 has 65,536 rows, that is 2¹⁶, numbered 1 to 65,536. Excel 2007 raised it to 1,048,576.

Answer: C

Correct answer: A meaningful name assigned to a cell or range so it can be used in formulas

A named range gives a cell or range a meaningful name - TaxRate, SalesData - which can then be used in formulas in place of the address. =B2*TaxRate is far easier to read and to check than =B2*$H$1. Names are created from the Name Box to the left of the formula bar, or managed from the Name Manager on the Formulas tab. They carry a practical advantage beyond readability: a name is absolute by default, so a formula using one can be copied anywhere without the reference shifting. That removes the commonest cause of broken formulas. A name may be scoped to the whole workbook or to a single sheet, must begin with a letter or underscore, and may not contain spaces or look like a cell address. Names can also refer to a constant or to a formula rather than a range, and they are what makes a chart or a validation list expand automatically when built on a dynamic definition.

Answer: A

Correct answer: Restrict what may be entered into a cell

Data Validation, on the Data tab, restricts what may be typed into a cell - a whole number within a range, a date, a text of limited length, a value from a list, or anything matching a custom formula. Its most common use is a drop-down list, created by choosing List and supplying either typed values or a range reference. That both speeds entry and guarantees consistent spelling, which matters greatly if the column will later be used in a lookup or a pivot table. It also supplies an input message shown when the cell is selected and an error alert when an invalid entry is attempted. The alert can be set to Stop, which refuses the entry, or to Warning or Information, which allow it after a prompt. Its limit is worth knowing: validation applies to what is typed, and a value pasted into the cell bypasses it entirely. Circle Invalid Data exists to find such entries afterwards.

Answer: B

Correct answer: Placed in a word table

An Excel range pasted into Word with an ordinary paste arrives as a Word table - the cells become table cells that Word formats and edits itself, with no connection back to the workbook. Linking and embedding are alternatives chosen through Paste Special, and a hyperlink would only point at the file.

Answer: B

Correct answer: Referencing

Using a value from another sheet is referencing it, written with the sheet name and an exclamation mark: =Sheet2!A1.

Answer: D

Correct answer: look up a value with more flexibility than VLOOKUP allows

MATCH returns the position of a value within a range, and INDEX returns the value at a given position. Combined - =INDEX(C1:C100,MATCH("Delhi",A1:A100,0)) - they perform a lookup. The combination is preferred to VLOOKUP for three reasons. It can look leftwards, since the lookup column and the return column are specified independently, whereas VLOOKUP can only return a column to the right of the one it searches. It does not break when a column is inserted, because there is no fixed column index to become wrong. And it is faster on large sheets, since only two columns are examined rather than the whole table. The third argument of MATCH should be 0 for an exact match; the default of 1 requires sorted data and fails silently otherwise. A two-dimensional lookup nests a second MATCH for the column: =INDEX(range,MATCH(...),MATCH(...)). XLOOKUP now does all of this in one function.

Answer: B

Correct answer: Swaps rows and columns of the copied data

Transpose turns rows into columns and columns into rows: a range four cells wide and two deep becomes two wide and four deep. It is used when data has been laid out the wrong way for what is needed - a table with months across the top when they are wanted down the side, which matters because charts, pivot tables and most functions expect records in rows and fields in columns. Two cautions apply. The destination must not overlap the source. And formulas are transposed with their references adjusted, which frequently breaks them, so pasting values and transposing together is usually safer. The TRANSPOSE function does the same as a live formula, so the result updates when the source changes - entered as an array formula in older versions, and spilling automatically in newer ones. Paste Special is reached with Ctrl + Alt + V, and Transpose is a checkbox rather than one of the radio buttons.