D100 Introduction to Spreadsheets - Set 2 - Part 3

Correct Answer: #DIV/0!

The #DIV/0! error occurs whenever a formula attempts to divide by zero or by a blank cell, which Excel treats as zero. #NAME? indicates an unrecognized name or misspelled function, #REF! signals an invalid cell reference (often after a deletion), and #VALUE! points to the wrong type of argument. Only #DIV/0! is specific to a division-by-zero situation.

Correct Answer: A function name or defined name is misspelled

The #NAME? error appears when Excel does not recognize text in a formula, most commonly because a function name is misspelled, a defined name does not exist, or quotation marks are missing around text. A deleted referenced cell produces #REF!, using text where a number is expected produces #VALUE!, and a missing lookup value produces #N/A. The misspelled name is what triggers #NAME? specifically.

Correct Answer: Freeze Top Row

Freeze Top Row, found under the Freeze Panes menu on the View tab, keeps the first row locked in place so column headers stay visible as you scroll through the rest of the worksheet. Split Window divides the sheet into independently scrollable panes, Hide Rows removes rows from view entirely, and Group Rows creates a collapsible outline, none of which pin the header row the way Freeze Top Row does.

Correct Answer: =CONCAT(A1,B1)

The CONCAT function (and the older CONCATENATE) joins two or more text strings into one, so =CONCAT(A1,B1) combines the contents of both cells. LEN counts the number of characters in a single text value, LEFT extracts characters from the start of a string, and SUM adds numeric values. Only CONCAT is designed to merge separate text entries into a single string.

Correct Answer: sheet

The RIGHT function extracts a specified number of characters from the end of a text string, so =RIGHT("Spreadsheet",5) returns the last five characters, which are sheet. Extracting the first five characters would require LEFT and would return Sprea, while returning 11 would be the result of LEN counting the total characters. Therefore sheet is the correct output.

Correct Answer: Print Titles (Rows to repeat at top)

Print Titles, located on the Page Layout tab under Page Setup, includes a Rows to repeat at top option that prints your chosen header row on every page of the printout. Freeze Panes only affects on-screen scrolling, not printing. Wrap Text displays long entries on multiple lines within a cell, and Merge and Center combines cells, so Print Titles is the feature that controls repeated headers when printing.

Correct Answer: Scatter (XY) chart

A scatter (XY) chart plots pairs of numeric values as points on two axes, making it ideal for revealing the relationship or correlation between two continuous variables such as advertising spend and revenue. Pie charts show proportions of a whole, column charts compare discrete categories, and area charts emphasize cumulative magnitude over time, none of which display the point-by-point relationship between two numeric variables the way a scatter chart does.

Correct Answer: The relative value of each cell compared to the others

Color Scales apply a two- or three-color gradient across a range so that each cell's shade reflects its value relative to the others, for example green for high values grading to red for low values. The coloring is based purely on numeric value, not on alphabetical order, formula content, or print sequence, so the gradient serves as a quick visual heat map of relative magnitude.

Correct Answer: #N/A

When VLOOKUP (or a similar lookup function) searches for a value that does not exist in the lookup range, it returns #N/A, meaning not available. #DIV/0! results from dividing by zero, #NULL! appears when you use an incorrect intersection of ranges, and #REF! indicates an invalid reference. The #N/A error is specifically tied to a lookup value that has no match.

Correct Answer: =SUM(Sales[Region])

Excel Tables use structured references written as TableName[ColumnName], so =SUM(Sales[Region]) adds every value in the Region column of the Sales table and automatically adjusts if rows are added or removed. The other options use invalid syntax: Sales!Region resembles a sheet reference, Region.Sales is not valid Excel notation, and Sales:Region would be interpreted as a range operator between two names. Only the bracket notation is a correct structured reference.