Spreadsheet Study Guide

📖 Core Concepts Spreadsheet – a computer program that stores data in a grid of cells (rows + columns) and can compute results with formulas. Workbook – the file that holds one or more worksheets (tabs). Cell – the intersection of a column (A, B, C…) and a row (1, 2, 3…). It can contain a number, text, date, time, or a formula result. Formula – an expression that begins with “=”, uses values, cell references, operators (+ – /), and built‑in functions (e.g., SUM()). Function – a pre‑written routine that returns a value (e.g., SUM(range), NPV(rate, values)). Absolute vs. Relative Reference – $A$1 never changes when copied; A1 changes relative to its new position; $A1 or A$1 lock only one coordinate. Automatic Recalculation – when any cell changes, all dependent cells are recomputed (unless a circular reference blocks the process). 📌 Must Remember Cell address: column letter(s) + row number (e.g., C10). Range notation: A1:A10 (continuous block). Absolute reference syntax: $Column$Row (e.g., $B$5). Mixed reference: $A1 (fixed column) or A$1 (fixed row). Circular reference → calculation error unless intentionally used for iterative solving. Named range → improves readability; defined in the Name Manager. Conditional formatting → visual cue (e.g., negative numbers red) without altering the underlying value. Data validation → restricts allowed entry types to reduce input errors. 🔄 Key Processes Entering a Formula Type = → start the expression. Add numbers, operators, cell references, and functions. Press Enter → result displays; formula viewable in the formula bar. Copying Formulas with Correct References Use relative references for row/column adjustments. Use $ to lock rows/columns when you need a constant reference. Creating a Named Range Select cells → Formulas → Define Name → give a meaningful name → OK. Setting Up Data Validation Select target cells → Data → Data Validation → choose criteria (list, whole number, date, etc.). Applying Conditional Formatting Select range → Home → Conditional Formatting → set rule (e.g., “Cell Value < 0 → red font”). 🔍 Key Comparisons Relative vs. Absolute Reference A1 → moves when copied. $A$1 → stays exactly the same. A1 Notation vs. R1C1 Notation A1 uses letters for columns, numbers for rows. R1C1 uses numbers for both (e.g., R1C1). Spreadsheet vs. Database Spreadsheet: single 2‑D table, great for quick “what‑if” analysis. Database: multiple related tables, strong schema, query language (SQL). ⚠️ Common Misunderstandings “Formulas are hidden” – the formula is not visible only because the cell shows the result; you can view/edit it in the formula bar. “Formatting changes the value” – formatting (currency, date, color) only alters appearance; the underlying numeric value stays unchanged. “All circular references are errors” – some advanced models use intentional circular references with iterative calculation turned on. 🧠 Mental Models / Intuition Think of the spreadsheet as a dependency graph: each cell is a node; arrows point from referenced cells to the dependent cell. Changes ripple forward along the arrows. Absolute references are anchors: when you copy a formula, anchors stay put while the rest of the ship (relative parts) moves. 🚩 Exceptions & Edge Cases Inserting rows/columns before a total cell may exclude the new data if the total’s range is hard‑coded (e.g., =SUM(A1:A5) after inserting a row above row 3 still sums only A1:A5). Iterative calculation must be enabled (Options → Formulas) for intentional circular references to converge; otherwise Excel returns a #REF! error. 📍 When to Use Which SUM vs. SUBTOTAL – use SUM for normal totals; use SUBTOTAL when you need totals that ignore filtered or hidden rows. Named range vs. direct cell address – choose a named range for formulas that are read by others or reused across sheets. Data Validation vs. Conditional Formatting – validation prevents bad input; conditional formatting highlights issues after they occur. 👀 Patterns to Recognize Repeated “=SUM(… )” across rows → likely a candidate for an array formula or a single table‑structured reference. Hard‑coded numbers inside formulas (e.g., =5C10) → a red flag for maintainability; replace with a named constant cell. Mixed references in copied formulas – usually indicate the formula is meant to lock either a column or a row (common in lookup tables). 🗂️ Exam Traps Choosing the wrong reference type – an answer that uses $A$1 where the problem expects a relative reference will be penalized. Confusing formatting with value – an answer that treats “$1,000” (currency format) as a different numeric value is wrong. Circular reference “error” option – some questions present “#VALUE!” or “#REF!” as distractors; remember that an intentional circular reference requires iterative mode, otherwise it’s an error. Assuming databases and spreadsheets are interchangeable – a distractor may claim that a spreadsheet provides relational integrity; remember it lacks explicit schema and referential constraints. --- Study tip: Review the dependency‑graph mental model while practicing copy‑paste of formulas; it instantly reveals whether a reference should be relative, absolute, or mixed. Good luck!