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

Question 1: What does the UNIQUE function do in Excel 365?

Removes duplicate formatting
Extracts distinct values from a range as a dynamic array (Correct answer)
Merges duplicate rows
Counts unique values

Correct answer: Extracts distinct values from a range as a dynamic array

UNIQUE returns a dynamic array of distinct values, removing duplicates.

Question 2: What does the TEXT function do?

Compares text strings
Finds text in a cell
Formats a number as text using a format code (Correct answer)
Converts text to numbers

Correct answer: Formats a number as text using a format code

TEXT converts a numeric value to text with a specific format.

Question 3: How does AVERAGEIFS calculate its result?

Averages top and bottom only
Averages all criteria values
Averages values where all corresponding criteria are met (Correct answer)
Returns average of COUNTIFS and SUMIFS

Correct answer: Averages values where all corresponding criteria are met

AVERAGEIFS averages values in a range only where ALL corresponding criteria are satisfied.

Question 4: What is the difference between relative and absolute cell references?

Absolute are faster
Relative adjust when copied, absolute (with $) stay fixed (Correct answer)
No functional difference
Relative use letters, absolute use numbers

Correct answer: Relative adjust when copied, absolute (with $) stay fixed

Relative references adjust when copied; absolute references ($A$1) remain fixed.

Question 5: Which option should you choose when you want a query's results available for other queries but do not want a table placed on any worksheet?

Load to Data Model only
Load to PivotTable Report
Only Create Connection (Correct answer)
Load to Table

Correct answer: Only Create Connection

Connection-only queries exist without loading output to the workbook.

Question 6: In the Power Query Editor, what happens when you delete a step from the middle of the Applied Steps list?

Only that step is removed and later steps are re-evaluated (Correct answer)
All steps after it are automatically deleted
The query is reset to its source
The step is hidden but still runs

Correct answer: Only that step is removed and later steps are re-evaluated

Removing a step removes only that step; subsequent steps still run and may error if they depended on it.

Question 7: After loading a query to a worksheet, the source CSV file receives new rows. What is the quickest way to bring those rows into Excel?

Manually copy the new rows into the table
Click Refresh All on the Data tab (Correct answer)
Re-import the file with a new query
Delete the query and recreate it

Correct answer: Click Refresh All on the Data tab

Refreshing re-runs the saved steps against the current source data.

Question 8: In Excel, which ribbon tab contains the Get & Transform Data group used to launch Power Query?

Home
Data (Correct answer)
Formulas
Insert

Correct answer: Data

Power Query connectors live in the Get & Transform Data group on the Data tab.

Question 9: Which language does Power Query use to record the steps you apply in the Power Query Editor?

VBA
DAX
SQL
M (Correct answer)

Correct answer: M

Every applied step is written in the M formula language, visible in the Advanced Editor.

Question 10: Which keyboard shortcut applies bold formatting to the selected cells?

Ctrl+F
Ctrl+B (Correct answer)
Ctrl+U
Ctrl+I

Correct answer: Ctrl+B

Ctrl+B toggles bold formatting on or off for the selected text or cells.

Question 11: Which Excel feature allows you to apply consistent formatting across multiple cells based on their values?

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

Correct answer: Conditional Formatting

Conditional Formatting automatically applies formatting rules based on cell values, such as highlighting cells above a certain threshold.

Question 12: Which view in Excel is best suited for setting page breaks and print areas before printing?

Full Screen View
Page Break Preview (Correct answer)
Normal View
Page Layout View

Correct answer: Page Break Preview

Page Break Preview shows exactly where pages will break when printed and allows you to drag page break lines to customize the print layout.

Question 13: Which Excel function returns the current date and time and updates automatically when the worksheet recalculates?

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

Correct answer: NOW()

NOW() returns the current date and time as a serial number, updating each time the worksheet recalculates. TODAY() returns only the date without the time component.

Question 14: What key combination on the keyboard locks cell references in a formula?

CTRL
ALT
F4 (Correct answer)
F2

Correct answer: F4

