ICDL Spreadsheets (ICDL) cheat sheet

A worksheet is a grid of columns (lettered) and rows (numbered); the intersection is a cell addressed as column plus row, for example C7.

Common file formats: .xlsx (Excel workbook), .ods (OpenDocument spreadsheet), .csv (plain comma-separated text) and .pdf (fixed layout, not editable as a spreadsheet).

Save (Ctrl+S) overwrites the current file in its existing name and location; Save As writes a copy and allows a new name, folder or file format.

Decision rule: choose CSV only for exchanging plain values, because it keeps numbers and text but drops formulas, formatting, charts and all but one worksheet.

Keyboard shortcuts: Ctrl+N new, Ctrl+O open, Ctrl+S save, Ctrl+P print, Ctrl+Z undo, Ctrl+Y redo, Ctrl+F find, Ctrl+H replace.

The Name Box shows the address of the active cell and accepts a typed address to jump there; the Formula Bar shows the stored content of the cell, not its displayed result.

Zoom changes only the on-screen view; it does not alter row height, column width, font size or print scaling.

A template is a pre-built workbook (built-in or user-saved) that supplies layout, labels and formulas so recurring documents start from a fixed structure.

A format compatibility warning appears when saving to an older format that cannot store newer features; the features are lost or converted if saving continues.

A range is written with a colon between two corner addresses: A1:A10 is a single column of ten cells, A1:C3 is a rectangular block of nine cells.

Shift+click extends the selection from the active cell; Ctrl+click adds separate, non-adjacent cells or ranges to the selection.

Copy (Ctrl+C) marks the selection with a marquee; Paste (Ctrl+V) transfers content, formulas and formatting, while Paste Special can insert values only, formulas only, formats only or a transposed layout.

The fill handle is the small square at the bottom-right of a selection; dragging it copies the cell or continues a recognised series such as Jan, Feb, Mar or 2, 4, 6.

Holding Ctrl while dragging the fill handle forces a numeric increment; dragging without Ctrl repeats the original value.

Insert and Delete on whole rows or columns shift the neighbouring cells; deleting part of a row or column prompts whether to shift remaining cells left or up.

Undo (Ctrl+Z) reverses actions one at a time in reverse order; Redo (Ctrl+Y) reapplies them.

Find (Ctrl+F) locates text or values; Replace (Ctrl+H) substitutes them, and options such as Match case or Entire cell narrow the matches.

Pressing Delete clears the content of a cell but keeps its formatting; the Clear All command removes content and formatting together.

A workbook is one file containing one or more worksheets; each worksheet is identified by a tab at the bottom of the window.

Double-click a tab to rename it; right-click the tab to reach Insert, Delete, Rename, Move or Copy, Tab Colour and Hide.

In the Move or Copy dialog, ticking Create a copy duplicates the sheet; leaving it cleared moves the original sheet to the chosen position or workbook.

Ctrl+click or Shift+click on tabs groups worksheets, so typing or formatting applies to every sheet in the group; right-click and Ungroup (or click another tab) releases the group.

Freeze Panes locks the rows above and the columns to the left of the active cell so those headings stay visible while the rest of the sheet scrolls.

Freeze Top Row and Freeze First Column are single-step shortcuts when only one row or one column must remain on screen.

Split divides the window into independently scrolling panes without locking them, and the split bar can be dragged to resize the panes.

Insert Sheet Rows adds rows above the current selection; column width and row height can be set by dragging a boundary, double-clicking it to auto-fit, or typing a value.

Hide Rows/Columns removes them from view without deleting data; select the surrounding rows or columns and choose Unhide to restore them.

Every formula starts with = ; an entry without it, such as 2+2, is stored as text rather than calculated.

Operator precedence: parentheses first, then ^ (exponent), then * and /, then + and -, with equal-priority operators evaluated left to right.

Relative references such as B2 change when copied; $B$4 is fully absolute, B$4 fixes the row, and $B2 fixes the column.

Core functions: =SUM(range), =AVERAGE(range), =MIN(range), =MAX(range), =COUNT(range) for numeric cells and =COUNTA(range) for all non-empty cells.

=IF(condition, value_if_true, value_if_false) returns one of two results; conditions use =, <, >, <=, >= and <> (not equal to).

=ROUND(number, digits) rounds to a set number of decimal places; ROUNDUP always rounds away from zero and ROUNDDOWN always toward zero.

Error values: #DIV/0! division by zero or an empty divisor, #REF! a deleted reference, #NAME? an unrecognised function or name, #VALUE! a wrong argument type, #N/A a value not found.

=COUNTIF(range, criteria) counts matching cells; =SUMIF(range, criteria, sum_range) totals the values that meet the criterion.

AutoSum inserts =SUM with a guessed range, so the highlighted range must be checked before pressing Enter to confirm.

Number formats change only how a value is displayed; the stored value used in calculations is unchanged.

The Percentage format multiplies the display by 100 and adds a % sign, so the stored 0.25 is shown as 25%.

Increase Decimal and Decrease Decimal alter the number of displayed decimal places without altering the underlying value.

Dates and times are stored as serial numbers; the chosen date format determines how that number appears in the cell.

Wrap Text breaks a long entry onto several lines inside one cell and increases row height, leaving column width unchanged.

Merge & Center joins selected cells into one cell and centres the content; merged cells interfere with sorting and filling, so Center Across Selection is preferred for headings.

Format Painter copies only formatting (font, colour, borders, number format) from a source cell to the target cells.

Conditional formatting applies a format automatically when a rule is met, such as greater than a value, top ten items, a colour scale or a data bar.

A row of #### in a cell means the column is too narrow to display the number; the value is intact and widening the column reveals it.

Pie charts show the parts of a single whole data series and cannot plot negative values; the slices should total 100 per cent.

Column and bar charts compare categories: column charts run vertically, bar charts run horizontally.

Line charts show a trend across ordered categories such as months; XY (scatter) charts show the relationship between two numeric variables.

A chart is linked to its source range, so editing the source cells updates the plotted values and labels automatically.

Categories are the labels along an axis; series are the plotted values, normally one series per source column or row.

Switch Row/Column swaps which dimension of the source range supplies series and which supplies categories when the chart is plotted the wrong way round.

The legend identifies each series by colour, and data labels place exact values next to the plotted points or bars.

Title, axis titles, gridlines, labels and trendlines are added through Add Chart Element or the chart's + button.

A chart is either embedded in a worksheet, where it moves and resizes with the cells, or placed on a separate chart sheet of its own.

Print Preview shows the exact printed result, including margins, headers, footers, scaling and page breaks, before anything is sent to the printer.

Print Area (Page Layout > Print Area > Set Print Area) restricts output to a defined range; Clear Print Area restores printing of the whole sheet.

Orientation selects Portrait for tall pages or Landscape for wide tables; margins and centring are set in the same Page Setup dialog.

Scaling options are Fit Sheet on One Page, Fit All Columns on One Page, Fit All Rows on One Page, or a custom percentage of normal size.

Print Titles repeats chosen rows at the top and chosen columns at the left of every printed page so headings stay visible on long tables.

Headers and footers accept codes: &P current page, &N total pages, &D current date, &T current time, &F file name, &A sheet name.

Gridlines and row/column headings are screen-only by default and must be switched on in Page Setup to appear on the printed page.

A manual page break is inserted above and to the left of the active cell; Page Break Preview allows breaks to be dragged into position.

Exporting to PDF fixes the layout for the recipient and prevents editing of formulas, while Print Selection sends only the highlighted cells to the printer.