MS Excel Questions and Answers

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

Correct answer: all of above

A design template can be applied to the current slide, to every slide in the presentation, or set as the default for new presentations.

Answer: D

Correct answer: 10+50

Without a leading = the entry is text, not a formula, so the cell shows the characters 10+50 rather than 60.

Answer: D

Correct answer: All of above

A cell becomes active when it is clicked, when Tab moves to it, or when an arrow key reaches it - all three simply move the selection.

Answer: D

Correct answer: All of above

Any of the three fits the column to its contents: double-clicking the boundary at the right of the heading, and the equivalent gestures the question lists. The right boundary is the one that belongs to that column.

Answer: B

Correct answer: On the file menu, click print, and then click selection under print what

Select the cells you want, then open File → Print and choose Selection under “Print what”. That prints just the highlighted range for this one job and leaves any saved print area untouched - which is what you need when the area differs from the usual one. Setting a print area would overwrite the stored setting, and custom views belong to Excel’s view management.

Answer: A

Correct answer: rotate text on the chart

Fill Effects offers gradients, textures, patterns and pictures, each with foreground and background colours. Rotating text is done from the axis or data label format dialog, not from a fill.

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

Correct answer: Re-apply the footer placeholder

If a footer placeholder is deleted in master view, Format → Master Layout lets you tick the footer box again and PowerPoint restores the placeholder in its original position. Re-applying the slide layout affects individual slides, and creating a new master would discard all the other formatting.

Answer: C

Correct answer: From Format Cells dialog box mark Shrink to fit check box

Shrink to fit reduces the displayed font size until the text fits the column, leaving the column width alone. Autofit does the opposite - it changes the column to suit the text.