Microsoft Excel: Modern Functions, Data Tools, Charts and PivotTables

Comprehensive vocabulary flashcards covering modern Excel lookup functions, dynamic arrays, text and date manipulations, statistical measures, flexible referencing, data cleaning, validation, conditional formatting, charting techniques, and PivotTable dashboard analytics.

XMATCH Function

A modern lookup function in Microsoft 365 that searches for a specified value in an array or range and returns its relative position. Unlike MATCH, its default match mode is an exact match ($0$), and its approximate match does not require the list to be sorted unless a binary search is explicitly selected.

Match Mode (XMATCH / XLOOKUP)

The argument determining how a match is evaluated: $0$ finds an identical entry (exact match, default); $-1$ finds the identical entry or next smaller item; $1$ finds the identical entry or next larger item; $2$ enables wildcard pattern matching ($\ast$, $?$); and $3$ performs a regex match.

Search Mode (XMATCH / XLOOKUP)

The argument specifying where a lookup begins: $1$ searches first-to-last (default); $-1$ searches last-to-first (finding the latest entry); $2$ performs a binary search on an ascending sorted list; and $-2$ performs a binary search on a descending sorted list.

XLOOKUP Function

A modern lookup function that combines INDEX, MATCH, and IFERROR into a single formula. It takes a lookup value, lookup array, and return array, can return values to the left of the lookup column without counting column index numbers, and includes built-in arguments for missing values, match modes, and search modes.

Dynamic Array Spilling

The automatic behavior in modern Excel where a single formula entered in one cell outputs multiple values that spill into adjacent empty rows or columns. If an existing cell blocks the output range, Excel returns a #SPILL! error until the obstruction is cleared.

Two-Way Lookup (Nested XLOOKUP)

A formula structure combining two XLOOKUP functions to retrieve a value from a two-dimensional grid. The inner XLOOKUP identifies a column or row block across headers and feeds that returned array into the outer XLOOKUP, eliminating the need for VLOOKUP with MATCH or INDEX with two MATCHes.

FILTER Function

A dynamic array function that extracts rows or columns matching specified criteria without altering the source table. Syntax: $=\text{FILTER}(array, include, [if\_empty])$. Conditions are combined using multiplication ($\ast$) for AND logic and addition ($+\text{)}$ for OR logic.

SORT Function

A dynamic array function that sorts an array by a specified column index ($\text{sort\_index}$) in ascending ($1$, default) or descending ($-1$) order ($\text{sort\_order}$), writing a sorted copy of the data elsewhere while leaving the source table intact.

UNIQUE Function

A dynamic array function that returns distinct values or rows from an array. It takes optional arguments $\text{by\_col}$ (set to TRUE to compare columns, default FALSE for rows) and $\text{exactly\_once}$ (set to TRUE to return only items appearing exactly one time).

LAMBDA Function

An Excel function that creates reusable custom functions without writing VBA code. Syntax: $=\text{LAMBDA}(parameter1, parameter2, ..., calculation)$. The calculation is always the final argument. The custom function is named using Define Name and managed in the Name Manager.

TRIM Function

A text cleaning function that removes all leading and trailing spaces from a text string and collapses multiple consecutive spaces between words into a single space.

LEN Function

A text function that returns the total count of characters in a string, including letters, digits, punctuation, and all visible or invisible spaces.

PROPER Function

A text function that capitalizes the first letter of each word in a text string and converts all other letters to lowercase.

SUBSTITUTE Function

A case-sensitive text function that replaces occurrences of specified existing text with new text. Syntax: $=\text{SUBSTITUTE}(text, old\_text, new\_text, [instance\_num])$. If $instance\_num$ is omitted, every occurrence is replaced; if specified, only that particular occurrence is changed.

LEFT, RIGHT, and MID Functions

Text extraction functions based on character positions: LEFT returns a specified number of characters from the start of a string; RIGHT returns characters from the end; and MID returns characters starting from a specified position ($\text{start\_num}$) for a given length ($\text{num\_chars}$).

FIND vs SEARCH

Functions that return the starting position of a substring within text. FIND is case-sensitive and returns #VALUE! if characters do not match exact case; SEARCH is case-insensitive.

TEXTBEFORE and TEXTAFTER

Modern text extraction functions in Microsoft 365 that extract and return all text situated before or after a designated delimiter character or string.

