Excel Data Management: Tables, Filters, PivotTables, and Formulas

Table vs. Range

A Range is just a collection of cells; a Table is a structured object with special properties like banded rows, automatic expansion, and "Structured References" (using column names like =[@Sales] instead of $C$2).

Filtering vs. Sorting

Sorting rearranges the order of data; Filtering hides rows that don't meet specific criteria.

Standard vs. Advanced Filter

Standard filters use simple dropdowns. Advanced Filters use a separate criteria range on the worksheet to perform complex logic.

AND vs. OR

In filters, AND requires all conditions to be met (narrower results); OR requires only one to be met (broader results).

Slicers

Visual buttons that act as interactive filters for Tables and PivotTables.

SUBTOTAL vs. SUM

SUM adds everything in a range, even hidden rows. SUBTOTAL (usually function 109) only adds visible rows after a filter is applied.

Database Functions (DSUM, etc.)

Functions that analyze data in a list/table based on a defined criteria range (similar to Advanced Filter).

PivotTable

A tool used to summarize and analyze data without changing the original source.

Grouping vs. Summary Variables

Grouping variables are used for rows/columns (to categorize), while Summary variables are placed in the "Values" area (to calculate).

Focus

Managing 3D data and external links.

Grouping Worksheets

Selecting multiple tabs (Shift+Click) to enter data or formatting in all of them simultaneously. Risk: You might accidentally overwrite data on a hidden sheet if you forget they are grouped.

3-D References

A formula that refers to the same cell or range across multiple worksheets. Syntax: =SUM('Sheet1:Sheet3'!B5)

Consolidation By Position

Used when data is in the exact same cells on every sheet.

Consolidation By Category

Used when row/column labels are the same, but in different positions.

External References

Links to data in a different workbook.

Linking

The destination file updates when the source changes.

Copying

A static snapshot that does not update.

Template

A model file (.xltx) used to create new workbooks (.xlsx) with pre-set formatting and formulas.

Precedents vs. Dependents

Precedents are cells that provide data to a formula. Dependents are cells that rely on the current cell for their value.

Watch Window

A floating window that lets you monitor the value of specific cells even if you are working on a different sheet.

Data Validation

Restricts the type of data entered (e.g., "List" creates a dropdown; "Custom" uses a formula).

Input Message

Appears when the cell is selected (proactive).

Error Alert

Appears after invalid data is entered (reactive).

Macros

Recorded sequences of actions to automate repetitive tasks.

Absolute macro

Always performs actions in specific cells (e.g., Cell A1).

Relative macro

Performs actions relative to the currently selected cell.

Protection

Worksheet level protects cells; Workbook level protects the structure (adding/deleting tabs); Encryption requires a password to open the file.

Get & Transform (Power Query)

The engine used to import and "clean" (transform) data from external sources like Web, SQL, or Text files.

Delimiters

Characters (like commas or tabs) that separate data into columns in a text file.

Flash Fill

Recognizes patterns (e.g., extracting first names) and fills the rest automatically.

Text Functions

LEFT/RIGHT/MID: Extract characters from the start, end, or middle of a string. FIND: Locates a character's position. LEN: Counts total characters. TRIM: Removes extra spaces. CLEAN: Removes non-printable characters.

Metadata

"Data about data" (e.g., file author, date created).

Break-even Analysis

Finding the point where Total Revenue = Total Cost ($0 profit).

Cost Types

Fixed (don't change with volume), Variable (change per unit), Mixed (both).

Data Tables

A range of cells that shows how changing one or two variables affects a formula.

Goal Seek

Use this when you know the result you want but need to find the input value to get there.

Scenario Manager

Saves multiple groups of values (e.g., "Best Case," "Worst Case") to compare results.

Solver

An add-in for optimization. It finds the maximum or minimum value for an objective cell by changing multiple variables while staying within Constraints.

Descriptive vs. Inferential

Descriptive summarizes the data you have; Inferential uses a sample to make predictions about a larger population.

Central Tendency

Mean (average), Median (middle), Mode (most frequent).

Standard Deviation

Measures how spread out the numbers are from the mean.

Histogram

A chart showing the frequency of data within specific ranges called Bins.

Correlation

Measures the relationship between two variables. The Correlation Coefficient ($r$) ranges from $-1$ to $+1$.

R-Squared

A value ($0$ to $1$) indicating how well a regression line fits the data points.

FORECAST.ETS

A function that predicts future values based on seasonal patterns.

Business Intelligence (BI)

The process of turning raw data into meaningful insights for decision-making.

Data Model

A collection of related tables inside Excel (Power Pivot) that allows you to create relationships between different data sources.

KPI (Key Performance Indicator)

A visual cue (like a green/red light) showing how a value compares to a target.

Implicit vs. Explicit

Implicit calculations are created by dragging fields in a PivotTable; Explicit are DAX formulas created by the user (Measures).

Dynamic Arrays

Formulas that "spill" into neighboring cells (e.g., UNIQUE extracts a list of distinct values, and SORT organizes them).

White Space

Crucial in dashboard design to prevent "clutter" and help the user focus on key metrics.

Table

A structured object with special properties like banded rows, automatic expansion, and Structured References

Range

A collection of cells

Sorting

Rearranges the order of data

Filtering

Hides rows that don't meet specific criteria

Advanced Filters

Use a separate criteria range on the worksheet to perform complex logic

Slicers

Visual buttons that act as interactive filters for Tables and PivotTables

SUBTOTAL

Only adds visible rows after a filter is applied

PivotTable

A tool used to summarize and analyze data without changing the original source

3-D References

A formula that refers to the same cell or range across multiple worksheets

Consolidation By Position

Used when data is in the exact same cells on every sheet

Consolidation By Category

Used when row/column labels are the same, but in different positions

Template

A model file (.xltx) used to create new workbooks (.xlsx) with pre-set formatting and formulas

Precedents

Cells that provide data to a formula

Dependents

Cells that rely on the current cell for their value

Watch Window

A floating window that lets you monitor the value of specific cells even if you are working on a different sheet

Data Validation

Restricts the type of data entered

Input Message

Appears when the cell is selected (proactive)

Error Alert

Appears after invalid data is entered (reactive)

Macros

Recorded sequences of actions to automate repetitive tasks

Flash Fill

Recognizes patterns and fills the rest automatically

Metadata

Data about data

Break-even Analysis

Finding the point where Total Revenue = Total Cost

Goal Seek

Use this when you know the result you want but need to find the input value to get there

Scenario Manager

Saves multiple groups of values to compare results

Solver

An add-in for optimization that finds the maximum or minimum value for an objective cell by changing multiple variables while staying within Constraints

Histogram

A chart showing the frequency of data within specific ranges called Bins

Correlation

Measures the relationship between two variables

Data Model

A collection of related tables inside Excel (Power Pivot) that allows you to create relationships between different data sources

KPI (Key Performance Indicator)

A visual cue showing how a value compares to a target