Computer Specialist in Spreadsheet & Database Level 1 common mistakes
Computer Specialist in Spreadsheet & Database Level 1 · Understand the traps and how to avoid them.
When copying a formula down a column, the candidate lets relative references shift onto the wrong cells (or writes $A$1 when A1 was needed), so totals pull from header rows or blank cells and return 0 or #REF!.
They memorized that "$ locks a cell" without internalizing which part of the reference the copy operation changes. On a spreadsheet exam the mix of absolute, relative, and mixed references is the single most-tested mechanic, and candidates rush the copy step because the first cell looked correct.
What to do instead
Before filling, state out loud which references must stay fixed (tax rate, lookup table) and which must move (row data). Build one row, verify it by hand against two source cells, then fill and spot-check the last row. Practice the F4 cycle (A1 → $A$1 → A$1 → $A1) until you can predict the result of a copy without executing it.
Choosing the wrong chart type for the data shape - a pie chart for 12 categories or for non-additive values, a line chart for unordered categorical data, or a clustered column chart for a single time series that should be a line.
Candidates pick the chart that looks most decorative rather than the one that matches the relationship (composition vs. trend vs. comparison). Exam items often show only the data table and ask which chart communicates it best, so the decision must be made from the data structure, not from a picture.
What to do instead
Apply a decision rule: parts of a whole with few slices → pie/doughnut; trend over ordered time → line; comparison across categories → bar/column; correlation between two numeric variables → scatter. Reject pie charts with more than about five slices or with negative values, and confirm the category axis is genuinely ordered before choosing a line.
Building a lookup with an approximate-match VLOOKUP (or omitting the fourth argument) against an unsorted table, then accepting a wrong-but-plausible result instead of the expected exact match.
The fourth argument defaults to TRUE (approximate), and beginners do not know the default exists. Because approximate match returns the nearest lower value rather than an error, wrong answers look convincing and pass a casual visual check.
What to do instead
Always type the fourth argument explicitly as FALSE (or 0) for exact matching, and use TRUE only for genuine banded lookups such as tax brackets, where the first column must be sorted ascending. Verify by testing a value that should return "not found" - an exact-match lookup must return #N/A there. Prefer INDEX/MATCH or XLOOKUP when the return column sits left of the key.
Typing numbers, dates, or IDs as text (leading apostrophe, leading zeros stripped, dates stored as strings), so SUM ignores them, sorting is alphabetical, and lookups fail.
Imported files and copied data frequently arrive as text, and the cell looks normal because alignment is the only cue. Candidates fix the symptom (retyping a few cells) rather than the cause, and the error reappears in the next range.
What to do instead
Check alignment and use ISTEXT/ISNUMBER or the status bar count vs. sum to detect text numbers. Convert with Text to Columns, VALUE, or Paste Special → Multiply, then confirm the cell is right-aligned. For identifiers with leading zeros, set the column to Text before entry rather than after.
Treating a filtered or manually hidden range as the whole dataset - subtotals and charts include hidden rows, or a sort is applied to only the selected column and breaks row integrity.
Candidates select a column and click Sort, answering the visible question without noticing that adjacent columns stayed put. Similarly, they confuse hiding rows with filtering and expect AGGREGATE/SUBTOTAL behavior that does not apply.
What to do instead
Before sorting, select the entire contiguous table or convert it to a structured table so rows move together; verify by checking that a known row's values still line up. Use SUBTOTAL or AGGREGATE (which ignore filtered/hidden rows) instead of SUM when a filter is active, and confirm the filter indicator is on before trusting a total.
Writing a SELECT that returns duplicate rows or omits the join condition, producing a Cartesian product or inflated counts that the candidate reports as the answer.
Candidates translate the question into SQL clause by clause and stop when the query runs without error, without checking row counts. A missing WHERE join predicate or a missing DISTINCT is syntactically valid, so nothing signals the logical error.
What to do instead
After every query, compare the returned row count to an expected magnitude and inspect a few rows. Add DISTINCT only when duplicates are genuinely unwanted, and always pair joined tables with an explicit ON/WHERE condition. When counting, ask whether the count should be of rows or of distinct entities before writing COUNT(*).
Confusing the levels of a relational model - treating a table as a record, mixing primary and foreign keys, or assuming a foreign key must be unique - and then answering normalization or key questions from that flawed picture.
Spreadsheet thinking encourages one flat sheet per topic, so candidates map "row = record, column = field" loosely and never separate entity from attribute. Key terminology (candidate key, composite key, referential integrity) is learned as vocabulary rather than as behavior.
What to do instead
Draw the entities as boxes and the relationships as lines before answering. For each table, name the primary key and prove uniqueness; for each relationship, state which side holds the foreign key and whether it may repeat. Test normalization questions by asking whether a non-key column depends on the whole key or only part of it.
Building a PivotTable from a range that has blank header cells, merged cells, or mixed data types, then blaming the tool when fields are missing or values sum as zero.
PivotTables require a clean, single-header, rectangular list, and the failure message ("data source must be a single block") is vague. Candidates rebuild the pivot repeatedly instead of repairing the source.
What to do instead
Before inserting the pivot, confirm one header row with no blanks or merges, no blank rows inside the data, and consistent types per column. Convert the source to a structured table so it expands automatically, and refresh after any source edit. If a field is missing, check the header cell is non-blank and not merged.
Confusing the three what-if tools - using Goal Seek where a one-variable Data Table is needed, or expecting Scenario Manager to solve for an input - and misreading which cells are inputs versus results.
All three live under the same menu and all "change numbers to see results," so candidates select by name recognition. The critical distinction (solve for a target vs. tabulate outcomes vs. store named sets of inputs) is rarely rehearsed.
What to do instead
Map each tool to its question: "what input makes the result equal X?" → Goal Seek; "how does the result vary across a range of one or two inputs?" → Data Table; "compare stored alternative input sets?" → Scenario Manager. Identify the dependent formula cell first, then choose the tool that takes it as its target.
Importing a CSV or text file with default settings, so delimiters, date order, or text qualifiers are misread and columns land shifted or dates flip between day and month.
The import wizard's defaults reflect the machine's locale, and candidates click Finish without previewing. Because the file opens without error, the misalignment is only discovered later when aggregates look wrong.
What to do instead
Use the import wizard (Get Data / Text Import) rather than double-clicking the file, and preview the first ten rows at each step. Set the delimiter, text qualifier, and per-column data type explicitly, declaring date columns and text IDs as Text. After import, verify row count and check that the first and last records match the source file.