MS Excel Questions and Answers

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.

Answer: C

Correct answer: COUNT

COUNT counts numeric entries only, ignoring text, logical values and empty cells. Dates are counted, since Excel stores them as numbers. The family is worth learning together, because the differences are exactly what is examined. COUNTA counts every non-empty cell whatever it holds, including text and error values - and including a cell containing an empty string returned by a formula, which looks blank but is not. COUNTBLANK counts genuinely empty cells. COUNTIF and COUNTIFS count those meeting one or more conditions. A practical use of the distinction: comparing COUNTA with COUNT on the same column reveals how many entries are text rather than numbers - the usual explanation for a SUM that comes out lower than expected, since SUM ignores numbers stored as text. Text to Columns, or multiplying by 1, converts them.

Answer: C

Correct answer: Opens a shortcut menu listing everything you can do to the object

The right button opens a context menu whose contents depend on what was clicked - cell, sheet tab, toolbar, chart element. It is the quickest route to most commands.

Answer: D

Correct answer: title slide

The title slide is the opening layout, carrying the presentation title and a subtitle. It names the topic and sets the tone before any content appears. A bullet slide carries the body points, while table and graph slides present data later in the deck.

Answer: D

Correct answer: 3.14

=ROUND(3.14159,2) rounds to two decimal places, giving 3.14. The second argument is the number of decimal places. That argument may be zero, which rounds to a whole number, or negative, which rounds to the left of the decimal point: =ROUND(1234,-2) gives 1200. That is a detail worth knowing, since it is the neat way to round to the nearest hundred or thousand. The related functions differ in direction. ROUNDUP and ROUNDDOWN always go the same way regardless of the digit; MROUND rounds to the nearest multiple; INT truncates towards negative infinity; and TRUNC simply cuts the decimals off. The important practical point is that ROUND changes the stored value, whereas decreasing the decimal places displayed only changes the appearance. A column that appears to total wrongly is almost always displaying rounded figures while summing the full ones.

Answer: C

Correct answer: A recorded or written sequence of actions that can be replayed

A macro is a stored sequence of actions that can be replayed on demand. It is recorded with the Macro Recorder, which watches what you do and writes the corresponding code, or written directly in VBA - Visual Basic for Applications - in the editor opened with Alt + F11. Its purpose is to automate anything repetitive: formatting a report the same way each month, cleaning an import, or producing a set of sheets from a template. A workbook containing macros must be saved as .xlsm; the ordinary .xlsx format silently discards them, which is a common cause of lost work. That separation exists for security, since macros can do anything the user can, and have historically been a route for malware. Excel therefore disables them by default and shows a warning bar. Recorded macros are literal and brittle - they repeat exact cell addresses - so recording is often used as a starting point and then edited.