MS Excel Questions and Answers

Answer: C

Correct answer: a file that can contain several worksheets

A workbook is the file; a worksheet is one sheet of cells within it. A new workbook typically opens with one sheet, and more can be added, renamed, coloured, reordered, copied and hidden using the tabs at the bottom. The distinction matters in formulas, because a reference can cross sheets. =Sheet2!A1 reads a cell on another sheet of the same workbook, and =[Budget.xlsx]Sheet1!A1 reads one in a different workbook - a link that must be updated when the source changes. A 3-D reference spans several sheets at once: =SUM(Jan:Dec!B5) adds cell B5 across twelve monthly sheets, which is the standard way to consolidate identical sheets. Several sheets can be selected together with Ctrl or Shift, after which typing or formatting applies to all of them at once - useful, but the cause of a good deal of accidental damage when the grouping is forgotten.

Answer: D

Correct answer: Recognises a pattern from your examples and completes the rest of a column

Flash Fill watches the pattern in what you type and fills the rest of the column to match. Typing the first name from a column of full names, then beginning the second, prompts it to complete every remaining row. It is triggered with Ctrl + E or accepted from the suggestion that appears. It handles splitting, joining, reformatting and case changes without a formula - extracting a domain from an email address, reformatting a phone number, or building initials from names. Its limits are worth knowing. It produces static values, not formulas, so the result does not update when the source changes. It infers the rule from very few examples and can guess wrongly on irregular data, which needs checking. And it works down a column adjacent to the source, not across arbitrary ranges. Where a live result is needed, Text to Columns or text functions such as LEFT, MID and FIND are the alternatives.

Answer: B

Correct answer: find the best values for several inputs subject to constraints

Solver is an optimisation tool. It adjusts several input cells at once to maximise, minimise or hit a target in an objective cell, while respecting constraints that may be placed on any of them. It is an add-in rather than a built-in feature and must be enabled from File, Options, Add-ins before it appears on the Data tab. Its relationship to Goal Seek is the point most often examined. Goal Seek changes exactly one cell to reach an exact target, with no constraints. Solver changes many, accepts constraints such as total hours must not exceed 40 or quantities must be whole numbers, and can seek a maximum or minimum rather than a specific value. Typical uses are production planning, portfolio allocation, staff rostering and transport routing - the classic linear-programming problems. It offers several solving methods, and the Simplex method is chosen where the model is genuinely linear.

Answer: B

Correct answer: Cycles the selected cell reference between relative, absolute and mixed

While editing a formula, F4 cycles the reference at the cursor through its four forms: A1, $A$1, A$1, $A1, and back again. It saves typing dollar signs and is one of the most-used keys in Excel. Outside formula editing, F4 has a quite different role: it repeats the last action, which makes it useful for applying the same formatting to several places in turn. Its behaviour therefore depends entirely on the mode Excel is in. The other function keys are worth knowing alongside it. F2 edits the active cell in place. F9 recalculates all open workbooks - and, when part of a formula is selected in the formula bar, evaluates just that part, which is the standard way to debug a long formula. F11 creates a chart on a new sheet. F5 opens Go To, whose Special button selects all blanks, constants or formulas at once.

Answer: C

Correct answer: Double click the chart object

Double-clicking an embedded chart opens it for editing in place, replacing PowerPoint's menus with those of the charting program. A single click merely selects it.

Answer: C

Correct answer: COUNT

COUNT counts numeric entries only, ignoring text, logical values and empty cells. Dates are counted, since Excel stores them as numbers. The family is worth learning together, because the differences are exactly what is examined. COUNTA counts every non-empty cell whatever it holds, including text and error values - and including a cell containing an empty string returned by a formula, which looks blank but is not. COUNTBLANK counts genuinely empty cells. COUNTIF and COUNTIFS count those meeting one or more conditions. A practical use of the distinction: comparing COUNTA with COUNT on the same column reveals how many entries are text rather than numbers - the usual explanation for a SUM that comes out lower than expected, since SUM ignores numbers stored as text. Text to Columns, or multiplying by 1, converts them.

Answer: A

Correct answer: SUBTOTAL can ignore filtered-out rows while SUM always includes them

SUM adds every cell in its range, whether or not the rows are visible. SUBTOTAL ignores rows hidden by a filter, so a filtered total reflects only what is displayed. That is why the Total Row of an Excel Table uses SUBTOTAL, and why the Subtotal command on the Data tab inserts it rather than SUM. Its first argument selects the operation and, importantly, the behaviour towards manually hidden rows. Codes 1 to 11 - where 9 is SUM and 1 is AVERAGE - ignore filtered rows but include rows hidden by hand. Codes 101 to 111 ignore both. So =SUBTOTAL(9,B2:B100) and =SUBTOTAL(109,B2:B100) differ only when rows have been hidden manually. SUBTOTAL also skips any nested SUBTOTAL within its range, which is what allows subtotals inside a grand total without double counting. AGGREGATE extends the idea further, adding options to ignore error values as well.

Answer: C

Correct answer: A formula has been given an argument of the wrong type

#VALUE! means a formula has received the wrong kind of argument - most often text where a number was expected, as when =A1+B1 meets a cell containing a word or a stray space. The commonest hidden cause is a number stored as text, which looks identical to a real number but aligns left rather than right and is ignored by arithmetic. Text to Columns, or multiplying by 1, converts such a column. Invisible non-breaking spaces from pasted web data produce the same result and need SUBSTITUTE with CHAR(160) to remove. Date arithmetic against a text date fails the same way. The remedy in a formula is IFERROR, though it is better to find the offending cell with Trace Precedents or Evaluate Formula than to hide the symptom. The other errors have distinct causes: #REF! for a deleted reference, ##### for a narrow column, #NAME? for a misspelt function.

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: 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.