EXCEL Practice Test: Video 1 Answers (25 Questions) Flashcards

What does VLOOKUP's fourth argument (range_lookup) control?

Answer: Exact match (FALSE) or approximate match (TRUE)

FALSE requires exact match; TRUE allows approximate match from sorted data.

What replaced CONCATENATE and why?

Answer: CONCAT and TEXTJOIN; they accept ranges and add delimiters

CONCAT accepts ranges; TEXTJOIN adds delimiters and can skip empty cells.

How does INDEX work with a single range?

Answer: Returns the value at a specified row position

INDEX returns the value at a given row and optional column position within a range.

Which VBA statement redirects execution when an error occurs?

Answer: On Error GoTo

On Error GoTo directs VBA to jump to a labeled line when a runtime error occurs.

What is the purpose of IFERROR in Excel?

Answer: Returns a specified value if a formula evaluates to an error

IFERROR returns an alternative value if the formula results in any error type.

How do you create a named range that automatically expands?

Answer: Use OFFSET and COUNTA in the name definition

Combining OFFSET with COUNTA creates a dynamic named range that grows with your data.

What does TEXTJOIN do that CONCATENATE cannot?

Answer: Joins text with a delimiter and can ignore empty cells

TEXTJOIN joins text using a specified delimiter and can skip empty cells.

You have a Sales query and a Products query that share a ProductID column. Which Power Query operation adds product details to each sales row?

Answer: Merge Queries

Merge performs a join between two queries on matching key columns.

What does the Group By transformation with the Sum operation on an Amount column produce?

Answer: One row per group with the total Amount

Group By aggregates rows into one summary row per unique group value.

Which Power Query feature lets you combine all CSV files in a folder into a single table with one query?

Answer: From Folder with Combine Files

The Folder connector plus Combine Files builds a sample transform and applies it to every file.

In the Power Query Editor, which command adds a column that increments 1, 2, 3, and so on for each row?

Answer: Index Column

Index Column creates a sequential numeric column starting from 0 or 1.

Which Excel tool applies different formatting to cells automatically based on the values they contain?

Answer: Conditional Formatting

Conditional Formatting changes a cell's appearance (color, font, borders) dynamically based on rules tied to the cell's value.

What error does Excel display when a formula attempts to divide by zero?

Answer: #DIV/0!

#DIV/0! appears whenever a formula divides a number by zero or by an empty cell.

Let's say that cell A1 shows the value "12000.7789". How should this number be rounded to the nearest integer using the following formula?

Answer: =ROUND(A1,0)

The `ROUND` function in Excel is used to round a number to a specified number of decimal places. To round a number to the nearest integer, you specify `0` as the number of decimal places. This instructs Excel to round the value in cell A1 to the closest whole number.

Which keyboard shortcut inserts the current date as a static value in a cell?

Answer: Ctrl+;

Ctrl+; inserts today's date as a fixed value that does not change when the workbook is recalculated.

Which function returns the current date and time that updates each time the worksheet recalculates?

Answer: =NOW()

NOW() returns the current date and time as a serial number and refreshes every time Excel recalculates.

Which Excel function returns a reference to a range specified by a text string?

Answer: INDIRECT

INDIRECT converts a text string into an actual cell reference for dynamic reference building.

How do you create a spilling array formula in Excel 365?

Answer: Simply press Enter — dynamic arrays spill automatically

In Excel 365, dynamic array formulas automatically spill results into adjacent cells when you press Enter.

What does the FORMULATEXT function do?

Answer: Returns the formula in a cell as a text string

FORMULATEXT returns the formula in a referenced cell as text, useful for documentation and auditing.

What does the VBA keyword 'Dim' stand for?

Answer: Dimension

Dim stands for 'Dimension' and is used to declare variables in VBA.