MS Excel Questions and Answers

Answer: A

Correct answer: Use the browse feature in the slide design task pane to find the file that has your design template and apply it to the current file

The Slide Design task pane ends with a Browse… link that opens any presentation or template file and applies its design to the current one, carrying over colours, fonts and masters intact. Copying a slide brings that slide’s design only as a side effect and can leave two masters in the file, and saving under a new name gives you a copy of the whole presentation rather than a reusable design.

Answer: D

Correct answer: Copy the worksheet

The basic steps are to enter the text and data, modify the layout, and save the workbook. Copying a worksheet is something you may do afterwards, not part of creating one.

Answer: C

Correct answer: the small square at the corner of a selection used to copy or extend a series

The Fill Handle is the small square at the bottom right corner of a selection. Dragging it copies the contents into adjacent cells, or extends a recognised series. It recognises a great many patterns. Dragging a single date advances it by a day; dragging Jan produces the months; dragging Monday produces the days. Entering two values first - 5 and 10 - establishes the step, so the fill continues 15, 20 and so on. Double-clicking the handle fills down as far as the adjacent column has data, which is much faster than dragging on a long list. Formulas filled this way have their relative references adjusted, which is exactly the behaviour absolute references with dollar signs exist to prevent. The Auto Fill Options button that appears afterwards offers to copy without formatting, fill formatting only, or fill as a series - useful when the default guess is wrong.

Answer: D

Correct answer: Networkdays

NETWORKDAYS returns the number of whole working days between two dates, excluding weekends and any holidays given to it. WORKDAY does the reverse - it returns the date a given number of working days away.

Answer: D

Correct answer: a and b

Dragging a selection moves it; holding Ctrl while dragging copies it. There is no drag gesture that adds contents to what is already there.

Answer: A

Correct answer: Empty cells and text

AVERAGE ignores empty cells and text, dividing only by the count of numeric entries. Negative numbers and decimals are included like any other value. The distinction that matters is between an empty cell and a cell containing zero. An empty cell is left out of both the total and the count; a zero is included in both and pulls the average down. A range with three values and two blanks averages over three; the same range with zeros in place of the blanks averages over five. AVERAGEA behaves differently, counting text as zero and including it in the divisor, which usually gives a lower and less useful figure. The conditional forms are AVERAGEIF and AVERAGEIFS, which average only the entries meeting given criteria. An average over an empty range returns #DIV/0!, since there is nothing to divide by.

Answer: A

Correct answer: are the same, except that in a list box the bullets are enabled

Both are containers for text, and the list box is simply a text box with bullets switched on; anything one can hold the other can. The claim that lists cannot be made in a text box is therefore false.

Answer: C

Correct answer: Finds related records

VLOOKUP searches the first column of a table for a value and returns something from the same row of another column - which is how one record is matched to another. The V is for vertical, not for the letter looked up.

Answer: D

Correct answer: Cells marked as locked cannot be edited until the sheet is unprotected

Protecting a worksheet prevents changes to cells whose Locked attribute is set. The catch, and the point most often missed, is that every cell is locked by default - the attribute does nothing at all until the sheet is protected. The correct sequence is therefore the reverse of what most people expect: first select the cells that should remain editable and clear their Locked box in Format Cells, then protect the sheet. Protecting first locks everything. The protection dialogue also lists what users may still do - select cells, sort, use filters, format columns - each of which can be permitted or withheld. Its password is a deterrent rather than real security, being easily removed. A related option is Hidden on the same tab, which conceals a cell’s formula from the formula bar once the sheet is protected. The second option describes Encrypt with Password under File, Info, which is a different and stronger protection.

Answer: B

Correct answer: create and edit formulas containing functions

The formula palette appears when a function is inserted and shows each argument with a box to fill and the result so far, so a nested function can be built without remembering its syntax.