ICDL Spreadsheets (ICDL) cheat sheet

A workbook is the saved file; worksheets (tabs) live inside it. Default new workbook contains one or more sheets depending on version settings.

Relative reference (A1) shifts when copied; absolute reference ($A$1) stays fixed; mixed ($A1 or A$1) locks only column or only row.

The Name Box (left of the formula bar) shows the active cell address and accepts a typed address or range to jump to it.

Ctrl+S saves; F12 or Save As creates a copy with a new name, location, or file type such as .csv or .pdf.

Undo (Ctrl+Z) reverses the last action; Redo (Ctrl+Y) reapplies it. Undo history is cleared when the file is closed.

Freeze Panes keeps chosen rows/columns visible while scrolling; it must be applied below and to the right of the cells to remain fixed.

AutoFill: dragging the fill handle copies values or continues a series (Months, 1,2,3) depending on the recognized pattern.

The formula bar displays the underlying content of the active cell, which may differ from the displayed formatted value.

A cell address combines column letter and row number (e.g., C7); a range is written with a colon (A1:C10) and includes all cells between corners.

Select non-adjacent ranges by holding Ctrl while dragging or clicking each range.

Cut (Ctrl+X) moves content and adjusts references pointing to it; Copy (Ctrl+C) duplicates; Paste Special can paste values, formats, or transpose.

Insert or delete rows/columns shifts existing data; deleting cells offers shift options (shift cells left/up).

To clear only formatting, use Clear Formats; Delete key removes contents but leaves formatting intact.

Wrap Text displays long text on multiple lines within a cell without changing the column width.

Merge & Center combines selected cells into one; only the upper-left value is kept, and merged cells cannot be sorted normally.

A cell displaying #### means the column is too narrow for a numeric or date value, not that data is lost.

Insert a new sheet via the plus icon or Insert > Worksheet; the new tab is placed relative to the active tab.

Rename a sheet by double-clicking its tab or right-click > Rename; sheet names cannot exceed 31 characters and cannot contain : \ / ? * [ ].

Move or copy a sheet by dragging its tab, or right-click > Move or Copy; checking 'Create a copy' duplicates it within the workbook.

A 3D reference such as =SUM(Jan:Mar!B2) totals the same cell across a contiguous range of sheets.

Cross-sheet reference syntax: =Sheet2!A1; if the sheet name has spaces, enclose it in single quotes: ='Sales Data'!A1.

Hiding a sheet removes its tab from view but keeps its data and references intact; Unhide restores it.

Grouping sheets (Ctrl+click tabs) applies edits and formatting to all grouped sheets simultaneously.

Deleting a worksheet removes its data permanently and cannot be undone after the file is saved; references to it return #REF!.

Every formula begins with =; operators follow order of precedence: ^ then * / then + -, with parentheses overriding precedence.

Common functions: =SUM(range), =AVERAGE(range), =COUNT(range) counts numbers, =COUNTA counts non-empty cells, =MAX, =MIN.

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

=ROUND(number, digits) rounds to a set number of decimals; =ROUND(A1,0) rounds to a whole number.

Error values: #DIV/0! divides by zero or empty; #VALUE! wrong data type; #NAME? unrecognized name; #REF! invalid reference; #N/A value not available.

=SUMIF(range, criteria, sum_range) adds values meeting a condition; =COUNTIF(range, criteria) counts matching cells.

Relative references change when a formula is copied down or across; use $ to lock row, column, or both before copying.

=CONCATENATE or the & operator joins text: =A1&" "&B1 combines first and last names with a space.

Number formats change display only, not stored value: Currency, Percentage (multiplies display by 100), Date, and Number with fixed decimals.

The Format Painter copies formatting from a selected cell to other cells; double-click it to apply to multiple targets.

Cell alignment includes horizontal (left/center/right), vertical (top/middle/bottom), and text orientation/rotation.

Borders apply to cell edges; fill color and font color are separate attributes set in the Font or Fill tabs.

Increase/Decrease Decimal buttons change displayed decimal places without altering the underlying number.

Conditional formatting applies formats (color, icon, data bar) automatically when cell values meet defined rules.

A date stored as text aligns left and cannot be used in date arithmetic; convert it to a real date serial number to calculate.

Column width and row height auto-fit by double-clicking the boundary between column or row headers.

Column/bar charts compare categories; line charts show trends over time; pie charts show parts of a whole and should use a single data series.

A chart has a data source (range), category axis labels, a value axis, and one or more data series.

Select the data range before inserting a chart; non-adjacent ranges require Ctrl-selection or editing the series afterward.

Switch Row/Column changes which dimension is plotted as series versus categories.

Chart elements (title, axis titles, legend, data labels, gridlines) are toggled via Chart Elements or the chart's design tools.

Changing the source data updates the chart automatically because the chart is linked to the range, not a static snapshot.

Pie charts plot only the first series; additional series are ignored, so use a column chart for multi-series data.

A sparkline is a miniature chart placed inside a single cell to show a trend compactly.

Print Preview shows page breaks and layout before printing; Page Layout view displays headers, footers, and margins on screen.

Orientation choices are Portrait (tall) and Landscape (wide); choose based on the data's column count relative to rows.

Fit to Page scaling options: Fit to 1 page wide by N tall; scaling reduces print size but does not change cell values.

Print Titles repeat chosen rows or columns on every printed page for multi-page worksheets.

Print Area defines a fixed range to print; Clear Print Area restores printing of the whole used range.

Headers and footers can insert page numbers, date, file name, and sheet name via preset fields.

Gridlines do not print by default; enable Print Gridlines in Page Setup to include them.

Export to PDF via Save As or Export preserves layout and is the standard way to share a non-editable final copy.