D100 Introduction to Spreadsheets - Set 2 - Part 2

Correct Answer: The cell stores a formula, and 100 is the calculated result

The cell itself contains a formula (=50+50), which is what appears in the formula bar, while the worksheet displays the calculated result (100). This illustrates the key distinction between a formula and a value: the underlying formula performs the calculation, and Excel shows only the resulting value in the cell. If any inputs to a formula change, the displayed value updates automatically. Clicking a cell and viewing the formula bar is the standard way to tell whether a cell holds a raw value or a live formula.

Correct Answer: The caret (^)

Excel uses the caret symbol (^) to raise a number to a power, so =2^3 calculates 2 to the third power and returns 8. The asterisk (*) is used for multiplication, the forward slash (/) for division, and the percent sign (%) applies a percentage to a value. In the order of operations, exponentiation is evaluated before multiplication and division, so understanding which operator does what is important for building accurate formulas.

Correct Answer: =AVERAGE(C1:C10)

The AVERAGE function computes the arithmetic mean by adding all numeric values in a range and dividing by the count of those values, so =AVERAGE(C1:C10) is correct. MEAN and AVG are not valid Excel function names, even though AVG resembles the abbreviation used in some other programs. MEDIAN is a real function, but it returns the middle value of a sorted list rather than the average. For a standard mean calculation, AVERAGE is the proper choice.

Correct Answer: Low

The IF function evaluates a logical test and returns the second argument when the test is true and the third argument when it is false. Here the test A1>10 checks whether 7 is greater than 10, which is false, so Excel returns the value_if_false argument, Low. If A1 had contained a number greater than 10, the function would have returned High instead. The IF function is the fundamental tool for making decisions in a spreadsheet based on the contents of a cell.

Correct Answer: The cell where column D and row 7 intersect

A standard cell reference combines the column letter followed by the row number, so D7 identifies the single cell located where column D and row 7 intersect. Columns are always labeled with letters and rows with numbers, so the letter always comes first. It does not represent a range, and it has nothing to do with worksheet numbering. This column-letter-then-row-number convention is the basis of the A1 reference style used throughout Excel and Google Sheets.

Correct Answer: It adjusts automatically based on the new position

A relative reference, such as A1 with no dollar signs, adjusts automatically when a formula is copied to a new cell. For example, if a formula in B2 refers to A2 and you copy it down to B3, the reference updates to A3 to maintain the same relative position. This automatic adjustment is what makes it possible to write one formula and fill it down a column efficiently. To prevent this behavior, you would add dollar signs to create an absolute or mixed reference.

Correct Answer: A1:C3

A rectangular block of cells is referenced by naming the top-left cell and the bottom-right cell separated by a colon, so A1:C3 defines a range that includes all cells from column A through column C and rows 1 through 3 (nine cells total). The colon is the range operator in Excel. A comma is a union operator that refers to separate cells rather than a continuous block, and neither the hyphen nor a double-dot is used for ranges in Excel.

Correct Answer: =MIN(A1:A20)

The MIN function returns the lowest numeric value in a range, so =MIN(A1:A20) correctly finds the smallest number among those cells. LOW and LEAST are not valid Excel functions. SMALL is a genuine function, but like LARGE it requires a second argument indicating which ranked position you want (for example =SMALL(A1:A20,1)); used without that argument it produces an error. For simply identifying the single lowest value, MIN is the appropriate and simplest function.

Correct Answer: COUNTA

The COUNTA function counts every cell in a range that is not empty, regardless of whether the content is a number, text, date, or logical value. This distinguishes it from COUNT, which counts only numeric cells. COUNTBLANK does the opposite by counting empty cells, and COUNTIF counts cells that meet a specified condition. When you need a total of all filled-in cells in a range including labels and text, COUNTA is the correct function to use.

Correct Answer: 5, because the addition inside the parentheses is calculated first