TEXTSPLIT Function

A dynamic array function that divides a text string into separate cells across columns using a column delimiter, down rows using a row delimiter, or across both dimensions simultaneously.

Concatenation (& Operator)

An operator that joins two or more text strings or cell values end-to-end into a single continuous text string, working without a formal function call.

TEXTJOIN Function

A text combination function that merges multiple strings or cell ranges using a designated delimiter, with an $ignore\_empty$ argument (TRUE ignores blank cells; FALSE inserts delimiters for blank cells).

Date Serial Number

Excel's internal numerical storage system where dates are stored as sequential integers representing the count of days since 1 January 1900 (serial number 1). Fractional values represent time.

TODAY vs NOW

Volatile date functions that take no arguments: TODAY returns the current system date as an integer serial number, whereas NOW returns the current date and time as a decimal serial number.

DATEVALUE Function

A function that converts a date stored as text into a standard Excel date serial number according to the computer's regional date settings.

DATEDIF Function

A legacy Excel function that calculates the interval between two dates in complete units. Syntax: $=\text{DATEDIF}(start\_date, end\_date, unit)$. The start date must precede the end date to avoid a #NUM! error.

DATEDIF Remainder Units ("YM", "YD", "MD")

Measurement codes in DATEDIF that calculate leftover intervals: "YM" counts complete months remaining after full years; "YD" counts days remaining after full years; and "MD" counts days remaining after full months.

EDATE vs EOMONTH

