A comprehensive glossary of terms related to Excel, spreadsheets, SQL, and data analysis. Use the alphabetical navigation to find specific terms quickly.
A
Absolute Reference
A cell reference that doesn’t change when copied to another cell. Created by adding dollar signs ($) before the column letter and row number (e.g., $A$1). Essential for locking cells in formulas.
ACID Properties
A set of properties (Atomicity, Consistency, Isolation, Durability) that guarantee reliable database transactions. Ensures data integrity even in case of errors or system failures.
Aggregate Functions
SQL functions that perform calculations on multiple rows and return a single value. Common examples include COUNT, SUM, AVG, MIN, and MAX.
AND Function
A logical function in Excel that returns TRUE if all conditions are met, FALSE otherwise. Often used with IF functions for multiple criteria testing.
AutoCorrect
An Excel feature that automatically corrects common typing errors and misspellings as you type. Can be customized with your own corrections.
AutoSum
A quick Excel feature that automatically creates a SUM formula for a range of cells. Activated with Alt+= (Windows) or Command+Shift+T (Mac).
C
Calculated Field
A custom field in a Pivot Table that performs calculations using other fields. Allows you to create new metrics without modifying source data.
Cell Reference
The address of a cell in a spreadsheet, combining the column letter and row number (e.g., A1, B5). Can be relative, absolute, or mixed.
COMMIT
An SQL command that saves all changes made during the current transaction permanently to the database.
CONCAT / CONCATENATE
Excel functions that join multiple text strings into one. CONCAT is the newer version that can handle ranges, while CONCATENATE requires individual cell references.
Conditional Formatting
An Excel feature that automatically changes cell appearance (color, font, icons) based on cell values or conditions.
COUNTIF / COUNTIFS
Excel functions that count cells meeting one condition (COUNTIF) or multiple conditions (COUNTIFS). Essential for data analysis and reporting.
CREATE TABLE
An SQL DDL command used to create a new table in a database, defining columns, data types, and constraints.
D
Data Definition Language (DDL)
SQL commands used to define and modify database structures. Includes CREATE, ALTER, DROP, and TRUNCATE statements.
Data Manipulation Language (DML)
SQL commands used to manipulate data within tables. Includes SELECT, INSERT, UPDATE, and DELETE statements.
Data Validation
An Excel feature that restricts the type of data that can be entered in a cell. Used to create dropdown lists and ensure data integrity.
Date Functions
Excel functions for working with dates, including TODAY, NOW, DATE, YEAR, MONTH, DAY, WEEKDAY, and ISOWEEKNUM.
DELETE
An SQL DML command that removes rows from a table based on specified conditions. Use with caution as it permanently removes data.
DIV/0 Error
An Excel error that occurs when a formula tries to divide by zero or an empty cell. Can be handled using IFERROR or IF functions.
DROP
An SQL DDL command that permanently deletes a database object (table, index, view). Cannot be undone.
F
FILTER Function
A dynamic array function in Excel that filters a range based on criteria and returns matching results. Automatically spills results to adjacent cells.
Flash Fill
An Excel feature that automatically fills values based on a pattern you establish. Activated with Ctrl+E (Windows) or Command+E (Mac).
Foreign Key
A column in a database table that references the primary key of another table. Creates relationships between tables.
Formula Bar
The bar at the top of the Excel window that displays the contents of the active cell. Used to enter and edit formulas.
Freeze Panes
An Excel feature that locks rows and/or columns so they remain visible when scrolling through large datasets.
Functions
Predefined formulas in Excel that perform specific calculations. Categories include logical, text, date/time, lookup, math, and statistical functions.
G
Gridlines
The light gray lines that separate cells in a spreadsheet. Can be hidden for cleaner presentation via View menu or Page Setup.
GROUP BY
An SQL clause that groups rows with the same values in specified columns. Often used with aggregate functions like COUNT, SUM, AVG.
H
HAVING
An SQL clause used to filter groups created by GROUP BY. Similar to WHERE but operates on aggregated data.
I
IF Function
A logical function that returns one value if a condition is TRUE and another if FALSE. The foundation of conditional logic in Excel.
IFERROR
An Excel function that returns a custom value if a formula results in an error, otherwise returns the formula’s result. Essential for error handling.
IFS Function
An Excel function that checks multiple conditions and returns a value corresponding to the first TRUE condition. Cleaner alternative to nested IFs.
INDEX
An Excel function that returns a value from a specific position in a range. Often combined with MATCH for powerful lookups.
Indent
Spacing added to cell contents for visual hierarchy. Increased with Alt+H+6 (Windows) and decreased with Alt+H+5.
INNER JOIN
An SQL join that returns only rows where there is a match in both tables. The most common type of join.
INSERT
An SQL DML command that adds new rows to a database table. Can insert single or multiple rows at once.
J
JOIN
An SQL operation that combines rows from two or more tables based on related columns. Types include INNER, LEFT, RIGHT, and FULL joins.
L
LEFT JOIN
An SQL join that returns all rows from the left table and matching rows from the right table. Non-matching rows show NULL values.
Line Break
A new line within an Excel cell. Created with Alt+Enter (Windows) or Control+Option+Enter (Mac).
Locking Cells
Protecting specific cells from editing while allowing changes to others. Requires both cell locking and sheet protection.
M
MATCH
An Excel function that returns the position of a value in a range. Often paired with INDEX for dynamic lookups.
Mixed Reference
A cell reference where either the column or row is absolute (locked) while the other is relative. Example: $A1 or A$1.
MySQL
A popular open-source relational database management system. Widely used for web applications and known for its speed and reliability.
N
Named Range
A descriptive name assigned to a cell or range of cells in Excel. Makes formulas easier to read and understand.
Nested Functions
Functions placed inside other functions in Excel. Allows complex calculations by combining multiple operations.
NOW Function
An Excel function that returns the current date and time. Updates automatically when the worksheet recalculates.
NULL
A special marker in SQL indicating that a data value does not exist in the database. Different from zero or empty string.
O
Oracle
A multi-model database management system commonly used in enterprise environments. Known for scalability and comprehensive features.
ORDER BY
An SQL clause that sorts query results in ascending (ASC) or descending (DESC) order based on specified columns.
P
Password Protection
Security feature in Excel that restricts access to workbooks or worksheets. Can protect structure, content, or both.
Pivot Table
A powerful Excel tool for summarizing, analyzing, and exploring data. Allows dynamic grouping, filtering, and calculation of large datasets.
PostgreSQL
An advanced open-source relational database known for reliability, feature robustness, and standards compliance.
Primary Key
A column or set of columns that uniquely identifies each row in a database table. Cannot contain NULL values.
Q
Query
A request for data from a database. In SQL, typically starts with SELECT and can include filtering, sorting, and joining operations.
Query Optimization
The process of improving SQL query performance through techniques like proper indexing, efficient joins, and avoiding unnecessary operations.
R
Relative Reference
A cell reference that adjusts automatically when copied to another cell. Default behavior in Excel (e.g., A1 becomes B1 when copied right).
ROLLBACK
An SQL command that undoes all changes made during the current transaction, restoring the database to its previous state.
Row
A horizontal line of cells in a spreadsheet, identified by numbers (1, 2, 3…). In databases, a row represents a single record.
S
Scalar Functions
SQL functions that operate on a single value and return a single value. Examples include UPPER, LOWER, LENGTH, ROUND.
SELECT
The fundamental SQL command for retrieving data from a database. Forms the basis of all SQL queries.
Shortcut Keys
Keyboard combinations that perform actions quickly without using the mouse. Essential for improving productivity in Excel.
Spell Check
An Excel feature that checks spelling in worksheets. Activated with F7 key. Can be customized with personal dictionaries.
SQL (Structured Query Language)
A standard programming language for managing and manipulating relational databases. Used for querying, updating, and managing data.
SQL Dialects
Variations of SQL implemented by different database systems (MySQL, PostgreSQL, SQL Server, Oracle). Each has unique syntax and features.
SQL Server
Microsoft’s relational database management system. Popular in enterprise environments with tight Windows/Azure integration.
SQLite
A lightweight, file-based database engine. Popular for mobile apps, embedded systems, and local data storage.
Stored Procedure
A saved collection of SQL statements that can be executed as a single unit. Improves performance and code reusability.
Subquery
A query nested inside another SQL query. Used to perform complex operations and filter results based on other queries.
SUM / SUMIF / SUMIFS
Excel functions for adding values. SUM adds all values, SUMIF adds values meeting one condition, SUMIFS handles multiple conditions.
T
Table
In Excel, a structured range with headers that enables automatic formatting, filtering, and formula references. In SQL, a collection of related data organized in rows and columns.
Text to Columns
An Excel feature that splits cell content into multiple columns based on delimiters (comma, semicolon, space) or fixed widths.
Theme
A collection of colors, fonts, and effects in Excel that can be applied consistently across a workbook for professional appearance.
TODAY Function
An Excel function that returns the current date. Updates automatically when the worksheet recalculates.
Transaction
A sequence of SQL operations performed as a single logical unit of work. Either all operations succeed or none do.
TRANSPOSE
An Excel function that converts a vertical range to horizontal or vice versa. Useful for rotating data layouts.
Trigger
A SQL procedure that automatically executes in response to specific events on a table (INSERT, UPDATE, DELETE).
TRUE/FALSE
Boolean values in Excel representing logical states. Returned by comparison operators and logical functions.
U
Undo/Redo
Commands to reverse or repeat actions. Ctrl+Z (undo) and Ctrl+Y (redo) on Windows; Command+Z and Command+Shift+Z on Mac.
UPDATE
An SQL DML command that modifies existing data in a table. Use WHERE clause to target specific rows.
V
VLOOKUP
An Excel function that searches for a value in the first column of a range and returns a value from a specified column. Being replaced by XLOOKUP in newer versions.
W
WHERE
An SQL clause that filters rows based on specified conditions. Used with SELECT, UPDATE, and DELETE statements.
Window Functions
SQL functions that perform calculations across a set of rows related to the current row. Examples include ROW_NUMBER, RANK, LAG, LEAD.
Workbook
An Excel file containing one or more worksheets. Saved with .xlsx extension (or .xlsm for macro-enabled files).
Worksheet
A single page within an Excel workbook, consisting of a grid of cells organized in rows and columns.
X
XLOOKUP
A modern Excel function that searches a range and returns matching items. More flexible than VLOOKUP, can search in any direction.