EXCEL Practice Test: Video 1 Answers (25 Questions) — Questions and Answers

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

Include hidden rows
Columns to search
Lookup direction
Exact match (FALSE) or approximate match (TRUE) (Correct answer)

Correct answer: Exact match (FALSE) or approximate match (TRUE)

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

Question 2: What replaced CONCATENATE and why?

SEARCH; it finds patterns
SUBSTITUTE; it removes text
CONCAT and TEXTJOIN; they accept ranges and add delimiters (Correct answer)
TEXTSPLIT; it splits text

Correct answer: CONCAT and TEXTJOIN; they accept ranges and add delimiters

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

Question 3: How does INDEX work with a single range?

Returns the value at a specified row position (Correct answer)
Returns the cell address
Indexes alphabetically
Creates an index of values

Correct answer: Returns the value at a specified row position

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

Question 4: Which VBA statement redirects execution when an error occurs?

On Error GoTo (Correct answer)
Try...Catch
Catch Exception
Error.Handle

Correct answer: On Error GoTo

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

Question 5: What is the purpose of IFERROR in Excel?

Prevents incorrect formula entry
Logs errors to a worksheet
Checks if a formula contains IF
Returns a specified value if a formula evaluates to an error (Correct answer)

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

Question 6: How do you create a named range that automatically expands?

Use OFFSET and COUNTA in the name definition (Correct answer)
Named ranges cannot expand
Set the range to 'Dynamic' in Name Manager
Use Define Name with a fixed range

Correct answer: Use OFFSET and COUNTA in the name definition

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

Question 7: What does TEXTJOIN do that CONCATENATE cannot?

Joins across workbooks
Converts numbers to text first
Joins text with formatting
Joins text with a delimiter and can ignore empty cells (Correct answer)

Correct answer: Joins text with a delimiter and can ignore empty cells

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

Question 8: 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?

Append Queries
Merge Queries (Correct answer)
Group By
Transpose

Correct answer: Merge Queries

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

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

A running total on each row
One row per group with the total Amount (Correct answer)
A percentage of the grand total
A sorted copy of the original table

Correct answer: One row per group with the total Amount

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

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

From Table/Range
From Web
From Folder with Combine Files (Correct answer)
Data Validation

Correct answer: From Folder with Combine Files

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

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

Column From Examples
Custom Column
Conditional Column
Index Column (Correct answer)

Correct answer: Index Column

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

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

Cell Styles
Conditional Formatting (Correct answer)
Format Painter
Data Validation

Correct answer: Conditional Formatting

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

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

#REF!
#NULL!
#DIV/0! (Correct answer)
#VALUE!

Correct answer: #DIV/0!

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

Question 14: 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?

=MROUND(A1,10)
=ROUND(A1,0) (Correct answer)
=ROUND(A1,1)
=MROUND(A1,100)

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

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

Ctrl+Shift+T
Ctrl+; (Correct answer)
Ctrl+D
Ctrl+Shift+:

Correct answer: Ctrl+;

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

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

=DATE()
=TODAY()
=NOW() (Correct answer)
=TIME()

Correct answer: =NOW()

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

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

ADDRESS
INDIRECT (Correct answer)
INDEX
OFFSET

Correct answer: INDIRECT

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

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

Press Ctrl+Shift+Enter
Select the output range first
Wrap in ARRAYFORMULA()
Simply press Enter — dynamic arrays spill automatically (Correct answer)

Correct answer: Simply press Enter — dynamic arrays spill automatically

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

Question 19: What does the FORMULATEXT function do?

Creates a formula from text
Converts a formula result to text
Returns the formula in a cell as a text string (Correct answer)
Evaluates text as a formula

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

Question 20: What does the VBA keyword 'Dim' stand for?

Direct Input Method
Diminish
Dimension (Correct answer)
Data In Memory

Correct answer: Dimension

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

Question 21: What is the purpose of the LAMBDA function in Excel?

Creating lambda expressions for Power Query
Generating random numbers
Defining custom reusable functions without VBA (Correct answer)
Creating anonymous functions in VBA

Correct answer: Defining custom reusable functions without VBA

LAMBDA lets you create custom reusable functions using Excel formulas alone, without VBA.

Question 22: Which Excel tool allows you to test multiple input values and see their effect on formula results simultaneously?

Scenario Manager
Solver
Goal Seek
Data Table (Correct answer)

Correct answer: Data Table

Data Tables allow you to substitute multiple input values into formulas and display all results in a table format.

Question 23: In Excel's Solver tool, what is a 'constraint'?

The formula you want to maximize
A limitation or requirement that the solution must satisfy (Correct answer)
The cell containing the objective function
A type of circular reference

Correct answer: A limitation or requirement that the solution must satisfy

Constraints in Solver are conditions that restrict possible solutions, such as requiring a value to be non-negative or within a range.

Question 24: Which Excel feature combines data from multiple ranges into a single summary?

CONCATENATE function
Consolidate tool (Correct answer)
Merge & Center
Group feature

Correct answer: Consolidate tool

The Consolidate tool on the Data tab combines data from multiple ranges using functions like SUM or AVERAGE.

Question 25: How does Excel's Forecast Sheet feature generate predictions?

Using polynomial curve fitting
Using exponential smoothing algorithms (ETS) (Correct answer)
Using simple linear regression only
Using moving averages

Correct answer: Using exponential smoothing algorithms (ETS)

Excel's Forecast Sheet uses the AAA version of the ETS algorithm to detect seasonality and generate predictions with confidence intervals.

EXCEL Practice Test: Video 1 Answers (25 Questions)

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

What Practice Test Geeks adds beyond an AI chat for Excel

An AI chatbot can quiz you, but it can't reproduce the exam conditions or keep your record. Here you get:

A timed, full-length Excel mock exam — 35 questions in 50 minutes with a 70% pass mark, modeled on the official exam's question count and time limit, followed by a score report broken down by exam domain.
A fixed Excel question bank: the same test every attempt, so your score is comparable from one try to the next instead of changing with every regenerated quiz.
Progress saved on your device: completed tests, scores and missed questions stay in My Progress, so you can retake only what you got wrong.
A printable Excel cheat sheet you can print or save as a PDF.
Excel flashcards built from the same question bank.

Start the Timed Excel Mock Exam →

These practice questions were written with AI tools. They are not real exam questions. Exam facts are checked against the official sources listed below. Not reviewed by a subject-matter expert. Editorial policy

Sources & references

Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy

learn.microsoft.com — Retake policy, test delivery, time limit, eligibility · verified Oct 2026
certiport.pearsonvue.com — Exam format, score validity, passing score, score release, exam information · verified Oct 2026