MS Excel Questions and Answers

Answer: D

Correct answer: Ctrl + Shift + %

Ctrl + Shift + % applies the percentage format, displaying the value multiplied by a hundred with a per cent sign and no decimal places. The number-format shortcuts share the pattern Ctrl + Shift with a symbol key, and are worth learning together: ~ for General, ! for number with two decimals and thousands separators, $ for currency, % for percentage, ^ for scientific, # for date and @ for time. Each symbol suggests its format, which makes the set easier to remember than it looks. The essential point about percentage format is that it changes only the display. A cell holding 0.25 shown as 25% still contains 0.25, so a formula multiplying by it works correctly. Typing 25 into a cell already formatted as a percentage gives 2500%, which is the usual surprise. Ctrl + 1 opens the full Format Cells dialogue for anything the shortcuts do not cover.

Answer: B

Correct answer: Adds the values in cells A1 through A10

=SUM(A1:A10) adds the values in every cell of the range A1 to A10. The colon is a range operator, meaning through, so A1:A10 is ten cells. Had a comma been used - =SUM(A1,A10) - only those two cells would be added, which is the distinction the fourth option tests. The two operators can be combined: =SUM(A1:A10,C1:C10) adds both ranges. SUM ignores text and blank cells rather than reporting an error, which is why it is safe to apply to a column that has a heading. The related functions are worth knowing as a family: AVERAGE for the mean, COUNT for numeric entries, COUNTA for non-empty cells of any kind, MAX and MIN for the extremes. Alt + = inserts a SUM formula with the range Excel guesses, which is usually the column above.

Answer: C

Correct answer: counts the cells in a range that meet a given condition

COUNTIF counts the cells in a range that satisfy one condition: =COUNTIF(A1:A100,"Delhi") counts how many entries are Delhi, and =COUNTIF(B1:B100,">50") counts the values above fifty. The condition is given as text, which is why comparison operators must be inside quotation marks. Wildcards are allowed: ? stands for one character and * for any number, so "D*" matches every entry beginning with D. Its relatives complete the family. SUMIF adds rather than counts. AVERAGEIF averages. The plural forms - COUNTIFS, SUMIFS, AVERAGEIFS - take several conditions at once, and note that SUMIFS reverses the argument order, putting the sum range first. The plain COUNT counts numeric entries only, COUNTA counts any non-empty cell, and COUNTBLANK counts the empty ones.

Answer: A

Correct answer: Summarises large amounts of data by grouping and aggregating it interactively

A Pivot Table summarises a large table by grouping rows and columns and aggregating the values - totals, counts, averages - and it can be rearranged simply by dragging fields between areas. It has four areas. Rows and Columns set the grouping; Values holds what is aggregated; Filters restricts what is included. A single source table can be reorganised many ways without altering the data, which is what makes it so useful for exploration. The source must be well formed: a single header row, no blank rows or columns, and one kind of thing per row. Most difficulties with pivot tables trace back to a source that is not laid out that way. A pivot table does not update automatically when the source changes and must be refreshed. Basing it on a proper Excel Table, rather than a fixed range, means new rows are picked up on refresh. The second option describes Transpose, an option in Paste Special.

Answer: C

Correct answer: XFD1048576

XFD1048576 is the last cell of a modern worksheet: column XFD is the 16,384th and row 1,048,576 is the last. A cell address is always column letters followed by row number, which disqualifies 1A. Rows are numbered from 1, so A0 does not exist. And column letters run A to Z, then AA to ZZ, then AAA to XFD - three letters at most - so AAAA5 is beyond the grid. The lettering is a base-26 system: after Z comes AA, and after AZ comes BA. The 27th column is AA and the 703rd is AAA. Both limits are powers of two, \( 2^{14} \) columns and \( 2^{20} \) rows, raised from 256 and 65,536 when the .xlsx format arrived in Excel 2007. Ctrl + Right Arrow from an empty row travels to column XFD, and Ctrl + Down Arrow to the last row, which is the quickest way to see the boundary.

Answer: D

Correct answer: Insert a new text box and select Insert >> slide Number

The slide number is a field, and a field must sit in a text box or placeholder. So a box is created first and the number inserted into it; the number then updates as slides are added or reordered.

Answer: D

Correct answer: Pie chart

A pie chart shows parts of a whole, each slice representing a category’s share of the total. It works only under conditions worth remembering: the values must be parts of a single total, must all be positive, and should be few - beyond about five or six slices the differences become impossible to judge. Human eyes compare angles poorly, so a bar chart is usually clearer even when a pie is technically valid. The other types answer different questions. A line chart shows change over time. A bar or column chart compares values across categories. A scatter chart shows the relationship between two numeric variables and is the right choice for correlation. An area chart is a line chart with the region below filled, emphasising cumulative magnitude. Choosing the chart type is a matter of what question is being asked, not of appearance. Alt + F1 inserts a default chart from the selected data.

Answer: D

Correct answer: A formula is attempting to divide by zero or by an empty cell

#DIV/0! appears when a formula divides by zero or by a cell that is empty, since an empty cell is treated as zero in arithmetic. The usual remedy is to guard the calculation: =IFERROR(A1/B1,"") returns a blank instead of the error, or =IF(B1=0,"",A1/B1) tests the divisor directly. IFERROR is more concise but hides every error, not only this one, so it should be used knowing that. The other error values each have their own cause and are worth learning as a set. ##### means the column is too narrow - widening it fixes it. #REF! means a reference points to a cell that has been deleted. #NAME? means a function name is misspelt or a text value was typed without quotation marks. #VALUE! means an argument is of the wrong type, such as text where a number is expected. #N/A means a lookup found nothing.

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