MS Excel Questions and Answers

Answer: D

Correct answer: a, b and c

Display and printing are controlled separately: gridlines may be shown on screen but not printed, printed but not shown, or neither. All three statements are therefore true.

Answer: C

Correct answer: Refresh Data

A pivot table holds a snapshot of its source, so changes to the source do not appear until Refresh Data is pressed. That is deliberate: recalculating a large pivot table on every edit would be slow.

Answer: D

Correct answer: Ctrl + 1

Ctrl + 1 opens the Format Cells dialogue, which holds every formatting option in one place: number format, alignment, font, border, fill and protection. The Number tab is the one that repays study, since Excel’s handling of numbers is entirely a matter of format. A date is a serial number displayed as a date; a percentage is a fraction displayed with a per cent sign; and a custom format code can display a number in almost any way without changing its value. The Protection tab is where cells are marked Locked or Unlocked - a setting that does nothing until the sheet itself is protected, which catches many users out. The neighbouring number shortcuts are worth knowing: Ctrl + 9 hides the selected rows, Ctrl + 0 hides columns, and Ctrl + Shift + 9 and 0 unhide them. Ctrl + F finds, and Ctrl + P prints.

Answer: B

Correct answer: the largest value in a range

MAX returns the largest numeric value in a range, and MIN the smallest. Both ignore text and empty cells, and both return 0 if the range contains no numbers at all. The fourth option describes MODE, which returns the most frequent value - one of the three measures of central tendency, alongside AVERAGE for the mean and MEDIAN for the middle value. Each answers a different question, and the median is the one to prefer where a few extreme values would distort the mean. For a ranked position rather than the extreme, LARGE and SMALL take a second argument: =LARGE(A1:A100,3) gives the third largest. MAXIFS and MINIFS apply conditions. A common combination worth knowing is =INDEX(B:B,MATCH(MAX(A:A),A:A,0)), which returns the value in column B belonging to the row where column A is greatest.

Answer: C

Correct answer: An absolute reference does not change when the formula is copied elsewhere

A relative reference such as A1 adjusts when the formula is copied: moved one row down it becomes A2, and one column right it becomes B1. An absolute reference such as $A$1 stays fixed wherever the formula goes. The dollar signs are what freeze it, and either part can be frozen alone. $A1 is mixed, fixing the column while the row adjusts; A$1 fixes the row while the column adjusts. The classic use of a mixed reference is a multiplication table, where one formula filled across and down must always read the header row and the header column. The F4 key cycles a reference through the four forms while editing a formula, which is far quicker than typing the dollars. Getting this wrong is the commonest cause of a formula that works in one cell and produces nonsense when filled down - typically a tax rate or exchange rate that shifts away from its cell.

Answer: B

Correct answer: Apply an entrance effect, and then set it to by letter in the effect option dialog box

Give the text an entrance effect in the Custom Animation pane, then open Effect Options → Text Animation and set Animate text: By letter. The animation then plays one character at a time. Slowing a Fly In only makes the whole block move more slowly, and the fade scheme animates by paragraph.

Answer: A

Correct answer: The difference between two dates in years, months or days

DATEDIF returns the difference between two dates in the unit given by its third argument: "Y" for complete years, "M" for complete months, "D" for days, and the compound codes "YM", "YD" and "MD" for remainders. Its standard use is age: =DATEDIF(A1,TODAY(),"Y") gives completed years, which is what an age must be - simple subtraction and division by 365 goes wrong across leap years. It is a curiosity of Excel: inherited from Lotus 1-2-3, undocumented in the function list and absent from IntelliSense, but present in every version and fully supported. It must be typed in full. The other date functions are better documented. TODAY and NOW give the current date and time; DATE builds one from parts; EOMONTH finds the end of a month; WEEKDAY gives the day number; and NETWORKDAYS counts working days between two dates, excluding weekends and a supplied holiday list.

Answer: C

Correct answer: transitions

The effects that introduce each slide are transitions; animations apply to the objects on a slide once it is displayed.

Answer: D

Correct answer: Strikethrough

The Font dialog offers Underline, Shadow, Emboss, Superscript and Subscript. Strikethrough is not among them, which is what makes it the answer.

Answer: A

Correct answer: Slides

In the Print dialog, choosing the Slides option lets you type a range, and non-adjacent slides are entered as a comma-separated list - here 5,12. Current slide prints only the one on screen, and a custom show would have to be defined in advance.