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

Table vs. Range

A Range is just a collection of cells. A Table is a structured object that Excel recognizes as a single unit, providing automatic formatting, filter buttons, and a Total Row.

Structured References

Instead of using cell addresses (like $A$1:$B$10), Tables use names. For example, =SUM(SalesTable[Amount]) is much easier to read and automatically expands if you add new data.

Data Set vs. Information

A Data Set is a collection of raw facts (numbers, dates). Information is that data processed and organized into a meaningful context for decision-making.

Filtering vs. Sorting

Sorting rearranges the order of the rows (e.g., A to Z). Filtering hides rows that don't meet your criteria without changing their order.

Standard Filter

Uses the drop-down arrows at the top of columns for quick, simple criteria.

Advanced Filter

Requires a separate 'Criteria Range' on the sheet. It allows for more complex 'OR' logic and the ability to copy filtered results to a new location.

AND vs. OR Criteria

AND: Criteria on the same row in an advanced filter (both must be true). OR: Criteria on different rows in an advanced filter (either can be true).

Slicers

These are visual filtering buttons that stay on the worksheet, making it easy to see exactly what is currently filtered in a Table or PivotTable.

SUBTOTAL vs. SUM Behavior

The SUM function always adds every cell in a range, even if some rows are hidden. The SUBTOTAL function only adds the visible cells after a filter is applied.

Database Functions

These allow you to perform math on a specific field in a list based on a criteria range.

DSUM

Adds values in a column that match criteria.

DCOUNT

Counts cells with numbers that match criteria.

DAVREAGE

Averages values that match criteria.

PivotTable Concept

A tool used to 'pivot' or reorganize large amounts of data into a summary table without changing the original data.

Filtering Behavior

A PivotTable ignores filters applied to the source worksheet. It has its own internal filters and slicers.

Grouping Variables

Categories used to organize the data (e.g., 'Region' or 'Month').

Summary Variables

The actual data being calculated (e.g., 'Sum of Revenue').

Drill-down

Double-clicking a value in a PivotTable creates a new worksheet showing every raw data row that makes up that specific total.

PivotChart

A visual representation (bar, pie, or line) directly linked to a PivotTable.

Subtotal Function

=SUBTOTAL(function_num, ref1, ...)

Database Syntax

=DSUM(database, field, criteria)

Grouping Worksheets

Selecting multiple tabs (using Ctrl or Shift) to perform tasks simultaneously.

Risks of Grouped Worksheets

Unintended Modifications: If you forget to ungroup, you may accidentally overwrite data on hidden sheets.

3-D References

A reference that points to the same cell or range across multiple contiguous worksheets.

External References

Formulas that refer to cells in a different workbook.

Consolidation by Position

Used when source worksheets have an identical layout. Excel totals data based on the specific cell address.

Collaboration vs. Co-authoring

Collaboration is the general act of working together. Co-authoring is a specific technical feature that allows multiple users to edit the same file in real-time.

Template vs. Standard Workbook

Standard Workbook (.xlsx): A file used for specific, one-time data entry and analysis. Template (.xltx): A master 'blueprint' that contains formatting, headers, and formulas.

Precedents vs. Dependents

Precedents: Cells that are referred to by a formula in the active cell. Dependents: Cells that contain formulas that refer to the active cell.

Circular Reference

Occurs when a formula refers to its own cell, either directly or indirectly, creating an infinite loop.

Data Validation Rules

Constraints placed on a cell to limit what a user can enter.

Input Message vs. Error Alert

Input Message: Appears when a cell is selected to guide the user on what to enter. Error Alert: Pops up after an invalid entry is attempted.

Invoice Number Generation

Created by concatenating the Date, Time, and Employee ID using the TEXT function and the ampersand (&) symbol.

Macros

Sets of instructions recorded in VBA (Visual Basic for Applications) to automate repetitive tasks.

Absolute Macros

Always performs actions on the exact same cells recorded.

Relative Macros

Performs actions based on the position of the active cell when the macro is run.

Trusted Locations

Folders on your computer or network designated as safe for running macros without security warnings.

Worksheet Protection

Prevents users from editing specific cells once they are 'Locked'.

Workbook Protection

Protects the structure of the file, preventing users from adding, deleting, or renaming tabs.

Protection

Restricts what a user can do inside the file, such as editing cells or moving sheets.

Encryption

