SUMPRODUCT
Learn how SUMPRODUCT multiplies aligned ranges and sums the results, when it raises an error, and how text in a range can silently change the total.
-
-
Rate of Change
Learn why a growth figure is a ratio rather than a percentage, why the base period decides the number, and why raw counts need a denominator.
-
Pivot Table
Learn how a pivot table groups and summarizes without formulas, which defaults it applies silently, and why it can show stale numbers.
-
Weighted Average
Learn when a plain average misleads, how weights change the answer, and why averaging group averages is the most common version of the mistake.
-
Conditional Aggregation
Learn how COUNTIF and SUMIF summarize only the rows meeting a condition, why criteria need quotes, and how a mismatched sum range goes wrong.
-
Calculated Field
Learn how a calculated field computes from aggregated values rather than individual rows, and why that distinction changes the number it returns.
-
Spreadsheet and SQL Tool Choice
Learn which operations the two tools share, which concrete signals say the work has outgrown a sheet, and what moving to SQL actually buys.
-
Lookup Key
Learn why two keys that look identical fail to match, which three defects cause it, and what to check before writing any lookup formula.
-
Lookup Function
Learn what lookup functions do, why VLOOKUP searches the first column, and how copied ranges and column positions affect its results.
-
INDEX and MATCH
Learn how splitting a lookup into finding a position and reading a value removes counted column numbers and the left-to-right restriction.
-
Exact and Approximate Match
Learn what VLOOKUP’s last argument decides, why its approximate match needs a sorted first column, and why identifiers require an exact match.
-
Data Aggregation
Learn what aggregation means when it refers to gathering sources rather than summarizing rows, and which decisions it hides inside a merged table.
-
Data Validation
Learn how a spreadsheet restricts what a cell accepts, why rejecting beats warning for critical columns, and what validation cannot protect.
-
Spreadsheet Filter
Learn how a filter hides rows without deleting them, why a filtered view can mislead a total, and how filtering isolates cleaning tasks.
-
Split Text to Columns
Learn how splitting a column on a delimiter works, why it can overwrite the columns beside it, and when a formula is the safer choice.
-
Sort Sheet and Sort Range
Learn why sorting one column detaches every row from its own values, what Sort sheet does differently, and how header recognition protects a table.
-
Sheet Protection and Permissions
Learn what protecting a range actually prevents, why hiding a sheet is not security, and how viewer, commenter and editor access differ.
-
Duplicate Records
Learn how a spreadsheet decides two rows are duplicates, why near-identical rows survive, and what to check before a deletion you cannot undo.
-
Conditional Formatting
Learn how rules change a cell’s appearance without changing its value, which rule types exist, and why color is a signal rather than a record.
-
Spreadsheet Formula and Function
Learn how a formula differs from a function, why parentheses decide the order, and why range functions survive edits that cell-by-cell sums do not.
-
Spreadsheet Error Values
Learn what #DIV/0!, #N/A, #NAME?, #NUM!, #REF! and #VALUE! each mean, why they propagate, and when hiding one with IFERROR is a mistake.
-
Number Stored as Text
Learn why a numeric-looking cell is skipped by SUM, how to spot it with alignment and COUNT versus COUNTA, and when not to convert it.
-
Header Row and Observation
Learn what a header row promises, why one row should mean one observation, and which common layouts break every step that follows.
-
Date Serial Number
Learn why a spreadsheet date is a number with a format, what a cell showing 45597 means, and how to tell a real date from text that looks like one.
-
CSV and Delimiters
Learn what a CSV really contains, how the delimiter and quoting rules work, and which values an import quietly changes on the way in.
-
Cell Reference
Learn how relative, absolute, and mixed references behave when a formula is copied, what the dollar sign locks, and why filled columns go wrong.