Date functions that add or subtract whole months: EDATE returns the exact same calendar day of the month after a specified number of months; EOMONTH returns the last calendar day of the month that many months away ($0$ represents the current month's end).

NETWORKDAYS and NETWORKDAYS.INTL

Functions that calculate the number of working days between two dates, excluding weekends and an optional list of holidays. NETWORKDAYS assumes Saturday and Sunday weekends; NETWORKDAYS.INTL allows custom weekend codes (e.g., $11$ for Sunday only) or 7-character binary strings (e.g., "0000011").

WORKDAY Function

A function that returns the date occurring a specified number of working days before or after a start date, treating the start date as day zero and skipping weekends and optional listed holidays.

TEXT Function

A formatting function that converts a number or date into formatted text using custom format codes in double quotation marks (e.g., $=\text{TEXT}(42000, \text{"₹\#\#,\#\#,\#\#0"})$ or $=\text{TEXT}(\text{TODAY}(), \text{"DDDD"})$). Its output is text and cannot be used directly in arithmetic.

VALUE Function

A data type conversion function that converts numbers or currency amounts stored as text strings into true numerical values that can be calculated, sorted, or compared.

Arithmetic Mean (AVERAGE)

The statistical measure of central tendency calculated by dividing the sum of all numeric observations by the count of observations ($\text{Mean} = \text{Sum} \div \text{Count}$). It is sensitive to extreme outliers.

Positional Centre (MEDIAN)

The middle value of a sorted dataset (or the arithmetic average of the two middle values if the count is even). It is resistant to extreme outliers and skewed distributions.

MODE.SNGL vs MODE.MULT

Functions that identify the most frequent values: MODE.SNGL returns a single value with the highest frequency (the first encountered in a tie); MODE.MULT spills all values tied for the highest frequency in multimodal datasets.

MIN/MAX vs MINA/MAXA

Functions returning dataset extremes: MIN and MAX evaluate numeric values and ignore text or logical values in cell ranges; MINA and MAXA evaluate logical TRUE as $1$, and evaluate logical FALSE and text entries as $0$.

MINIFS and MAXIFS

Conditional statistical functions that return the minimum or maximum value among records meeting one or more specified criteria. In both functions, the first argument is the result range, followed by criteria ranges and their respective criteria.

RANK.EQ vs RANK.AVG

Functions that determine the rank of a number within a reference list: RANK.EQ assigns tied values the same top rank; RANK.AVG assigns tied values the average of the occupied rank positions.

LARGE and SMALL

Functions that extract values based on relative position without sorting the source data: LARGE returns the $k$-th largest value in an array; SMALL returns the $k$-th smallest value.

PERCENTILE.INC vs PERCENTRANK.INC

Statistical functions operating in opposite directions: PERCENTILE.INC takes a percentage position ($k$ from $0$ to $1$) and returns the corresponding dataset value; PERCENTRANK.INC takes a dataset value ($x$) and returns its relative percentage position on a scale from $0$ to $1$.

Defined Name (Named Range / Cell)

A descriptive, meaningful label assigned to a single cell or a range of cells (such as GST_Rate for $B$2) that replaces cell coordinates in formulas, improving formula readability and eliminating the need for manual absolute referencing ($$).

Create from Selection

A command on the Formulas tab that automatically creates multiple defined names at once using labels found in the Top row, Left column, Bottom row, or Right column of the selected data.

Name Manager

A central management tool located on the Formulas tab used to view, edit, delete, and filter all defined names, scope assignments, and table names within a workbook.

INDIRECT Function

A dynamic reference function that converts a text string into an active cell or range reference (e.g., $=\text{INDIRECT}(B2\,\&\,\text{"!B2"})$). It enables formulas to reference worksheets whose names are selected dynamically in another cell, returning #REF! if the text does not match an existing sheet.

OFFSET Function

A reference function that returns a cell or range located a specified number of rows down/up and columns right/left from a starting reference point, with optional height and width arguments to specify the dimensions of the returned range.

Multilevel Sorting

Arranging data by two or more criteria in a defined priority sequence using Custom Sort. Excel sorts the entire dataset by the top level first, and subsequent levels sort records only within groups that tie on the levels above.

Custom List (Excel Sorting)

A user-defined ordering sequence (configured via Excel Options → Advanced → Edit Custom Lists) used in Custom Sort to arrange records in a non-alphabetical and non-numerical business hierarchy (e.g., Director → Manager → Junior → Intern).

Sorting by Cell Color vs Font Color

Custom Sort options located under 'Sort On' that order rows based on cell visual properties: Cell Color orders by the background fill color of cells, while Font Color orders by the color of the cell's text.

SUBTOTAL Function

A summary function that performs a selected mathematical operation on a range while automatically excluding rows hidden by a filter and skipping other nested SUBTOTAL formulas inside the referenced range to prevent double counting.

Function Numbers 1–11 vs 101–111 (SUBTOTAL)

Argument codes in the SUBTOTAL function: numbers 1–11 include manually hidden rows, whereas numbers 101–111 exclude manually hidden rows (e.g., $9$ is SUM including manually hidden rows; $109$ is SUM excluding them). Both groups exclude rows hidden by filters.

Data Subtotal Tool

A command on the Data tab that automatically groups a dataset sorted by a category column, inserting subtotal rows and a grand total with outline levels (1, 2, 3) whenever the value in the grouping field changes.

AutoFilter

A built-in data tool that places drop-down arrows on table headers to filter records using Text Filters, Number Filters (such as Top 10 or Above Average), Date Filters (structured in a Year → Month → Date hierarchy), or Color Filters.

Advanced Filter

A filtering feature on the Data tab that filters a list using a criteria range defined on the worksheet. It supports complex AND/OR logic across multiple rows, formula-based criteria, outputting unique records only, and copying filtered results to another location.

Criteria Range (Advanced Filter)

A dedicated worksheet range consisting of column headers and condition rows: conditions placed in the same row are evaluated using AND logic; conditions placed in different rows are evaluated using OR logic.

Formula-Based Criteria (Advanced Filter)

An Advanced Filter setup where a condition is evaluated by an Excel formula returning TRUE or FALSE. The header cell above the formula must be left blank or contain a label that is not a column header, and the formula must use a relative reference to the first data row.

Remove Duplicates

A data cleaning tool on the Data tab that permanently deletes duplicate rows from a dataset based on selected key columns, retaining only the first occurrence of each duplicate set.

Data Validation

An Excel feature on the Data tab that controls, restricts, and verifies cell entries using predefined rules (such as Whole number, Decimal, List, Date, Time, Text length, or Custom formulas) to prevent invalid data entry.

Input Message vs Error Alert

Components of Data Validation: an Input Message displays instructions as a yellow pop-up note when a validated cell is selected; an Error Alert appears after an invalid value is entered, blocking or warning the user.

Error Alert Styles (Stop, Warning, Information)

Enforcement levels for invalid data entry: Stop blocks the entry completely (no override); Warning cautions the user but allows an override via 'Yes'; Information informs the user and allows acceptance via 'OK'.

Dependent Drop-Down List

A secondary validation list whose available options dynamically change based on a choice made in a primary drop-down list, configured using Data Validation List with $=\text{INDIRECT}(cell)$ referencing named ranges.

Circle Invalid Data

A Data Validation auditing tool on the Data tab that draws red circles around preexisting cells that violate active validation rules without modifying or deleting their stored values.

Conditional Formatting

A feature that automatically changes cell appearance (fill color, font color, borders, icons, or data bars) when specified conditions or rules evaluate to TRUE, updating dynamically as cell values change.

Color Scales (Percent vs Percentile)

A conditional formatting preset that shades cells along a two- or three-color gradient: 'Percent' spaces colors linearly based on actual numeric values; 'Percentile' spaces colors based on ranked position, preventing outliers from distorting color gradations.

Icon Sets (Conditional Formatting)

Visual symbols (such as traffic lights or flags) displayed inside cells to classify values into performance categories based on customizable percentage, percentile, or numerical threshold values.

Data Bars (Show Bar Only)

In-cell horizontal bars whose lengths are proportional to cell values relative to the maximum value in the range. The 'Show Bar Only' option hides underlying numerical text while keeping the bars visible.

Formula-Based Conditional Formatting

A custom conditional formatting rule utilizing logical formulas. When applying formatting across an entire row, a mixed reference locking the column (e.g., =\D4>\$C4$) ensures every cell in the row evaluates the test column.

Stop If True (Conditional Formatting)

A setting in the Conditional Formatting Rules Manager that prevents lower-priority rules from being evaluated or applied to a cell once that cell satisfies the higher-priority rule.

Picture in Cell vs Picture over Cells

Picture insertion modes: 'Picture in Cell' embeds an image directly into a cell as its value, causing it to resize with the cell and be retrievable via XLOOKUP; 'Picture over Cells' places a floating image above the grid with full formatting, rotation, and cropping controls.

Structured Reference

Formula syntax used in Excel Tables that references table columns and data ranges by their bracketed header names (e.g., $\text{Catalogue}[\text{Item Code}]$) instead of standard cell addresses.

Alternative Text (Alt Text)

A descriptive textual property added to pictures, shapes, and charts that is read aloud by screen readers to assist visually impaired users without altering visual appearance.

HYPERLINK Function

A formula that creates a clickable link to a sheet, file, or webpage. Syntax: $=\text{HYPERLINK}(link\_location, [friendly\_name])$. For internal workbook destinations, the sheet and cell address must be preceded by a hash symbol (e.g., "#'Finance'!A1").

Checkbox (Form Control)

An interactive cell control that visually represents a Boolean state: checked evaluates to TRUE and unchecked evaluates to FALSE, enabling formulas like COUNTIF to compute task completion progress.

Column Chart vs Bar Chart

Categorical comparison charts distinguished by orientation: Column charts display vertical columns with categories along the horizontal axis (ideal for time sequences); Bar charts display horizontal bars with categories on the vertical axis (ideal for long labels or numerous categories).

Switch Row/Column

A chart command located on the Chart Design tab that swaps the assignments of data series and category axes without altering the layout of the source data on the worksheet.

Clustered vs Stacked vs 100% Stacked Charts

Chart variations: Clustered charts compare individual category values side-by-side; Stacked charts display component segments adding up to a total magnitude; 100% Stacked charts normalize each category to 100% to emphasize proportional composition.

Series Overlap vs Gap Width

Formatting parameters for bar and column charts: Series Overlap controls the extent to which multiple series overlap within a category; Gap Width controls the spacing between categories, making columns thicker or thinner.

Trendline

An analytical line or curve fitted to a data series in a chart to depict the general direction or pattern of data over time or across variables (options include Linear, Exponential, Logarithmic, Polynomial, Power, and Moving Average).

Moving Average Trendline

A smoothing trendline that calculates and plots the average of a specified number of consecutive prior observations ($\text{Period}$) to reduce the effect of short-term fluctuations.

Secondary Axis

A second vertical value axis added to a chart that allows two series with substantially different numerical scales or measurement units (e.g., revenue in lakhs and margin percentage) to be plotted together effectively.

Pie Chart vs Doughnut Chart

Circular charts representing proportional shares of a whole (totaling 100%): a pie chart is a solid circle cut into slices; a doughnut chart is a proportional ring with an empty center hole.

Sparklines

Miniature charts placed entirely inside single worksheet cells that provide compact visual representations of trends (Line), relative magnitudes (Column), or positive/negative states (Win/Loss).

Combo Chart

A chart that plots multiple data series using different chart types (such as columns for revenue and a line for profit margin) within a single visualization, frequently utilizing a secondary axis.

Scatter Plot

A chart plotting paired numerical observations as points along an X-axis and Y-axis to examine the mathematical relationship, correlation, or clustering between two continuous variables.

Linear Trendline Equation ($y = ax + b$)

The mathematical formula displayed on a scatter plot representing a fitted linear regression line: $x$ is the independent variable, $y$ is the estimated dependent variable, $a$ represents the slope (change in $y$ per unit change in $x$), and $b$ is the y-intercept.

Bubble Chart

An extension of a scatter plot that displays three numerical variables simultaneously: X-axis position, Y-axis position, and marker bubble size (representing the third variable).

Chart Template (.CRTX)

A reusable chart format file saved with the .CRTX extension that preserves custom styles, fonts, layouts, and colors, allowing identical formatting to be applied to future charts.

PivotTable

An interactive data summarization tool in Excel that organizes, aggregates, groups, and analyzes large datasets without modifying the source data or requiring manual formula construction.

Four PivotTable Areas (Rows, Columns, Values, Filters)

The structural layout areas of a PivotTable: Rows organizes labels vertically; Columns organizes labels horizontally; Values performs calculations (such as Sum or Average); and Filters restricts the entire report by selected criteria.

Value Field Settings

The configuration dialog used to modify how numerical data in the Values area is computed (via 'Summarize Values By' using Sum, Count, Average, Min, or Max) and how it is displayed (via 'Show Values As').

PivotTable Refresh (Pivot Cache)

The update command required to synchronize a PivotTable with edited source data. Because PivotTables calculate from an internal memory snapshot called the pivot cache, source changes do not appear until the user manually triggers a Refresh.

Tabular Form vs Outline Form

PivotTable report layouts: Tabular Form places each row field in a separate column with subtotal rows below each group (ideal for copying data); Outline Form displays fields in separate columns within a hierarchical outline format.

Label Filters vs Value Filters (PivotTable)

PivotTable filtering mechanisms: Label Filters evaluate category text labels (e.g., Begins With, Ends With, Contains); Value Filters evaluate summarized numerical measures (e.g., Greater Than, Between, Top N).

Slicer

A visual, interactive filtering component featuring clickable buttons designed for fast categorical filtering across one or more connected PivotTables.

Timeline

A specialized interactive filter designed exclusively for date and time-period fields in a PivotTable, allowing users to filter by days, months, quarters, or years.

PivotChart

A dynamic chart linked directly to a PivotTable that visually represents summarized data and updates automatically when the PivotTable is filtered, rearranged, or refreshed.

Report Connections

The configuration setting that links a single slicer or timeline to multiple compatible PivotTables, allowing one user selection to update multiple charts and reports across an interactive dashboard.

Calculated Field (PivotTable)

A custom PivotTable field created using a formula that references existing fields, generating a new custom metric without requiring additional columns in the original source dataset.

Show Values As: % of Grand Total

A display setting in Value Field Settings that converts each summarized value into its percentage share of the entire report's grand total using the formula: $(\text{Selected Value} \div \text{Grand Total}) \times 100$.

Show Values As: % of Row Total vs % of Column Total

Comparative display settings: % of Row Total divides each cell by its row total (summing to 100% across rows); % of Column Total divides each cell by its column total (summing to 100% down columns).

Show Values As: Running Total In

A display setting that calculates a cumulative running total across successive items of a designated Base field in a PivotTable.

Date Grouping (Calendar Q1 vs Indian Financial Q1)

PivotTable date grouping into Days, Months, Quarters, or Years. Default Excel calendar quarters run January–March (Q1) to October–December (Q4), whereas Indian financial quarters run April–June (Q1) to January–March (Q4).

Number Bands (PivotTable Grouping)

A PivotTable feature that groups continuous numeric values (such as salaries or sales) into discrete intervals defined by a Starting value, an Ending value, and an interval width ($\text{By}$).