Restricts who can open the file by requiring a password, providing the highest level of security.

Unlocking Cells

Cells are 'Locked' by default, but must be unlocked before protection is turned on to allow data entry.

Invoice Number Logic

=IF(E6>0,TEXT(E6,'YYYYMMDD'),"")&' '&IF(E8>0,TEXT(E8,'HHMM'),"")&' '&IF(E10>0,VLOOKUP(E10,Therapists,2,FALSE),'')

External Data

Any data not stored in the current Excel file or on your local drive.

Local Data

Data already within your .xlsx or .xls file.

Get & Transform (Power Query)

A powerful business intelligence tool used to connect to, prepare, and transform data.

Importing vs. Copy-Paste

Importing creates a 'live' link; Copy-Paste is a static snapshot.

XML (Extensible Markup Language)

Uses custom 'tags' to define structure and separates content from formatting.

Text Files

Flat files that use simple lines of text separated by Delimiters to indicate column separations.

Metadata

Data about data, such as file size, author, or source of an import.

Data Cleansing

The process of fixing formatting errors, removing extra spaces, or correcting spellings.

Data Verification

Confirming the accuracy of the data, often more expensive and time-consuming than cleansing.

Flash Fill

A pattern-recognition tool that fills in data automatically based on examples.

LEFT / RIGHT Function

Extracts a set number of characters from the start (left) or end (right) of a string.

MID Function

Extracts characters from the middle of a string, starting at a specific position.

FIND Function

Locates a specific character and returns its position number.

LEN Function

Returns the total number of characters in a cell, including spaces.

TRIM Function

Removes extra leading, trailing, or double spaces between words.

CLEAN Function

Removes 'non-printing' characters often found in web imports.

Primary Key

A unique identifier for a record in its own table.

Foreign Key

A primary key from one table that appears in a second table to create a relationship.

Remove Duplicates Tool

Deletes identical rows without providing a preview.

Break-Even Analysis

The process of finding the point where Total Revenue equals Total Expenses.

Fixed Costs

Costs that do not change regardless of volume.

Variable Costs

Costs that change in direct proportion to volume.

Mixed Costs

Costs containing both fixed and variable components.

What-If Analysis

The process of changing values in cells to see how those changes affect the outcome of formulas.

Data Tables

Used to see the results of multiple inputs at once.

Goal Seek

A tool used to find the input value needed to achieve a desired result.

Scenario Manager

Used to define and save different groups of values (scenarios) to compare impacts.

Solver

An add-in used for complex problems with multiple variables and restrictions.

Objective Cell

The target cell you want to maximize, minimize, or set to a specific value.

Changing Cells

The variable cells Solver adjusts to reach the objective.

Constraints

The limits placed on the model in Solver.

Binding Constraint

A constraint that limits the final result.

Nonbinding Constraint

A constraint that does not restrict the optimal solution.

Linear Model

Results change at a constant rate.

Nonlinear Model

Results change at different rates.

Price Elasticity of Demand

Measures how sensitive customers are to a change in price.

Inelastic Demand

Demand barely changes when price moves.

Elastic Demand

Demand changes significantly when price moves.

Unit Elastic Demand

Demand changes at the exact same rate as price.

Net Income Calculation

$$Net Income = Gross Revenue - (Total Fixed Costs + Total Variable Costs)$$

Elasticity Formula

$$Elasticity = \frac{\% \text{ Change in Quantity}}{\% \text{ Change in Price}}$$

Descriptive Statistics

Summarizes and describes the features of a specific data set.

Inferential Statistics

Uses a sample of data to make predictions or generalizations about a larger population.

Population

The entire group being studied.

Sample

A smaller subset of the population used for study.

Random Sample

A sample where every member of the population has an equal chance of being selected.

Mean

The arithmetic average.

Median

The middle value in a sorted list.

Mode

The most frequently occurring value.

Variance

Measures how far data points are spread out from the mean.

Standard Deviation

The square root of variance.

Normal Distribution

A bell-shaped curve where data is symmetrical around the mean.

Empirical Rule

68% of data falls within 1 standard deviation, and 95% falls within 2.

Correlation

Standardizes the relationship into a Correlation Coefficient (r).

Covariance

Indicates the direction of a relationship but not the strength.

Regression Analysis

A method for predicting a Dependent Variable based on one or more Independent Variables.

R-Squared

Indicates how much of the variance in the dependent variable is explained by the model.