ICDL Advanced Spreadsheets common mistakes
ICDL Advanced Spreadsheets · Understand the traps and how to avoid them.
Typing a currency symbol, percent sign, or thousands separator directly into cells (e.g. entering "$1,250" or "15%") so the values become text and stop calculating.
The task reads like a formatting instruction, and typing the symbol feels like the fastest way to make the screen look right; the cell then left-aligns and SUM/AVERAGE silently ignores it.
What to do instead
Enter the plain number only, then apply the display format via Format Cells > Number (Currency/Percentage) or the Number Format gallery on the Home tab; verify by checking that the value right-aligns and that a SUM over the column picks it up.
Spacing out content to make it look aligned or wrapping text by inserting line breaks, instead of using alignment, indent, merge-and-center, and wrap text settings.
It produces an acceptable visual result on screen at one zoom level, so the candidate moves on and never revisits the cell.
What to do instead
Clear the padding, keep one value per cell, and use Home > Alignment tools: Wrap Text, Increase Indent, horizontal/vertical alignment, and Merge & Center for headings; then set column width or row height so nothing is truncated.
Filling a formula down a column when it contains a lookup range or fixed reference, without anchoring that reference, so later rows return #N/A or wrong results.
Excel enters every reference as relative by default and the first row looks correct, hiding the error until the sheet is inspected further down.
What to do instead
Before filling, select the reference in the formula bar and press F4 to cycle to absolute ($A$1:$B$20) or the correct mixed reference, then fill down with the fill handle or Ctrl+D and spot-check the last row.
Building formulas with hard-coded numbers or hand-typed ranges (e.g. =B2*0.2, =SUM(B2:B9)) instead of referencing cells and full column/table ranges.
It is quicker to type the visible numbers, and the result is correct at that moment, so nothing signals a problem.
What to do instead
Place rates and thresholds in their own labelled cells and reference them absolutely; use whole-column or structured table references (Table1[Amount]) so newly added rows are included automatically, then test by adding a row.
Creating a chart from a selection that omits or wrongly includes header cells, leaving the legend reading "Series1" and the category axis numbered 1, 2, 3.
Dragging over the data block is faster than checking the range, and Excel's auto-detection guesses wrong when the headers are text or the first column is numeric.
What to do instead
After inserting the chart, use Chart Design > Select Data: set the correct data range, then edit each series name to point at its header cell and set Horizontal (Category) Axis Labels to the label column.
Sorting with only the key column selected so that one column is reordered while the rest of each record stays put, silently mismatching names to values.
Clicking a single cell and pressing Sort A-Z feels natural, and Excel's "Expand the selection?" prompt is often dismissed with the default without reading it.
What to do instead
Select one cell inside the block and choose Sort, confirm "Expand the selection", tick "My data has headers", then set Sort by / Order in the dialog; or convert the range to a table (Ctrl+T) before sorting so rows always move together.
Copying the results of an AutoFiltered list and pasting elsewhere, which brings the hidden rows along with the visible ones.
The clipboard copies the whole range regardless of the filter, and the pasted block looks plausible because the hidden rows are silently interleaved.
What to do instead
Select the visible range, then use Alt+; (Select Visible Cells Only) or Home > Find & Select > Go To Special > Visible cells only before copying; verify the pasted row count matches the visible record count.
Applying a data validation list to the wrong range or leaving the Error Alert and Input Message tabs unconfigured, so invalid entries are still accepted or users get no guidance.
The Settings tab is the only one that shows by default, and the range is often set before the last row of data has been confirmed.
What to do instead
Select the exact input range (whole column or full table field), then on the Error Alert tab choose Stop with a written message and on the Input Message tab add a prompt; test by typing an invalid value and confirming it is rejected.
Protecting the worksheet without first unlocking the cells users are meant to fill in, so every cell on the sheet becomes read-only.
Every cell is Locked by default, and the lock setting is invisible until protection is switched on, so the mistake only surfaces after the sheet is protected.
What to do instead
Select the input cells, press Ctrl+1 > Protection tab, clear Locked, click OK, then Review > Protect Sheet with the required options and password; test that an input cell accepts typing while a formula or label cell does not.
Printing or exporting with default page setup: no print area, the table splitting awkwardly across pages, header rows printed only on page 1, and gridlines missing.
The task is judged on screen, so page layout is the last thing checked, and Print Preview is often skipped entirely before finishing.
What to do instead
Use Page Layout > Print Area > Set Print Area, Print Titles > Rows to repeat at top, Page Setup to set orientation and Fit to 1 page wide, and tick Print > Gridlines/Headings; confirm the result in Print Preview before submitting.