MS Excel Questions and Answers

Answer: A

Correct answer: find the input value needed to produce a desired formula result

Goal Seek, under What-If Analysis on the Data tab, works a calculation backwards: given a formula and a target result, it finds the input value that produces it. It takes three arguments - the cell containing the formula, the value it should reach, and the single cell to change - and then adjusts that cell iteratively until the target is met. A typical use is finding the sales figure needed to hit a profit target, or the interest rate that gives a required repayment. Its limits define when to use something else. It changes exactly one input, the changing cell must be a constant rather than a formula, and it finds an approximate numerical solution rather than an exact algebraic one. For several inputs at once, or for constraints and an optimum rather than a target, the Solver add-in is the tool. Data Tables and Scenario Manager, on the same menu, explore what happens when inputs vary rather than solving for one.

Answer: D

Correct answer: Inserts summary rows into a sorted list at each change of a chosen column

The Subtotal command inserts summary rows into a list wherever a chosen column changes value - a total for each region, department or month - together with a grand total, and adds outline controls so the detail can be collapsed. Its one prerequisite is that the list must first be sorted by the column being grouped on. Applied to unsorted data it produces a subtotal at every change of value, which may be dozens of meaningless groups. That step is where most difficulty with the feature arises. The rows it inserts use the SUBTOTAL function rather than SUM, which is what allows nested subtotals to avoid double counting and makes the totals respect any filter applied afterwards. The outline numbers at the left collapse the view to totals alone, which is useful for printing a summary. Subtotals are removed with the Remove All button in the same dialogue. For anything more elaborate, a pivot table is the better tool.

Answer: C

Correct answer: Ctrl + Home

Ctrl + Home jumps to the beginning of the presentation, and Ctrl + End to the last slide. Page Up and the Previous Slide button move back only one slide at a time.

Answer: B

Correct answer: Press the shift key and click the handout master view button

Holding Shift while clicking a view button at the bottom-left of the window switches to the corresponding master instead of the ordinary view - Shift + Slide Sorter gives Handout Master, Shift + Normal gives Slide Master. Without Shift the same button gives the ordinary view.

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

Correct answer: change the appearance of cells automatically according to their values

Conditional Formatting applies formatting that depends on a cell’s value, and updates itself whenever the value changes. It is on the Home tab. The built-in rules cover most needs: highlighting cells above or below a figure, marking the top or bottom ten, and flagging duplicate values. Three visual styles turn a column of numbers into something readable at a glance - data bars draw an in-cell bar proportional to the value, colour scales shade from one colour to another across the range, and icon sets add arrows or traffic lights. The most flexible option is a rule driven by a formula, which allows one cell to be formatted according to another. Using a formula such as =$C2="Overdue" with the range set to the whole row highlights the entire row when that column says Overdue - and note the mixed reference, which is what makes it work across columns. Rules are managed, reordered and cleared from the Manage Rules dialogue.

Answer: D

Correct answer: All of above

An organization chart’s boxes can be selected in any of the three ways: Edit → Select All, right-clicking the chart background and choosing Select All, or holding Shift while clicking each box in turn. All three end with every box selected, so the combined option is correct.

Answer: B

Correct answer: Right clicking the current slide

During a slide show the toolbars are hidden. Right-clicking anywhere on the slide raises the show’s shortcut menu, with Next, Previous, Go to Slide, Pointer Options, Screen and End Show. The formatting toolbar is not on screen during a show, so there is no button to click.

Answer: D

Correct answer: All of above

Auto ClipArt watches the words you type, so it both places suggested clip art in the presentation and checks the text - on the slides and in WordArt objects - as it does so. Since each statement describes part of what the feature does, the combined option is the right one.

Answer: A

Correct answer: select the box and text, then make changes

In an organization chart you must first click the box to select it and then click into its text before typing; selecting the box alone gives you the shape, and there is no text to highlight until the box is active. That is why only the option naming both steps is correct.