ICDL Data Analytics (ICDL) cheat sheet

Four analytics levels in ascending maturity: descriptive (what happened), diagnostic (why), predictive (what will happen), prescriptive (what to do).

Measurement scales: nominal = unordered labels; ordinal = ordered categories with unequal gaps; interval = equal differences but no true zero; ratio = equal differences plus a true zero (money, weight, counts).

A parameter describes a population; a statistic describes a sample and is used to estimate the parameter.

Mean is sensitive to extreme values; median is resistant to outliers; mode is the only valid central measure for nominal data.

Standard deviation is the square root of variance and is expressed in the same units as the data, which is why it is preferred for interpretation.

Correlation coefficient r lies between -1 and +1 and measures linear association only; r near 0 does not rule out a strong non-linear relationship.

Central Limit Theorem: the sampling distribution of the mean approaches normality as n grows; n >= 30 is a common rule of thumb for means.

A metric is any quantifiable measure; a KPI is a metric tied to a strategic objective with a target, an owner and a reporting cadence.

Data roles separate accountability: the data owner is accountable for a domain, the steward defines standards, and the custodian operates storage and access controls.

ETL transforms data before loading it into the warehouse; ELT loads raw data first and transforms it inside the warehouse using its own compute.

Normalisation: 1NF requires atomic values and no repeating groups; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies on non-key fields.

Data quality dimensions to test separately: accuracy, completeness, consistency, timeliness, validity and uniqueness.

Missing-value options include listwise deletion, pairwise deletion, mean or median imputation and multiple imputation; deletion shrinks the sample and can bias results if data are not missing at random.

IQR outlier rule: flag values below Q1 - 1.5 x IQR or above Q3 + 1.5 x IQR, where IQR = Q3 - Q1; a z-score threshold of |z| > 3 is a parametric alternative.

Join types differ by row retention: inner returns matches only, left keeps all left rows, right keeps all right rows, full outer keeps both, and unmatched fields become null.

Referential integrity requires every foreign key to match an existing primary key in the parent table; violations create orphan records.

Deduplication needs a defined key: exact matching on a primary or composite key, or fuzzy matching (similarity scoring) for near-duplicate names and addresses.

Text standardisation before matching or grouping: trim spaces, unify case, standardise date formats and resolve unit inconsistency so equal values compare as equal.

COUNT counts numeric cells only; COUNTA counts all non-empty cells including text; COUNTBLANK counts empty cells; COUNTIF and COUNTIFS count on one or more criteria.

Conditional aggregation: SUMIFS, AVERAGEIFS and COUNTIFS take criteria ranges paired with criteria, using operators and wildcards such as ">=1000" or "North*".

VLOOKUP searches only to the right of the lookup column and defaults to approximate match, so FALSE or 0 is needed for exact match; INDEX-MATCH and XLOOKUP search in either direction.

A PivotTable groups rows by category fields and aggregates a value field; grouping dates by month or quarter converts transaction rows into a time series.

Sorting reorders all records by a key; filtering hides rows failing a condition without removing them, so hidden rows may still affect calculations unless the function ignores them.

Regression slope gives the change in y per one-unit change in x, while R-squared gives the proportion of variance in y explained by the model (0 to 1).

A p-value is the probability of data at least as extreme as observed if the null hypothesis is true; it is not the probability that the null is true.

Simpson's paradox: an aggregated trend can reverse after segmentation when subgroup sizes are unbalanced across categories.

A moving average averages the most recent n periods and recalculates each period, smoothing short-term noise to expose trend and seasonality.

Chart choice by question: bar or column for comparing categories, line for change over time, scatter for relationship between two numeric variables, histogram for distribution, pie only for parts of a single whole with few slices.

Bar and column charts require a zero baseline; starting the value axis above zero exaggerates differences and is a misleading-axis fault.

Colour scale semantics: sequential (one hue, light to dark) for ordered values, diverging (two hues around a neutral midpoint) for signed deviations, qualitative palettes for unordered categories.

Dual-axis charts scale two series independently, so apparent alignment is an artefact of chosen scales rather than evidence of a relationship.

Data-ink ratio (Tufte): maximise the proportion of ink that encodes data and remove gridlines, borders, 3-D effects and decoration that carry no information.

Dashboard layout rule: place the most important indicator top-left and ensure the headline message is graspable within about five seconds.

Accessibility requirements: sufficient contrast, no colour-only encoding of categories, readable minimum font sizes and alt text summarising the chart's insight.

Ordering convention: sort categories alphabetically when they act as a lookup list and by value descending when the purpose is ranking.

Number formatting consistency: fixed decimal places, thousands separators, explicit units in axis titles, and identical scales across charts being compared.