Computer Specialist in Spreadsheet & Database Level 1 cheat sheet

Workbook = the file; worksheet = one tab within it. A single workbook can hold many worksheets plus chart sheets.

Name Box shows the active cell address and accepts a typed address or range (e.g. B7:D20) to jump or select.

Formula Bar displays the stored contents of the active cell; the cell itself shows the computed result.

Ctrl+End moves to the last used cell; Ctrl+Home returns to A1. Used range = bounding rectangle from A1 to the last cell containing data or formatting.

Absolute vs relative addressing: $ locks a row or column ($A$1 absolute, A$1 column-locked, $1 row-locked); F4 cycles the reference type while editing.

Row/column headers: clicking a header selects the whole row/column; Ctrl+Space selects the column, Shift+Space selects the row of the active cell.

Freeze Panes keeps rows above and columns left of the active cell visible while scrolling; it freezes by position, not by content.

Sheet reference syntax across tabs: Sheet2!A1. A space-containing name needs quotes: 'Sales 2024'!A1.

Status bar can show Average, Count, Numerical Count, Min, Max, Sum of the current selection without writing a formula.

Excel stores dates as serial numbers starting at 1 = 1900-01-01; time is a fraction of a day, so 12:00 = 0.5.

Text that looks numeric (leading zeros, apostrophe prefix) is stored as text and is ignored by SUM; use VALUE or Text to Columns to convert.

Fill Handle drag copies cells; Ctrl+drag increments series. AutoFill recognizes patterns such as weekdays, months, and numeric steps.

Paste Special options: Values, Formulas, Formats, Column Widths, Transpose, Add/Subtract/Multiply/Divide, Skip Blanks.

Number formats change display only, never the stored value. Custom format sections are positive;negative;zero;text.

Conditional Formatting applies formats by rule; 'Stop If True' halts evaluation of lower-priority rules for a matching row.

Merge & Center keeps only the upper-left value; use Center Across Selection to align without destroying individual cells.

Move a sheet with drag or right-click > Move or Copy; check 'Create a copy' to duplicate. Sheet order affects 3-D references like SUM(Jan:Dec!B2).

Data validation restricts entry by list, whole/decimal number, date, text length, or custom formula; input and error messages are configurable per cell.

Operator precedence: reference operators (: space ,) first, then negation, %, ^, * and /, + and -, then & and comparison operators (= < > <= >= <>).

Error codes: #DIV/0! divide by zero, #N/A lookup miss, #VALUE! wrong type, #REF! deleted reference, #NAME? unrecognized name, #NUM! invalid numeric, #NULL! bad range intersection.

VLOOKUP(lookup, table, col_index, [FALSE]) searches the first column only and cannot look left; INDEX+MATCH or XLOOKUP removes that limit.

IF(logical_test, value_if_true, value_if_false) nests up to 64 levels; IFS and SWITCH provide flatter alternatives.

SUMIFS/COUNTIFS/AVERAGEIFS use criteria pairs (range, criteria) and are order-independent; SUMIF/COUNTIF take a single criterion.

A1 vs R1C1: R1C1 shows relative position as R[-1]C[2]; toggling the reference style changes how formulas display, not what they compute.

Mixed references let one formula fill both directions: =$A2*B$1 locks the column of the row header and the row of the column header.

Named ranges substitute a label for an address; names cannot look like cell references (e.g. 'A1') and are workbook-scoped by default.

Circular reference = a formula that depends on its own cell, directly or through a chain; Excel flags it and iteration must be enabled to resolve it.

Chart type by purpose: column/bar for comparison, line for trend over time, pie/doughnut for parts of one whole, scatter (XY) for correlation of two numeric variables, histogram for distribution.

A scatter chart uses numeric X values; a line chart treats X as evenly spaced categories - a line chart cannot show a true numeric X axis.

Chart elements: chart area, plot area, data series, category axis, value axis, legend, data labels, gridlines, trendline.

Excel Tables (Ctrl+T) auto-expand, carry structured references like Table1[Sales], and enable banded rows, total row, and slicers.