Parentheses have the highest priority in the order of operations, so Excel evaluates whatever is inside them before doing anything else. In =(4+6)/2, the addition 4+6 is calculated first to give 10, and then that result is divided by 2 to produce 5. Without the parentheses, the formula =4+6/2 would divide 6 by 2 first (giving 3) and then add 4, returning 7. This shows how parentheses let you control and override the default order of operations.

Correct Answer: Line chart

A line chart connects data points with a continuous line, making it ideal for displaying trends across evenly spaced intervals such as months in a year. It emphasizes the direction and rate of change over time. Pie and doughnut charts show parts of a whole at a single point in time, and a scatter chart without lines is used to show relationships between two numeric variables rather than a time-based trend.

Correct Answer: From A to Z

Ascending order arranges text alphabetically from A to Z, while numbers go from smallest to largest and dates from oldest to newest. Descending order reverses this, sorting text from Z to A. Sorting by cell color is a separate option you must select explicitly, and sorting never reshuffles data randomly because it always follows a defined order key.

Correct Answer: Custom AutoFilter (Number Filters)

The Custom AutoFilter, accessed through Number Filters in the filter drop-down, allows you to combine two criteria using And/Or logic, such as greater than 100 AND less than 500. Sort Ascending only reorders rows and does not hide any data. Freeze Panes keeps rows or columns visible while scrolling, and Format Painter copies formatting, neither of which filters values.

Correct Answer: Percentage format

The Percentage format multiplies the underlying value by 100 and appends a percent sign, so 0.75 is displayed as 75%. Currency and Accounting formats add a currency symbol and typically two decimal places to a monetary value. Scientific format expresses numbers in exponential notation, such as 7.50E-01, and none of these three convert the decimal to a percent display.

Correct Answer: Top/Bottom Rules

Top/Bottom Rules include options such as Top 10 Items, Top 10%, Bottom 10 Items, and Above Average, which directly highlight the highest or lowest values in a range. Data Bars draw proportional bars inside each cell, Color Scales shade cells along a gradient based on relative value, and Icon Sets add symbols by category, but none of those specifically isolate the top 10 items the way Top/Bottom Rules do.

Correct Answer: Formulas and formatting automatically expand as new rows are added

An Excel Table is a structured, dynamic object: when you add a new row at the bottom, the table automatically extends its formatting, banded rows, and any calculated column formulas to that new row. Tables do not lock data from editing, do not remove formulas, and actually make sorting and filtering easier through built-in header drop-down buttons, so the automatic expansion is the defining benefit.

Correct Answer: Values area

The Values area is where numeric fields are placed to be summarized using functions such as Sum, Count, or Average. The Rows and Columns areas define the categories that organize the data along each axis, and the Filters area lets you restrict which records the entire PivotTable displays. Only the Values area actually performs the aggregation calculation on the data.

Correct Answer: Date with a between criteria

Choosing the Date validation type with a between comparison lets you set a start and end date so only entries within that span, such as January 1 to December 31 of the current year, are accepted. A List restricts entries to items you predefine, Text length limits the number of characters, and Any value places no restriction at all, so the Date between option is the correct choice for a date range.

Correct Answer: VLOOKUP

VLOOKUP (Vertical Lookup) searches down the leftmost column of a table array for a match and returns a value from a specified column to the right in the same row. HLOOKUP performs the same task horizontally across a top row rather than down a column. INDEX alone cannot search for a match without MATCH, and LEN counts characters in text, so VLOOKUP is the function designed for this vertical lookup.

Correct Answer: It can return values from columns to the left of the lookup column

XLOOKUP can return a result from a column positioned either to the left or right of the lookup column because it accepts separate lookup and return arrays, overcoming VLOOKUP's limitation of only looking rightward. XLOOKUP also defaults to an exact match and does not require the data to be sorted. Therefore the ability to look up in any direction, including leftward, is a key improvement over VLOOKUP.