The F4 key is a powerful shortcut in Excel used to cycle through different types of cell references (relative, absolute, mixed) when editing a formula. Pressing F4 repeatedly on a cell reference (e.g., A1) will add or remove dollar signs ($) to lock either the row, column, or both, making it an absolute reference ($A$1) that does not change when copied.

Question 15: What is the difference between a VBA Sub and Function procedure?

Subs run faster
Functions cannot modify cell values
Subs take arguments but Functions cannot
Functions return a value while Subs do not (Correct answer)

Correct answer: Functions return a value while Subs do not

Functions return a value and can be used in worksheet formulas, while Subs perform actions without returning a value.

Question 16: In VBA, what does 'Option Explicit' enforce?

Strict data type checking
All functions must return a value
All procedures must be public
All variables must be declared before use (Correct answer)

Correct answer: All variables must be declared before use

Option Explicit requires every variable to be declared with Dim before use, preventing bugs from typos.

Question 17: What does Application.Evaluate do in VBA?

Evaluates a string as a formula and returns the result (Correct answer)
Tests if a cell contains an error
Compares two ranges
Checks if VBA syntax is valid

Correct answer: Evaluates a string as a formula and returns the result

Application.Evaluate takes a string containing a formula and evaluates it as if entered in a cell.

Question 18: How can you make a VBA macro run when a workbook opens?

Set to 'Auto-execute' in Properties
Place code in the Workbook_Open event of ThisWorkbook (Correct answer)
Name it 'Auto_Run'
Save in Personal.xlsb

Correct answer: Place code in the Workbook_Open event of ThisWorkbook

Code in the Workbook_Open event automatically executes whenever the workbook is opened.

Question 19: What is the purpose of Excel's 'Group' feature in the Data tab?

Linking workbooks together
Creating collapsible outline levels for rows or columns (Correct answer)
Grouping worksheets together
Merging cells in a range

Correct answer: Creating collapsible outline levels for rows or columns

The Group feature creates outline levels allowing you to collapse and expand rows or columns for easier navigation.

Question 20: What does the Analysis ToolPak add-in provide in Excel?

Additional chart types
Statistical and engineering analysis functions (Correct answer)
Database connectivity tools
Macro recording capabilities

Correct answer: Statistical and engineering analysis functions

The Analysis ToolPak provides data analysis tools for statistical and engineering analysis, including regression, histograms, and ANOVA.

Question 21: Which function in Excel performs a two-dimensional lookup by matching both a row and column value?

LOOKUP
HLOOKUP
INDEX/MATCH combination (Correct answer)
VLOOKUP

Correct answer: INDEX/MATCH combination

INDEX combined with two MATCH functions can perform a two-dimensional lookup at the intersection of a matched row and column.

Question 22: What does the 'What-If Analysis' group in Excel's Data tab include?

Only Goal Seek
Solver and Data Table only
Scenario Manager, Goal Seek, and Data Table (Correct answer)
Forecast Sheet and Scenario Manager only

Correct answer: Scenario Manager, Goal Seek, and Data Table

The What-If Analysis group contains Scenario Manager, Goal Seek, and Data Table.

Question 23: Which chart shows the relationship between two numerical variables?

Pie chart
Scatter (XY) chart (Correct answer)
Area chart
Bar chart

Correct answer: Scatter (XY) chart

Scatter charts plot data points using X and Y coordinates to reveal relationships.

Question 24: What is the purpose of error bars in charts?

Indicating formula errors
Highlighting incorrect data
Showing when data exceeds boundaries
Displaying variability or uncertainty of data points (Correct answer)

Correct answer: Displaying variability or uncertainty of data points

Error bars represent variability, uncertainty, or margin of error in data.

Question 25: What is a sparkline in Excel?

An animated chart effect
A chart template
A type of conditional formatting
A tiny chart within a single cell showing data trends (Correct answer)

Correct answer: A tiny chart within a single cell showing data trends

Sparklines are miniature charts that fit inside a single cell.

EXCEL Practice Test: Video 2 Answers (25 Questions)

What does the UNIQUE function do in Excel 365?

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