ICDL Spreadsheets (ICDL) cheat sheet

A workbook is the saved file; a worksheet is one grid or sheet inside it, and a single workbook can hold many worksheets.

The ribbon is organised into tabs (Home, Insert, Page Layout, Formulas, Data, Review, View), each holding groups of related commands.

The Quick Access Toolbar holds frequently used commands such as Save and Undo and stays visible whichever ribbon tab is active.

Ctrl+S saves the current file; F12 opens Save As so the file can be stored under a new name, location or file type.

Ctrl+F opens Find and Ctrl+H opens Find and Replace, which locates content and substitutes it one match or all matches at a time.

Files can be saved as .xlsx, .xls, .csv or .pdf; CSV keeps only values, with no formulas, formatting or extra sheets.

Zoom level and view modes (Normal, Page Layout, Page Break Preview) are set on the View tab and affect display only, never data.

A cell entry is confirmed with Enter, Tab or an arrow key; Esc cancels the entry and restores the previous content.

The Name Box, to the left of the formula bar, shows the active cell reference and can be typed into to jump to a cell or range.

A cell reference combines the column letter with the row number, for example C7; the current cell is shown in the Name Box.

Relative references (A1) shift when copied; absolute references ($A$1) stay fixed; mixed references lock one part only ($A1 or A$1).

F2 or a double-click enters edit mode inside the cell, while the formula bar always shows the underlying value or formula.

Hold Ctrl to select non-adjacent cells or ranges; hold Shift to extend a selection to a clicked cell.

The fill handle in the bottom-right corner of a selection copies content or continues a series such as months or numbers when dragged.

Delete removes cell contents only; Clear All removes content, formats and comments together.

Insert and Delete on rows or columns shift the surrounding cells right/down or left/up respectively; removed cells are not recoverable via Undo after saving.

Default alignment is left for text and right for numbers and dates, so a left-aligned number signals a value stored as text.

Wrap Text, Merge & Center and vertical/horizontal alignment are controlled from the Alignment group on the Home tab.

Insert, Delete, Rename, Move, Copy and Hide are all available from the right-click menu on a worksheet tab.

A sheet name is limited to 31 characters and cannot contain the characters \ / ? * [ ] :

Dragging a sheet tab moves it; holding Ctrl while dragging creates a copy of the sheet with all its data and formatting.

Selecting several tabs with Ctrl or Shift groups them, so typing or formatting applies to every grouped sheet at once.

Right-click a tab and use Move or Copy with Create a copy ticked to duplicate a sheet within the workbook or into another workbook.

Freeze Panes keeps chosen rows or columns on screen while scrolling; Split divides the window into independently scrollable panes.

Hide/Unhide, Tab Color and Protect Sheet are reached from the tab context menu or the Review tab.

A 3-D reference such as =SUM(Sheet1:Sheet3!B2) aggregates the same cell across a range of consecutive sheets.

Every formula starts with = ; precedence runs exponentiation (^), then multiplication and division (* /), then addition and subtraction (+ -).

Brackets override precedence and are evaluated first: =(A1+A2)*3 adds before multiplying.

SUM, AVERAGE, MIN, MAX and COUNT aggregate a range; COUNT counts numeric cells only, whereas COUNTA counts all non-empty cells.

IF(logical_test, value_if_true, value_if_false) returns one of two results, for example =IF(B2>=50,"Pass","Fail").

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

ROUND(number, digits) rounds to a set number of decimals; ROUNDUP and ROUNDDOWN force the direction of rounding; INT truncates to a whole number.

Error values: #DIV/0! division by zero, #NAME? unrecognised name, #REF! deleted or invalid reference, #VALUE! wrong argument type, ##### column too narrow.

A circular reference occurs when a formula refers back to its own cell; it triggers a warning and usually returns 0.

The fx (Insert Function) dialog guides entry of a function's arguments and can be used to nest one function inside another.

Ctrl+1 opens Format Cells, with tabs for Number, Alignment, Font, Border, Fill and Protection.

In number codes, 0 forces a digit to display, # shows a digit only when significant, a comma inserts thousands separators and % multiplies the display by 100.

Number formatting changes only what is displayed; the stored value used in calculations is unaffected.

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

Format Painter copies formatting from one cell or range to another; double-click it to apply the format to several separate places.

Conditional formatting applies formats automatically when a value meets a rule, such as turning all scores below 50 red.

Merge & Center combines selected cells into one; only the upper-left value survives and the other contents are discarded.

Double-clicking the boundary between column headers auto-fits the column width to its widest entry; the same applies to row height.

Cell styles apply a named combination of font, fill, border and number format in a single action.

Column and bar charts compare values across categories; line charts show trends over time; pie charts show parts of one whole.

A chart contains a chart area, plot area, data series, category (X) axis, value (Y) axis, legend and optional titles.

Select the source range, then Insert > Chart; the chart redraws automatically when the underlying data changes.

Change Chart Type on the Chart Design tab switches chart type without re-selecting the source data.

Switch Row/Column swaps which data is plotted as series and which as categories when a chart appears transposed.

A legend identifies the colour or pattern for each series and can be repositioned or removed via Add Chart Element.

A secondary axis is used when two series have very different scales, such as revenue in thousands and units sold.

Sparklines are miniature in-cell charts (Insert > Sparklines) that show a trend without creating a chart object.

A chart can be embedded as an object on a worksheet or placed on its own separate chart sheet tab.

The Page Layout tab sets margins, orientation (portrait or landscape), paper size, print area and scaling.

Print Titles repeats chosen rows at the top or columns at the left of every printed page of a long worksheet.

Page Break Preview shows page boundaries and lets you drag the blue lines to reposition breaks.

Fit to 1 page wide (Scaling in Page Setup) shrinks output so all columns fit one page width; the alternative is Adjust to a percentage.

Headers and footers, added from the Insert tab or Page Setup, can carry page numbers, date, file name and title.

Gridlines and row/column headings print only when their checkboxes are ticked in the Sheet Options group.

Ctrl+P or File > Print opens Print Preview, where the final layout, page range and number of copies are confirmed before printing.

A workbook or sheet is exported to PDF via File > Export or Save As with PDF selected, preserving the page layout.

Spell check (F7) and the Thesaurus on the Review tab verify text before the file is printed or distributed.