A cell reference that changes when a formula is copied to another cell, adjusting based on the relative position of the original formula.
This is the default reference type in Excel and is crucial for efficiently applying calculations across many cells without manual editing.
Card 2
Absolute Reference
Answer
A cell reference that remains fixed when a formula is copied to another cell, indicated by dollar signs before both the column letter and row number.
Use an absolute reference, like $A$1, when a formula needs to always refer to the same specific cell regardless of where it is copied.
Card 3
Mixed Reference
Answer
A cell reference where either the column or the row is absolute, but not both, allowing one part of the reference to change while the other remains fixed when copied.
A mixed reference, like A$1 or $A1, is particularly useful for creating multiplication tables or other grid-like calculations.
Card 4
SUM Function
Answer
An Excel function that adds all the numbers in a specified range of cells.
The SUM function is more efficient and less error-prone than manually adding multiple cells with plus signs, especially for large ranges.
Card 5
AVERAGE Function
Answer
An Excel function that calculates the arithmetic mean of a range of numbers, ignoring text and empty cells.
This function helps determine a central tendency for numerical data, such as average sales or average test scores.
Card 6
COUNT Function
Answer
An Excel function that counts the number of cells in a range that contain numbers.
COUNT is distinct from COUNTA, which counts non-empty cells, and COUNTBLANK, which counts empty cells.
Card 7
IF Function
Answer
A logical function that checks whether a condition is met, and returns one value if true and another value if false.
The IF function enables conditional logic in a spreadsheet, allowing different outcomes based on specific criteria.
Card 8
Nested IF
Answer
An IF function placed inside another IF function's true or false argument, used to test multiple conditions in a sequence.
Nested IF statements allow for more complex decision-making logic than a single IF function, handling three or more possible outcomes.
Card 9
VLOOKUP Function
Answer
A lookup function that searches for a value in the first column of a table array and returns a corresponding value from another column in the same row.
VLOOKUP stands for 'Vertical Lookup' and is used when your lookup value is in the leftmost column of your data range.
Card 10
HLOOKUP Function
Answer
A lookup function that searches for a value in the first row of a table array and returns a corresponding value from another row in the same column.
HLOOKUP stands for 'Horizontal Lookup' and is used when your lookup value is in the topmost row of your data range.
Card 11
XLOOKUP Function
Answer
A modern lookup function that searches for a value in one range and returns a corresponding item from a second range, offering more flexibility than VLOOKUP or HLOOKUP.
XLOOKUP can search left or right, top or bottom, and supports approximate and exact matches, making it a powerful replacement for older lookup functions.
Card 12
INDEX/MATCH Combination
Answer
A powerful lookup method where MATCH finds the position of a lookup value in a range, and INDEX returns the value at that position from another range.
This combination is more flexible than VLOOKUP because it can look up values to the left of the lookup column and avoids the need for sorted data in some cases.
Card 13
CONCATENATE Function
Answer
A text function that joins two or more text strings into one string.
Use CONCATENATE or the ampersand (&) operator to combine separate pieces of text, such as first and last names, into a single cell.
Card 14
TRIM Function
Answer
A text function that removes all spaces from a text string except for single spaces between words.
TRIM is essential for data cleanup, especially when importing data that may contain leading, trailing, or multiple internal spaces that can cause lookup errors.
Card 15
LEFT Function
Answer
A text function that returns a specified number of characters from the beginning of a text string.
Use LEFT to extract prefixes, codes, or initial parts of text strings, such as extracting a department code from a product ID.
Card 16
RIGHT Function
Answer
A text function that returns a specified number of characters from the end of a text string.
Use RIGHT to extract suffixes or trailing parts of text strings, like extracting a file extension from a filename.
Card 17
FIND Function
Answer
A text function that returns the starting position of one text string within another text string, case-sensitive.
FIND is useful for locating specific characters or substrings within a larger text string, often used in conjunction with MID or LEFT/RIGHT.
Card 18
Text to Columns
Answer
A data cleanup tool that separates the contents of one cell into multiple columns based on a delimiter or fixed width.
This tool is invaluable for parsing imported data where multiple pieces of information, like full names or addresses, are combined in a single cell.
Card 19
Remove Duplicates
Answer
A data cleanup tool that identifies and deletes duplicate rows from a dataset based on selected columns.
Use Remove Duplicates to ensure data integrity and uniqueness, preventing skewed analyses caused by redundant entries.
Card 20
Conditional Formatting
Answer
A feature that automatically applies formatting, such as colors, fonts, or icons, to cells based on specified criteria or rules.
Conditional formatting visually highlights important trends, outliers, or specific data points, making data interpretation faster and more intuitive.
Card 21
Pivot Table
Answer
A powerful data summarization tool that calculates, summarizes, and analyzes data, allowing users to see comparisons, patterns, and trends.
Pivot tables enable dynamic rearrangement and aggregation of data from a large dataset, making complex analysis accessible without writing formulas.
Card 22
Pivot Table Fields
Answer
The individual columns from the source data that can be dragged into the Rows, Columns, Values, and Filters areas of a Pivot Table.
Understanding how to use Pivot Table Fields is fundamental to designing and manipulating the layout and summary of a pivot table.
Card 23
Calculated Field (Pivot Table)
Answer
A user-defined field within a Pivot Table that performs a calculation using other fields in the Pivot Table.
Calculated fields extend the analytical capabilities of Pivot Tables by allowing custom formulas to derive new insights not directly present in the source data.
Card 24
Slicer (Pivot Table)
Answer
An interactive visual control that allows users to quickly filter data in a Pivot Table, Pivot Chart, or regular table by clicking buttons.
Slicers provide a user-friendly way to filter data, making reports more dynamic and easier for others to interact with.
Card 25
Column Chart
Answer
A chart type that displays categories along the horizontal axis and values along the vertical axis, using vertical bars to represent data.
Column charts are effective for comparing values across different categories or showing changes over a period of time.
Card 26
Line Chart
Answer
A chart type that displays data points connected by straight line segments, primarily used to show trends over time.
Line charts are ideal for visualizing continuous data, revealing patterns such as growth, decline, or stability over a series.
Card 27
Pie Chart
Answer
A chart type that displays data as proportional slices of a circular pie, representing parts of a whole.
Pie charts are best used for showing the proportion of categories relative to a total, especially when there are few categories.
Card 28
Scatter Plot (XY Scatter)
Answer
A chart type that displays the relationship between two numerical variables, with data points plotted as individual markers on a coordinate system.
Scatter plots are crucial for identifying correlations or patterns between two different sets of quantitative data.
Card 29
Data Validation
Answer
A feature that restricts the type or range of data that users can enter into a cell, preventing incorrect data entry.
Data validation improves data quality and consistency, for example, by ensuring users select from a predefined list or enter only numbers within a certain range.
Card 30
Goal Seek
Answer
A What-If Analysis tool that determines what input value is needed in one cell to achieve a desired result in another cell that contains a formula.
Goal Seek is useful for reverse calculations, such as finding out what sales volume is needed to reach a specific profit target.