MS Excel Questions and Answers

Answer: A

Correct answer: Nested Function

A function used as an argument of another function is nested - for example =IF(SUM(A1:A5)>100,"High","Low"), where SUM is nested inside IF. Excel evaluates the inner function first and passes its result outward. SUM, ROUND and the text functions are individual functions, not a category.

Answer: C

Correct answer: Spreadsheets

A spreadsheet lays data out in a grid of lettered columns and numbered rows and lets each cell hold either a value or a formula that refers to other cells - exactly the ruled columnar paper an accountant works on, with the arithmetic done for you. Word processing handles running text, a database stores structured records for querying, and graphics software draws images; none of them mimic the worksheet layout.

Answer: B

Correct answer: Appears t the left of the formula bar

The Name box sits at the left of the formula bar. It shows the reference of the active cell and can be typed into to jump to a cell or to name a range.

Answer: A

Correct answer: =

Every formula begins with =. Excel also accepts + and − at the start for the sake of old Lotus users, converting them to a formula, but the equals sign is the rule.

Answer: B

Correct answer: Joins several text values into one

CONCATENATE joins text values end to end, and the ampersand does the same more briefly: =A1&" "&B1 joins a first and last name with a space between. The space must be supplied explicitly, since nothing is added automatically. Newer versions provide CONCAT and TEXTJOIN. TEXTJOIN is the more capable, taking a delimiter and an option to ignore empty cells, so a list can be assembled without stray separators. The reverse operation is Text to Columns on the Data tab, which splits one column into several at a delimiter or at fixed widths - the second option here. Flash Fill, Ctrl + E, does the same by example and is often quicker. The related text functions are worth knowing: TRIM removes surplus spaces, UPPER, LOWER and PROPER change case, LEFT, RIGHT and MID extract parts, and LEN counts characters.

Answer: C

Correct answer: It can contain many sheets including worksheets and chart sheets

A workbook is a book of sheets: several worksheets and chart sheets held in one file, able to refer to each other's cells.

Answer: B

Correct answer: Provides a quick way to view the result of an arithmetic operation on a range of cells

AutoCalculate shows the sum, average, count, maximum or minimum of the selected cells on the status bar. It only displays the result; nothing is written into the sheet.

Answer: B

Correct answer: change the appearance of cells automatically according to their values

Conditional Formatting applies formatting that depends on a cell’s value, and updates itself whenever the value changes. It is on the Home tab. The built-in rules cover most needs: highlighting cells above or below a figure, marking the top or bottom ten, and flagging duplicate values. Three visual styles turn a column of numbers into something readable at a glance - data bars draw an in-cell bar proportional to the value, colour scales shade from one colour to another across the range, and icon sets add arrows or traffic lights. The most flexible option is a rule driven by a formula, which allows one cell to be formatted according to another. Using a formula such as =$C2="Overdue" with the range set to the whole row highlights the entire row when that column says Overdue - and note the mixed reference, which is what makes it work across columns. Rules are managed, reordered and cleared from the Manage Rules dialogue.

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: the number of characters in a text value

LEN counts the characters in a text value, including spaces. =LEN("New Delhi") returns 9. It is used for validation - checking that a code or an account number has the right number of digits - and as a component of longer text formulas, since extracting part of a string usually requires knowing its length. Its most useful diagnostic role is finding stray spaces: comparing =LEN(A1) with =LEN(TRIM(A1)) shows at once whether a cell carries surplus spaces that would make a lookup fail. On numbers it counts the digits of the underlying value, not the formatted display, so a number shown to two decimal places may return a different count from the one expected. The related text functions are LEFT, RIGHT and MID for extraction, FIND and SEARCH for locating a substring - FIND being case-sensitive and SEARCH not - and SUBSTITUTE for replacement.