MS Excel Questions and Answers

Answer: D

Correct answer: Ampersand (&)

The ampersand joins text: =A1&" "&B1 puts the contents of two cells together with a space between. CONCATENATE does the same thing as a function.

Answer: A

Correct answer: Edit the notes master and add your company logo

Speaker notes pages are governed by the notes master, which holds the slide image, the notes area and anything else to appear on every notes page. The slide master governs the slides themselves.

Answer: D

Correct answer: Ctrl key

Dragging within a sheet moves the data; dragging between sheets needs Ctrl held down so that the pointer can cross the sheet tabs and drop onto another sheet.

Answer: D

Correct answer: Ctrl + Shift + %

Ctrl + Shift + % applies the percentage format, displaying the value multiplied by a hundred with a per cent sign and no decimal places. The number-format shortcuts share the pattern Ctrl + Shift with a symbol key, and are worth learning together: ~ for General, ! for number with two decimals and thousands separators, $ for currency, % for percentage, ^ for scientific, # for date and @ for time. Each symbol suggests its format, which makes the set easier to remember than it looks. The essential point about percentage format is that it changes only the display. A cell holding 0.25 shown as 25% still contains 0.25, so a formula multiplying by it works correctly. Typing 25 into a cell already formatted as a percentage gives 2500%, which is the usual surprise. Ctrl + 1 opens the full Format Cells dialogue for anything the shortcuts do not cover.

Answer: B

Correct answer: There is a comment associated with the cell

A small red triangle in the top right corner marks a comment, which appears when the pointer rests on the cell. A green triangle in the top left corner is Excel's error indicator, which is the mark the other option describes.

Answer: D

Correct answer: Data

Consolidation gathers values from several ranges into one summary, so it sits with the other data-analysis commands: Data → Consolidate. Insert adds objects, Format changes appearance, and Tools holds macros and add-ins.

Answer: A

Correct answer: To monitor the values of chosen cells while working elsewhere in the workbook

The Watch Window, on the Formulas tab, displays a small floating list of chosen cells with their current values and formulas, and keeps them visible however far away in the workbook you are working. Its value is on large models where an input on one sheet drives a total on another. Rather than switching back and forth to check the effect of a change, the total is added to the Watch Window and updates in view. Each entry records the workbook, sheet, cell name, address, value and formula, and double-clicking one jumps straight to that cell - which makes it a navigation aid as well. It sits alongside the other auditing tools on the same tab: Trace Precedents and Trace Dependents, which draw arrows showing which cells feed a formula and which depend on it, Evaluate Formula, which steps through a calculation, and Error Checking. Together these are how a spreadsheet is debugged.

Answer: D

Correct answer: Automatic filters, banded rows and a range that grows with new data

Formatting a range as a Table with Ctrl + T turns it into a named object with several behaviours the ordinary grid does not have. It gains filter arrows, banded row shading, and a header row that stays visible as you scroll. Most importantly it expands automatically: typing in the row beneath extends the table, and every formula, chart, pivot table and named range built on it picks up the new data without adjustment. That single property removes the commonest source of stale reports. Formulas inside a table use structured references such as =[@Quantity]*[@Price], which are far more readable than cell addresses, and a formula entered in one cell of a column fills the whole column at once. A Total Row can be switched on, using SUBTOTAL so that it respects filters. The table is renamed from the Table Design tab, and can be converted back to a plain range at any time.

Answer: C

Correct answer: Description

A worksheet is judged on whether it is efficient, clear to read and auditable - that is, whether its calculations can be traced and checked. Description is not one of the design criteria.

Answer: D

Correct answer: A formula is attempting to divide by zero or by an empty cell

#DIV/0! appears when a formula divides by zero or by a cell that is empty, since an empty cell is treated as zero in arithmetic. The usual remedy is to guard the calculation: =IFERROR(A1/B1,"") returns a blank instead of the error, or =IF(B1=0,"",A1/B1) tests the divisor directly. IFERROR is more concise but hides every error, not only this one, so it should be used knowing that. The other error values each have their own cause and are worth learning as a set. ##### means the column is too narrow - widening it fixes it. #REF! means a reference points to a cell that has been deleted. #NAME? means a function name is misspelt or a text value was typed without quotation marks. #VALUE! means an argument is of the wrong type, such as text where a number is expected. #N/A means a lookup found nothing.