Computer Specialist in Spreadsheet Level 2 common mistakes
Computer Specialist in Spreadsheet Level 2 · Understand the traps and how to avoid them.
Typing a cell reference like A1 into a formula without anchoring it, then copying the formula and getting wrong results or #REF! errors.
Candidates forget that relative references adjust when copied; they assume the reference stays fixed. This often happens when building a running total or a lookup table.
What to do instead
Before copying, decide whether the reference should stay fixed. Use F4 to cycle through absolute ($A$1), mixed (A$1, $A1), and relative (A1) references. Test by copying one cell and checking the result.
Entering numbers with currency symbols, commas, or spaces (e.g., $1,200) so they are stored as text, causing SUM and other functions to ignore them.
Candidates treat the cell as a word processor and type formatting characters directly. The cell shows the value but aligns left, and calculations return 0 or errors.
What to do instead
Enter raw numbers only (1200) and apply number formatting via the Format Cells dialog or the ribbon. Use the status bar or ISTEXT/ISNUMBER to verify data type; convert text-numbers with Text to Columns or VALUE.
Sorting a single column or a selected range without including all related columns, which scrambles row associations.
Candidates highlight only the column they want sorted and click Sort A-Z. The software warns about expanding the selection, but they confirm the current selection, breaking the table.
What to do instead
Click any single cell inside the data and use Sort; the entire contiguous range is detected automatically. Alternatively, select the whole table (Ctrl+A) or convert it to a Table (Ctrl+T) before sorting. Always verify row integrity after sorting.
Building a PivotTable from a range that has blank rows, blank columns, or text in numeric fields, resulting in missing categories or wrong aggregations.
Candidates assume the source is clean. PivotTables silently drop blank rows and treat numbers stored as text as text, so sums may show 0 or counts instead of sums.
What to do instead
Before inserting a PivotTable, remove blank rows/columns, ensure each column has a header, and convert text-numbers to numbers. Use a Table as the source so future rows are included. Refresh and check the field list for missing items.
Applying a filter but then copying only the visible cells incorrectly, pasting hidden rows as well.
Candidates select the filtered range and press Ctrl+C, assuming only visible cells are copied. By default, hidden rows are included, so pasted data contains unwanted rows.
What to do instead
After filtering, select the range and use Home > Find & Select > Go to Special > Visible cells only (or Alt+;), then copy. Alternatively, use Advanced Filter with 'Copy to another location' or copy from a Table, which copies only visible rows.
Using a chart type that misrepresents the data, such as a pie chart with many categories or a line chart for categorical comparison.
Candidates pick charts by appearance rather than by data relationship. Pie charts with more than a few slices become unreadable, and line charts imply continuity where none exists.
What to do instead
Match chart to purpose: column/bar for comparisons, line for trends over time, pie only for parts of a whole with few categories, scatter for correlation. Limit pie slices by grouping small categories into 'Other'.
Setting print area or page breaks without checking page layout, resulting in columns split across pages or repeated headers missing.
Candidates go straight to Print without using Page Layout or Print Preview. They do not set orientation, scaling, or print titles, so the printout is unprofessional.
What to do instead
Use Page Layout > Print Titles to repeat header rows, set orientation and scaling (Fit to 1 page wide), and define Print Area. Always check Print Preview before printing.
Sharing a workbook without tracking changes or protecting structure, leading to lost edits or broken formulas when multiple users edit.
Candidates assume collaboration is automatic. They overwrite each other's work, or others delete rows/columns that formulas depend on, causing #REF! errors.
What to do instead
Use co-authoring in OneDrive/SharePoint with version history, or Track Changes for legacy sharing. Protect the sheet/workbook structure with a password, and lock formula cells while leaving input cells unlocked.
Entering a formula that refers to a whole column (e.g., =SUM(A:A)) in a large workbook, causing slow calculation and potential memory issues.
Candidates learn whole-column references as a shortcut but do not realize they force calculation over a million rows, especially with volatile functions or array formulas.
What to do instead
Use structured references with Tables or limit ranges to the actual data (e.g., =SUM(A2:A1000)). Convert data to a Table so ranges expand automatically without whole-column references.
Renaming, moving, or deleting worksheets without updating formulas that reference them, resulting in #REF! errors or broken links.
Candidates reorganize sheets for clarity but forget that formulas contain sheet names. Deleting a sheet removes its data, and renaming breaks references unless done through the Rename command with formulas updated.
What to do instead
Before renaming or deleting, use Find & Replace (Ctrl+H) to update references, or rename via the sheet tab (which updates formulas automatically). For deletion, first move dependent formulas or convert to values. Check with Ctrl+` to show formulas.