Computer Specialist in Spreadsheet & Database Level 1 flashcards

33 cards. Read the term and the definition here. The deck at the top of this page is optional.

Workbook vs worksheet

A workbook is the spreadsheet file itself; a worksheet is a single tab or grid inside it. One workbook can contain many worksheets and chart sheets.

Name Box

The box left of the formula bar showing the active cell address. Typing a range such as B7:D20 there selects that range immediately.

Freeze Panes

Locks rows above and columns left of the active cell so they stay visible while scrolling. It freezes by position, not by content or header status.

Absolute reference

A cell address with dollar signs, like $A$1, that stays fixed when a formula is copied. F4 cycles between absolute, mixed, and relative forms.

Date serial number

Excel stores each date as a whole number counting from 1 = January 1, 1900, and each time as a fraction of a day, so noon equals 0.5.

Paste Special Transpose

A paste option that swaps rows and columns of the copied range. It is applied at paste time and does not alter the original source data.

Conditional Formatting

Applies cell formats automatically when a rule is true. Rules have priority order, and Stop If True prevents lower-priority rules from applying.

Custom number format sections

A custom format holds up to four sections separated by semicolons: positive;negative;zero;text. Each section formats only values matching its condition.

Data validation list

A rule restricting a cell to values from a defined list or range. It can also show an input message and a custom error alert.

Three-D reference

A reference spanning multiple consecutive worksheets, written as SUM(Jan:Dec!B2). It totals the same cell across every sheet in that tab range.

#REF! error

Appears when a formula refers to a cell or range that no longer exists, typically after deleting the rows or columns the reference pointed to.

VLOOKUP limitation

VLOOKUP searches only the first column of its table and returns a column to the right, so it cannot look leftward. INDEX with MATCH avoids this.

IFS function

Evaluates multiple condition-result pairs in order and returns the result for the first true condition, replacing deeply nested IF statements.

SUMIFS arguments

SUMIFS(sum_range, criteria_range1, criteria1, ...) adds values meeting every criterion. Criteria pairs are order-independent and combine with AND logic.

Circular reference

A formula that depends on its own cell, directly or through a chain of other formulas. Excel warns and cannot calculate until the loop is broken.

Scatter chart

Plots numeric X against numeric Y to reveal correlation. Unlike a line chart, it treats the horizontal axis as real numbers, not evenly spaced categories.

Structured reference

Table-aware formula syntax such as Table1[Sales] or [@Units]. It expands automatically as rows are added and stays readable instead of using cell addresses.

Secondary axis

An extra value axis placed on the right of a chart for a series whose scale differs greatly from the others. It is set per series.

Sparkline

A miniature chart drawn inside a single cell from one row or column of data. Types are line, column, and win/loss.

SUBTOTAL function

Aggregates a range while ignoring rows hidden by a filter. Function numbers 1-11 include manually hidden rows; 101-111 exclude them.

Goal Seek

Finds the single input value that makes a formula reach a target result. It handles one variable only and cannot apply constraints.

Solver add-in

An optimization tool that adjusts multiple changing cells to maximize, minimize, or reach a target while honoring defined constraints.

PivotTable Values area

The field well that aggregates data. Numbers default to SUM and text to COUNT; Show Values As offers percent of total, running total, and rank.

Advanced Filter

Filters rows using a separate criteria range. Conditions on the same row combine with AND; conditions on different rows combine with OR.

Primary key

A column or column set that uniquely and permanently identifies each row of a table. It cannot contain NULL values or duplicates.

Foreign key

A column whose values must match a primary key in another table, enforcing referential integrity between parent and child tables.

Third normal form

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column, eliminating transitive dependencies.

Junction table

A table that resolves a many-to-many relationship by holding foreign keys to both parent tables, often with extra attributes of the pairing.

WHERE vs HAVING

WHERE filters individual rows before grouping; HAVING filters aggregated groups after GROUP BY. Aggregate functions belong in HAVING, not WHERE.

INNER JOIN

Returns only rows where the join key matches in both tables. Unmatched rows from either side are dropped from the result set.

COUNT versus COUNT(*)

COUNT(column) counts non-NULL values in that column; COUNT(*) counts all rows regardless of NULLs. Results differ whenever NULLs exist.

Power Query Unpivot

Transforms wide cross-tab columns into normalized attribute-and-value rows, producing a structure suitable for loading into a relational table.

CSV import encoding

The character set used to read a text file, such as UTF-8 or ANSI. Choosing the wrong encoding corrupts accented and non-Latin characters on import.