MS Excel Questions and Answers

Answer: C

Correct answer: cell B5 on the worksheet named Sheet2

The exclamation mark separates the sheet name from the cell address, so Sheet2!B5 is cell B5 on the sheet called Sheet2. If the sheet name contains a space or a punctuation mark it must be enclosed in single quotation marks: ='Sales Data'!B5. Excel adds them automatically when a reference is built by clicking. A reference to another workbook adds the file name in square brackets: =[Budget.xlsx]Sheet1!A1. That creates a link, which prompts to update when the file is opened and breaks if the source is moved or renamed. Links are managed from Data, Edit Links. A 3-D reference spans several sheets: =SUM(Jan:Dec!B5) adds B5 across twelve monthly sheets, which is how identical sheets are consolidated. Renaming a sheet updates every formula referring to it automatically, which is one reason to rename rather than recreate.

Answer: B

Correct answer: slide sorter view

Slide Sorter view lays the whole presentation out as a grid of thumbnails, so slides can be re-ordered, duplicated, deleted and given transitions. Slide Show projects one slide full-screen, Outline view shows text only, and Notes Pages shows one slide with its notes.

Answer: D

Correct answer: Outline view

Outline view strips the slides down to their text - titles at the left margin, bullet levels indented beneath - so the argument of the deck can be written and re-ordered without any graphics in the way. Pictures, charts and drawn objects do not appear there.

Answer: B

Correct answer: Pastes the results of formulas rather than the formulas themselves

Paste Special → Values pastes the calculated results, discarding the formulas that produced them. The cells become static numbers or text. It is used constantly for two reasons. It breaks a dependency, so a result survives when its source data is deleted or the sheet it referred to is removed. And it fixes a value in place, so a calculation whose inputs will change is frozen at a moment in time. Copying a cell onto itself and pasting values is the standard way to convert a whole column of formulas into constants. The dialogue, Ctrl + Alt + V, offers a good deal more: Formats pastes formatting alone, Transpose swaps rows and columns, Values and Number Formats keeps the appearance as well as the result, and the arithmetic options add, subtract, multiply or divide the destination by what was copied - a quick way to scale a whole range by a factor.

Answer: B

Correct answer: It deletes rows whose values repeat in the chosen columns

Remove Duplicates, on the Data tab, permanently deletes rows whose values repeat across the columns selected in its dialogue, keeping the first occurrence of each. It reports how many were removed and how many remain. The choice of columns is the crucial part. Ticking every column removes only rows that are identical throughout; ticking one column alone treats two rows as duplicates whenever that single value repeats, regardless of the rest - which can discard data that was not in fact duplicated. It is destructive and its result is undoable only with Ctrl + Z, so the usual practice is to work on a copy. The non-destructive alternatives are the third and fourth options. Conditional Formatting has a Highlight Duplicate Values rule that marks them without touching anything. COUNTIF counts occurrences, so a helper column of =COUNTIF($A$2:$A$100,A2) shows which entries repeat and how often, which is the safer first step.

Answer: C

Correct answer: The chart updates automatically

A chart is linked to its source range, so changing a value redraws the chart immediately. That link is the reason charts are built from ranges rather than from typed figures. The limit worth knowing is that the link is to a fixed range, so data added beyond it is not picked up. A chart built on A1:B12 ignores a thirteenth row. Building it on an Excel Table instead solves this, since the table expands as rows are added and the chart follows - the standard remedy for a chart that stops reflecting the data. The source can be adjusted afterwards from Select Data, or by dragging the coloured outline that appears around the range when the chart is selected. Two related details: hidden rows are excluded from a chart by default, which can be changed in Select Data; and pasting a chart into Word offers either a link to the workbook or a static copy, and the choice matters when the report is sent on.

Answer: A

Correct answer: Transitions command

The shortcut menu shown during a running slide show offers navigation commands, speaker notes and the meeting reminder, but transitions must be set beforehand in the editing views - there is no command to change them while the show is running.

Answer: B

Correct answer: Joins several text values into one

CONCATENATE joins text values end to end, and the ampersand does the same more briefly: =A1&" "&B1 joins a first and last name with a space between. The space must be supplied explicitly, since nothing is added automatically. Newer versions provide CONCAT and TEXTJOIN. TEXTJOIN is the more capable, taking a delimiter and an option to ignore empty cells, so a list can be assembled without stray separators. The reverse operation is Text to Columns on the Data tab, which splits one column into several at a delimiter or at fixed widths - the second option here. Flash Fill, Ctrl + E, does the same by example and is often quicker. The related text functions are worth knowing: TRIM removes surplus spaces, UPPER, LOWER and PROPER change case, LEFT, RIGHT and MID extract parts, and LEN counts characters.

Answer: B

Correct answer: TRUE or FALSE

A comparison returns a logical value - TRUE or FALSE - not a number. =A1>B1 reports whether A1 exceeds B1. The comparison operators are =, >, <, >=, <= and <> for not equal. That last one catches people out, since it is written with two angle brackets rather than the mathematical symbol. Logical values behave as numbers in arithmetic, TRUE counting as 1 and FALSE as 0, which is what makes expressions such as =SUMPRODUCT((A1:A10>5)*1) work as a count. These comparisons are the raw material of the IF function, which turns them into useful results: =IF(A1>B1,"Higher","Lower"). To find the larger of two values - the second option - the function is MAX, and for the difference it is simple subtraction.

Answer: C

Correct answer: A formula that refers, directly or indirectly, to its own cell

A circular reference occurs when a formula depends on its own cell, either directly - =A1+1 typed into A1 - or through a chain that eventually comes back to it. Excel cannot resolve it, since the answer would depend on itself, so it displays a warning, shows zero, and names the offending cell in the status bar. The full list is found under Formulas, Error Checking, Circular References. The usual cause is accidental: a SUM formula placed inside the range it is adding, most often at the bottom of a column where the range was extended one row too far. Deliberate circularity is possible where a genuinely iterative calculation is intended, by enabling Iterative Calculation in Excel’s options and setting a maximum number of iterations and a change threshold. That is used for engineering and financial models that converge, and should not be left on by default, since it silently masks accidental circular references.