Structured reference symbols: [Column] single column, [#All], [#Data], [#Headers], [#Totals], [@Column] current row.

PivotChart stays linked to its PivotTable; changing the PivotTable fields re-renders the chart.

Secondary axis is required when two series have very different scales; it is set per series, not per chart.

Sparklines (line, column, win/loss) fit inside a single cell and reference one row or column of data.

Sort levels are applied in order; each level can use values, cell color, font color, or icon. Sort is stable only when a unique key is included.

AutoFilter filters rows by value, color, or criteria; Advanced Filter can copy results elsewhere and supports AND across columns, OR within a column.

Subtotal inserts outline levels and uses the SUBTOTAL function, which ignores rows hidden by a filter; SUM does not.

Goal Seek solves one input for one target value (single-variable); it cannot solve for multiple inputs.

Data Table (one- or two-variable) recalculates a formula across a grid of input values; Scenario Manager stores named sets of changing cells.

Solver handles multi-variable optimization with constraints and objectives (max, min, value); it needs the Solver add-in enabled.

PivotTable areas: Rows, Columns, Values, Filters. Values default to SUM for numbers and COUNT for text; Show Values As offers % of total, running total, rank.

Descriptive Statistics and Regression live in the Analysis ToolPak add-in; CORREL and LINEST compute the same core results without it.

Remove Duplicates and Text to Columns (delimited or fixed width) are the standard cleanup steps before analysis.

Relational model: tables (relations) of rows (tuples) and columns (attributes); each row should be uniquely identified by a primary key.

Primary key = unique, non-null identifier. Foreign key = a column referencing another table's primary key, enforcing referential integrity.

Normalization: 1NF removes repeating groups, 2NF removes partial dependence on part of a composite key, 3NF removes transitive dependence on non-key columns.

Cardinality: one-to-one, one-to-many, many-to-many. A many-to-many relationship requires a junction (linking) table.

NULL means unknown, not zero or blank; NULL = NULL is unknown, so use IS NULL / IS NOT NULL.

ACID: Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions don't interfere), Durability (committed data survives failure).

A view is a stored query presented as a virtual table; it holds no data of its own. An index speeds reads but slows writes and consumes storage.

Candidate key = any minimal unique key; the chosen one becomes the primary key, the others become alternate keys.

Clause order: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY. WHERE filters rows before grouping; HAVING filters groups after aggregation.

Logical evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY - which is why a SELECT alias can be used in ORDER BY but not in WHERE.

Joins: INNER returns matching rows only; LEFT/RIGHT keep all rows from one side with NULLs; FULL keeps both; CROSS produces the Cartesian product.

Aggregates: COUNT(*), COUNT(column) (ignores NULL), SUM, AVG, MIN, MAX. Every non-aggregated SELECT column must appear in GROUP BY.

DISTINCT removes duplicate result rows; it applies to the whole selected row, not to a single column.

INSERT INTO t (cols) VALUES (...); UPDATE t SET col = value WHERE condition; DELETE FROM t WHERE condition - omitting WHERE affects every row.

DDL vs DML: CREATE/ALTER/DROP define structure; SELECT/INSERT/UPDATE/DELETE manipulate data. GRANT/REVOKE control privileges.

LIKE wildcards: % any sequence, _ one character. BETWEEN is inclusive. IN tests membership in a list or subquery.

Subqueries can appear in WHERE, FROM (derived table), or SELECT (scalar). EXISTS tests whether the subquery returns any row.

Delimited text (CSV) vs fixed-width: CSV uses a separator and quotes fields containing it; fixed width pads each field to a set position.

Power Query (Get & Transform) steps are recorded and re-runnable; 'Close & Load' can send results to a worksheet table or the data model.

Import settings that matter: delimiter, text qualifier, encoding (UTF-8 vs ANSI), locale for decimal/thousands separators, and column data types.

Unpivot in Power Query converts wide cross-tab columns into normalized attribute/value rows - the standard step before loading to a database.

Validation layers: source system constraints, import type checking, then spreadsheet-side Data Validation and lookup checks against a reference list.

Common integrity checks after import: row count reconciliation, duplicate detection on the key, NULL scan on required columns, referential check against parent table.

Append combines tables with identical columns; Merge joins on key columns (inner, left anti, full outer) similar to SQL joins.

Export options: CSV (text, loses types and formatting), XLSX (keeps formulas and formats), PDF (fixed layout, not re-importable).

ODBC/OLEDB connections let Excel query external databases directly; refresh re-runs the query but does not fix changed source schemas.