ICDL Advanced Spreadsheets cheat sheet

A custom number format has up to four sections separated by semicolons: positive;negative;zero;text.

0 forces a digit, # shows a digit only when significant, a comma after a placeholder scales by thousands, and % displays the value times 100.

Date codes: d = day, m = month, yyyy = year; inside time formats h = hour, m = minute, s = second.

Conditional formatting formula rules start with = and use relative references based on the top-left cell of the applied range; $ locks a row or column.

A cell shows ##### when the column is too narrow for a number or date; widen the column to display the value.

Numbers aligned left are usually stored as text; convert with VALUE, Text to Columns, or Paste Special Multiply.

Format Painter copies all formatting; double-click the button to apply it to multiple ranges and press Esc to cancel.

Merged cells keep only the top-left value and can break sorting, filtering, and pivot operations; Center Across Selection centers without merging.

Wrap Text increases row height automatically; Alt+Enter inserts a manual line break inside a cell.

Operator precedence: reference operators, negation, %, ^, * and /, + and -, &, then comparisons = < > <= >= <>.

A1 is relative; $A$1 is absolute; $A1 locks the column only; A$1 locks the row only.

=IF(logical_test, value_if_true, value_if_false) returns one of two values; nested IF or IFS handles more conditions.

=VLOOKUP(lookup, table, col_index, FALSE) requires an exact match; TRUE or 1 uses approximate match and needs the first column sorted ascending.

=INDEX(return_range, MATCH(lookup, lookup_range, 0)) works for leftward lookups and is unaffected by inserted columns.

SUMIFS, COUNTIFS, and AVERAGEIFS require every criteria range to be the same size as the sum or average range.

Criteria with operators are entered as text such as >=100; wildcards * and ? apply to text criteria, with ? matching one character.

Error values: #DIV/0! divide by zero; #N/A lookup not found; #VALUE! wrong type; #REF! deleted reference; #NAME? unrecognized name.

& joins text; TEXTJOIN joins ranges with a delimiter and can ignore empty cells; CONCAT joins ranges without a delimiter.

Column charts compare categories, line charts show trends over time, pie charts show parts of one whole, and scatter XY charts show relationships between two numeric variables.

The Select Data Source dialog controls the chart data range; Switch Row/Column swaps which orientation is treated as series.

Use a secondary axis when series have very different scales; right-click the series and choose Format Data Series > Secondary Axis.

Link a chart title to a cell by selecting the title, typing = in the formula bar, and clicking the cell.

Data labels show values; Format Data Labels can also show percentages, category names, or values from cells.

Axis bounds and major units are set in Format Axis; a logarithmic scale suits data spanning orders of magnitude.

Sparklines inserted from Insert > Sparklines place a mini trend chart inside a single cell.

Trendline types include linear, exponential, logarithmic, and polynomial; an R-squared value near 1 indicates a close fit.

A combo chart assigns different chart types to series and often pairs a secondary axis with a line series.

Create an Excel table with Ctrl+T; structured references such as Table1[Sales] expand automatically as rows are added.

Sort supports multiple levels via Add Level; a custom list sorts by a user-defined order rather than alphabetically.

AutoFilter filters a range in place; Advanced Filter uses a separate criteria range and can copy results to another location.

In a criteria range, headings must match source headings; conditions on one row are AND, while conditions on separate rows are OR.

PivotTable areas are Rows, Columns, Values, and Filters; Values defaults to Sum for numbers and Count for text.

Value Field Settings change the summary function and Show Values As options such as percent of total or running total.

Pivot tables do not refresh automatically; use Refresh or Refresh All after source data changes.

Group date fields in a pivot table by years, quarters, or months; slicers and timelines filter visually.

Remove Duplicates deletes rows with matching values in selected columns; Subtotal inserts grouped totals and outline levels.

Data validation types include whole number, decimal, list, date, time, text length, and custom formula.

A list source can be a range, a named range, or comma-separated values typed directly; enable In-cell dropdown to show choices.

A custom validation formula must return TRUE to accept input; references are relative to the active cell of the validated range.

Error Alert styles: Stop blocks invalid entry, Warning asks for confirmation, and Information displays a message without blocking.

An Input Message appears when the cell is selected; it provides guidance and does not block entry.

Circle Invalid Data marks values that violate current validation rules; Clear Validation Circles removes the marks.

Dependent dropdowns use INDIRECT with named ranges matching the first list choices, or a FILTER-based dynamic list.

Paste Special > Validation copies only validation rules to another range.

Validation is not security; users can paste over validated cells unless the sheet is protected.

All cells are locked by default, but locking applies only when the sheet is protected; unlock input cells before protecting.

Review > Protect Sheet sets a password and chooses allowed actions such as selecting cells, formatting, sorting, and using AutoFilter.

Format Cells > Protection > Hidden conceals formulas in the formula bar only when the sheet is protected.

Allow Edit Ranges permits specific users or ranges to be edited on a protected sheet without unprotecting it.

Protect Workbook restricts structural changes: adding, deleting, hiding, renaming, or moving sheets.

Page Setup controls orientation, paper size, margins, scaling such as Fit to 1 page wide, print area, and print titles.

Print Titles repeats chosen rows at the top or columns at the left on every printed page.

Headers and footers support codes such as &[Page], &[Pages], &[Date], and &[File].

Export to PDF via File > Export or Save As PDF; check print area and page breaks before exporting.