MS Excel Questions and Answers

Answer: D

Correct answer: look up a value with more flexibility than VLOOKUP allows

MATCH returns the position of a value within a range, and INDEX returns the value at a given position. Combined - =INDEX(C1:C100,MATCH("Delhi",A1:A100,0)) - they perform a lookup. The combination is preferred to VLOOKUP for three reasons. It can look leftwards, since the lookup column and the return column are specified independently, whereas VLOOKUP can only return a column to the right of the one it searches. It does not break when a column is inserted, because there is no fixed column index to become wrong. And it is faster on large sheets, since only two columns are examined rather than the whole table. The third argument of MATCH should be 0 for an exact match; the default of 1 requires sorted data and fails silently otherwise. A two-dimensional lookup nests a second MATCH for the column: =INDEX(range,MATCH(...),MATCH(...)). XLOOKUP now does all of this in one function.

Answer: A

Correct answer: find a value in the first column of a range and return a value from another column of the same row

VLOOKUP searches for a value down the first column of a range and returns a value from a specified column of the matching row. Its arguments are the lookup value, the table range, the column index number, and a final argument for the match type. The final argument is where most errors arise. FALSE demands an exact match; TRUE, which is the default if omitted, finds the nearest match below and requires the first column to be sorted. Leaving it out on unsorted data returns wrong answers silently rather than an error, which makes it a dangerous default. Two structural limitations are worth knowing: it can only look rightwards from the first column, and the column index breaks if a column is inserted into the table. HLOOKUP does the same across a row. INDEX with MATCH, and the newer XLOOKUP, avoid both limitations and are now generally preferred.

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: Removes extra spaces from text, leaving single spaces between words

TRIM removes leading and trailing spaces and reduces any run of spaces between words to one. It does not shorten text in any other way. It matters because data imported from another system routinely carries invisible surplus spaces, and a trailing space is enough to make a lookup fail or two apparently identical entries count as different. Wrapping a lookup value in TRIM is a standard defensive habit. Its limitation is that it removes only the ordinary space character. Text copied from a web page often contains a non-breaking space, character 160, which TRIM leaves untouched - the usual remedy is =TRIM(SUBSTITUTE(A1,CHAR(160)," ")). CLEAN is its companion, removing non-printing control characters. The second option describes LEFT or MID, and the fourth is done with Go To Special or a filter.

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

Correct answer: Toggles between showing formulas and showing their results

Ctrl + `, using the grave accent below the Escape key, toggles Show Formulas: every cell displays its formula instead of its result, and columns widen to fit. It is the fastest way to audit a sheet. Formulas that have been overwritten with typed constants become immediately visible, and inconsistencies in a column that should hold one repeated formula stand out at a glance. It is also useful for printing a formula listing as documentation. Pressing it again returns to normal. The same setting is on the Formulas tab and in Excel’s options, where it applies per worksheet. It is worth knowing because its effect is alarming if triggered accidentally - the sheet appears to have lost all its results - and the key is close enough to Escape and Tab to be hit by mistake. The related auditing tools are Trace Precedents, Trace Dependents and Evaluate Formula, which examine one formula at a time rather than all of them at once.

Answer: B

Correct answer: If you choose to select from the pre-made slide layouts, you cannot change the layout

A layout can be changed at any time simply by choosing another from the pane; the placeholders rearrange and the content stays. The other statements are true.

Answer: C

Correct answer: Ctrl + ;

Ctrl + ; inserts the current date as a static value, and Ctrl + Shift + ; inserts the current time. The important distinction is between these and the TODAY() and NOW() functions. The shortcuts stamp a fixed value that never changes - the right choice for recording when something happened. The functions recalculate every time the workbook is opened, which is what is wanted for a header showing today’s date but disastrous for a record of a transaction date. Excel stores dates as serial numbers counting from 1 January 1900, which is why a date can be subtracted from another to give a number of days, and why a date formatted as a number shows something like 45000. Ctrl + D fills down from the cell above, Ctrl + T creates a table, and Ctrl + N opens a new workbook - all real shortcuts doing something else.

Answer: B

Correct answer: SUM(A1:A5)

A formula must begin with =. Without it, SUM(A1:A5) is entered as ordinary text and simply sits in the cell as typed. Excel also accepts a leading + or - and converts it silently to an equals sign, which is why +A1+A2 works - a concession inherited from Lotus 1-2-3, whose users typed the plus out of habit. The commonest reason a formula appears as text is a missing equals sign, and the second is that the cell was formatted as Text before the formula was typed. In the second case adding the equals sign changes nothing: the cell must be reformatted as General and the formula re-entered. A third possibility is that Show Formulas is switched on, with Ctrl + `, which displays every formula instead of its result across the whole sheet. Text entries align left by default and numbers right, which is the quickest visual check.