MS Excel Questions and Answers

Answer: D

Correct answer: A formula refers to a cell that no longer exists

#REF! means a formula refers to a cell that is no longer there - almost always because the row, column or sheet it pointed to has been deleted. It is the most damaging of the error values, because it is irreversible: the original address is gone from the formula, replaced by the error itself, so undoing the deletion does not restore it. Every other error can be fixed by correcting the cause; this one requires the formula to be rewritten. It also propagates, since anything referring to the broken cell reports an error in turn. The habit that prevents it is to delete contents with the Delete key rather than removing entire rows and columns, and to check dependent cells with Trace Dependents before deleting anything. The other errors named have their own causes: ##### for a column too narrow, #NAME? for a misspelt function, #DIV/0! for division by zero, #VALUE! for a wrong argument type and #N/A for a failed lookup.

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

Correct answer: Select the cells containing the title text and choose Center Across Selection in the Format Cells dialog box

Center Across Selection centres the text over the whole range while leaving the cells separate, so the columns can still be sorted and referenced individually. Merging them would centre the text too, but at the cost of destroying the cell structure. Widening a column moves the text, it does not centre it.

Answer: A

Correct answer: The date value for the day according to system clock

TODAY() returns the current date as a date value, taken from the system clock and recalculated whenever the sheet is. NOW() returns the date with the time as well.

Answer: D

Correct answer: All of above

Shading is applied from the Patterns tab of Format Cells, from the Fill Color button on the Formatting toolbar, or from the same button on the Drawing toolbar - three routes to the same setting.

Answer: D

Correct answer: The width of a row can be specified manually or fit automatically

Rows have height and columns have width. The statement mixes the two, which is what makes it the invalid one; the other three are all true of Excel.

Answer: D

Correct answer: All of above

An organization chart’s boxes can be selected in any of the three ways: Edit → Select All, right-clicking the chart background and choosing Select All, or holding Shift while clicking each box in turn. All three end with every box selected, so the combined option is correct.

Answer: B

Correct answer: To show a geographic distribution of data

A data map plots values onto a geographical map, shading each region according to its figure. Changes over time call for a line chart and comparisons for a bar chart.

Answer: B

Correct answer: No data will be entered in the adjacent cells

Overflowing text is displayed only while the neighbouring cells are empty; the moment anything is entered beside it, the display is cut off at the cell boundary. So it is acceptable only where those cells will stay empty.

Answer: B

Correct answer: Ctrl + Down Arrow

Ctrl + Down Arrow jumps to the last filled cell of the contiguous block below the cursor, and stops at the edge of any gap. From an empty cell it jumps to the next filled one, or to the bottom of the sheet if there is none. The same works in all four directions, and adding Shift selects everything passed over - so Ctrl + Shift + Down selects a whole column of data in one keystroke, which is the fastest way to select a long range. Ctrl + End, the fourth option, is different: it goes to the last used cell of the whole sheet, the intersection of the last used row and column. That is often further out than the visible data, because Excel remembers cells that once held content - and that stale boundary is what inflates file sizes and can be reset by deleting the surplus rows and columns and saving. Alt + Down opens a drop-down list in the cell.