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

What does the UNIQUE function do in Excel 365?

Answer: Extracts distinct values from a range as a dynamic array

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

What does the TEXT function do?

Answer: Formats a number as text using a format code

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

How does AVERAGEIFS calculate its result?

Answer: Averages values where all corresponding criteria are met

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

What is the difference between relative and absolute cell references?

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

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

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?

Answer: Only Create Connection

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

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

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.

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?

Answer: Click Refresh All on the Data tab

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

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

Answer: Data

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

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

Answer: M

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

Which keyboard shortcut applies bold formatting to the selected cells?

Answer: Ctrl+B

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

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

Answer: Conditional Formatting

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

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

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.

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

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.

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

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.

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

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.

In VBA, what does 'Option Explicit' enforce?

Answer: All variables must be declared before use

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

What does Application.Evaluate do in VBA?

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.

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

Answer: Place code in the Workbook_Open event of ThisWorkbook

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

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

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.

What does the Analysis ToolPak add-in provide in Excel?

Answer: Statistical and engineering analysis functions

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