To ensure a cell reference remains fixed when a formula is copied to other cells, an absolute reference uses dollar signs before both the column letter and row number.
Missing Word
absolute
An absolute reference prevents a cell's column or row from changing when the formula is moved or copied.
Question 2
The SUMIF function is used to add up numbers in a range that meet a specific criterion.
Missing Word
SUMIF
The SUMIF function conditionally sums cells based on a single condition.
Question 3
To find a value in the first column of a table array and return a corresponding value from a specified column to the right, one would typically use the VLOOKUP function.
Missing Word
VLOOKUP
VLOOKUP is designed to search for a value in the leftmost column of a table and return a value from a specified column in the same row.
Question 4
A pivot table is an interactive tool used to quickly summarize and analyze large datasets, allowing users to rearrange and aggregate data in various ways.
Missing Word
pivot
Pivot tables enable dynamic summarization and analysis of data, making it easier to identify patterns and trends.
Question 5
To display trends over time or categories, such as monthly sales figures, a line chart is an effective visualization choice.
Missing Word
line
Line charts are best suited for showing changes or trends in data over a continuous period or ordered categories.
Question 6
Applying conditional formatting allows cells to automatically change their appearance, such as color or font, based on specified rules or conditions.
Missing Word
conditional
Conditional formatting automatically applies formatting to cells that meet certain criteria, highlighting important data.
Question 7
The Remove Duplicates feature in Excel helps to clean a dataset by identifying and deleting rows where all selected columns have identical values.
Missing Word
Duplicates
The Remove Duplicates tool eliminates redundant rows from a dataset, ensuring each record is unique based on specified columns.
Question 8
When a formula attempts to divide a number by zero, Excel displays the #DIV/0! error.
Missing Word
#DIV/0!
This error indicates that a formula is trying to divide by an empty cell or a cell containing zero.
Question 9
For more flexible lookups than VLOOKUP, especially when the lookup column is not the leftmost, the combination of INDEX and MATCH functions is often preferred.
Missing Word
MATCH
The MATCH function returns the relative position of an item in a range, which the INDEX function can then use to retrieve a value from another range.
Question 10
A slicer provides interactive buttons that allow users to filter data in a pivot table or pivot chart quickly and visually.
Missing Word
slicer
Slicers offer an intuitive way to filter data in pivot tables and charts, making reports more interactive.
Question 11
To display a value as a percentage, such as 0.25 as 25%, one applies a specific number format.
Missing Word
number
Number formats control how numerical values are displayed in cells without changing their underlying value.
Question 12
By default, when a formula is copied to an adjacent cell, its cell references adjust automatically, which is known as a relative reference.
Missing Word
relative
Relative references change when a formula is copied or filled to other cells, maintaining the same relative position to the new formula location.
Question 13
To remove extra spaces from text, leaving only single spaces between words and no leading or trailing spaces, the TRIM function is highly useful.
Missing Word
TRIM
The TRIM function cleans up text by removing all spaces from text except for single spaces between words.
Question 14
The XLOOKUP function is a modern and versatile lookup function that can search in any direction and return an exact match by default, making it superior to older lookup functions in many scenarios.
Missing Word
XLOOKUP
XLOOKUP offers more flexibility and features than VLOOKUP or HLOOKUP, including searching in either direction and an exact match by default.
Question 15
In a pivot table, the Values area is where numerical data is placed to be summarized, typically by summing, counting, or averaging.
Missing Word
Values
The Values area of a pivot table is used to perform calculations on numerical data, providing aggregated results.
Question 16
To show the exact value for each data point directly on a chart, one would add data labels.
Missing Word
labels
Data labels display the specific values associated with each data point or series directly on the chart, enhancing readability.
Question 17
Flash Fill automatically fills data when it senses a pattern, such as extracting first names from a list of full names without needing a formula.
Missing Word
Flash
Flash Fill is a smart tool that recognizes patterns in data entry and automatically completes the remaining entries based on that pattern.