MS Excel Questions and Answers

Answer: B

Correct answer: A horizontal axis

Categories are plotted along the horizontal axis - the months, products or regions the values are grouped by. The values themselves go up the vertical axis.

Answer: B

Correct answer: slide sorter view

Slide Sorter view lays the whole presentation out as a grid of thumbnails, so slides can be re-ordered, duplicated, deleted and given transitions. Slide Show projects one slide full-screen, Outline view shows text only, and Notes Pages shows one slide with its notes.

Answer: C

Correct answer: Returns a specified value when a formula produces an error, and the result otherwise

IFERROR takes two arguments: a formula and a value to return if that formula produces any error. =IFERROR(A1/B1,0) gives the quotient normally and 0 when B1 is empty or zero. It is the concise replacement for the older =IF(ISERROR(x),y,x), which had to evaluate the formula twice. Its convenience is also its risk. It catches every error type, so a genuine #REF! from a deleted column is hidden just as quietly as an expected #DIV/0!. A formula wrapped in IFERROR can be silently wrong for months. Where only one error is expected, the more precise tools are better: IFNA catches only #N/A, which is the right choice around a lookup, and an explicit test such as =IF(B1=0,"",A1/B1) guards the actual condition. The second option describes ISERROR, which returns a logical value rather than substituting one.

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

Correct answer: find the input value needed to produce a desired formula result

Goal Seek, under What-If Analysis on the Data tab, works a calculation backwards: given a formula and a target result, it finds the input value that produces it. It takes three arguments - the cell containing the formula, the value it should reach, and the single cell to change - and then adjusts that cell iteratively until the target is met. A typical use is finding the sales figure needed to hit a profit target, or the interest rate that gives a required repayment. Its limits define when to use something else. It changes exactly one input, the changing cell must be a constant rather than a formula, and it finds an approximate numerical solution rather than an exact algebraic one. For several inputs at once, or for constraints and an optimum rather than a target, the Solver add-in is the tool. Data Tables and Scenario Manager, on the same menu, explore what happens when inputs vary rather than solving for one.

Answer: C

Correct answer: Pressing the Esc key

Esc cancels the entry being typed and restores the cell’s previous contents, so it is the one action that does not commit data. Enter, Tab, an arrow key and the tick button on the formula bar all accept what you typed and confirm it into the cell.

Answer: A

Correct answer: =SUM(Sales)-A3

Sales is a name, and unless it has been defined the formula has nothing to sum, so Excel returns #NAME?. The others all compute - even the division by zero, which computes an error value rather than failing to be evaluated.

Answer: A

Correct answer: Splits the contents of one column into several columns

Text to Columns, on the Data tab, splits one column into several. It offers two methods: Delimited, splitting at a chosen character such as a comma, tab or space, and Fixed width, splitting at set positions. The typical use is separating full names, addresses or data pasted as a single line. It overwrites the columns to the right, so empty columns must be inserted first. Its third step is more useful than it looks: each resulting column can be assigned a data type, and a column can be marked Do not import to be discarded. Setting a column to Text is the standard way to stop Excel mangling account numbers and codes with leading zeros. It is also the quickest way to fix numbers stored as text - running it on a single column and clicking Finish converts them. Flash Fill, Ctrl + E, does the same job by example and is often faster; the reverse operation is CONCAT or the ampersand.

Answer: C

Correct answer: Rows and columns

A spreadsheet is a grid of numbered rows and lettered columns, and a cell is identified by the intersection of the two.

Answer: D

Correct answer: Excel will sort the whole table rather than the selected column alone

The warning appears when a single column is selected before sorting, and Excel detects data alongside it. Expand the selection sorts the entire table, keeping each row together. Continue with the current selection sorts that column alone, leaving every other column where it is. Choosing the second option is one of the most destructive mistakes possible in a spreadsheet: names become detached from their marks, and every row is silently wrong with nothing to show it. Because the damage is invisible, it is often discovered long afterwards. The safe habit is to click a single cell inside the table before sorting and let Excel select the whole region itself, rather than selecting a column first. Formatting the data as a proper Excel Table with Ctrl + T removes the risk entirely, since a table always sorts as a unit. Merged cells prevent sorting altogether and produce a different message.