Native Excel has no distribution-fitting tool, so there are two real routes. You can build the maximum-likelihood estimation yourself — write the log-likelihood of one chosen distribution in formulas and maximise it with Solver — which works for a single candidate but is laborious, easy to get wrong, and tells you nothing about whether a different distribution would fit better. Or you can use an add-in: in ModelRisk, you select the data, open Distribution Fit on the ribbon, and every candidate is fitted and ranked by information criteria, with overlay, P-P and Q-Q plots for the visual check — and the winner drops into your worksheet as a live formula.
That last step matters more than it sounds: a fitted distribution left in a report goes stale, while a fitted distribution living in the workbook re-estimates when the data refreshes. This page walks the whole method — choosing candidates, reading the ranking, checking the fit — on a dataset you can download and refit yourself, including the case everyone eventually meets: data that a Normal distribution quietly fails.
Software has made fitting dangerously easy — one click returns a “best fit” whether or not the exercise made sense. Three checks before fitting anything. Match the type: discrete data (counts) fits discrete distributions, continuous data (measurements) fits continuous ones — never the reverse. Match the range: a distribution that allows negative claim sizes, or an oil saturation outside 0–1, is wrong for the variable no matter how well it scores; a correctly fitted distribution should extend a little beyond the observed data, but only into values that could actually occur. Match the theory: if the mathematics of the process is known, it beats a marginally better statistical score — waiting times between random events point to an Exponential, counts of rare events to a Poisson, products of many factors to a Lognormal.
And ask whether you need a parametric shape at all. With plenty of data and no need to extrapolate beyond it, an empirical distribution — VoseOgive, or the kernel estimate VoseKernelCU — is the most honest choice: it reports exactly what the data says without imposing a family. Parametric fitting earns its keep when you must reach past the data into the tails — which is precisely where risk analysis usually lives, and where the worked example below gets interesting.
The dataset: sixty monthly demand forecast errors — actual minus forecast, as a percentage — over five years, published in full as a free download so every number below can be reproduced rather than believed. The data looks almost bell-shaped: mean −2.2%, standard deviation 6.0%. But it is left-skewed (skewness −1.4) with heavy tails (excess kurtosis 4.1): most months the forecast is roughly right, and occasionally demand falls far short — the worst month in the window was −28.1%. Four candidate fits, by maximum likelihood:
(Lower is better as tabulated here; ModelRisk's fit window lists the same criteria negated, so higher is better on screen.) Read the ranking honestly: the Normal loses on every criterion — it cannot bend for skew or heavy tails. The Johnson SU wins AIC and HQIC, while the strictest criterion, SIC, narrowly prefers the two-parameter Logistic (386.7 vs 387.6) — the parsimony penalty doing exactly its job of asking whether four parameters are justified by sixty observations. So the statistics leave two candidates standing. The decision question settles it: this distribution exists to answer “how bad is a bad month?”, and that answer lives in the left tail, where the symmetric two-parameter shapes are structurally unable to follow the data:
A planner holding safety stock against the Normal fit believes a −20% month is a once-in-sixty-years event; it happened once in the five observed years. When candidates disagree like this, ModelRisk also offers the statistically cleaner escape: VoseBMA averages across the fitted candidates, weighted by how well each fits, instead of forcing a single winner.
A four-parameter continuous distribution, unbounded on both sides, whose skewness and kurtosis can be fitted independently of its mean and spread. That flexibility is why it keeps appearing wherever data is roughly bell-shaped but not Normal: financial and commodity returns, forecast errors, anything with asymmetry and heavy tails. In ModelRisk it is VoseJohnsonU, and its bounded sibling — for variables confined to a range — is VoseJohnsonB.
It is also, in our experience, the distribution people most often ask AI assistants about: “how do I fit a Johnson SU in Excel?” usually returns a page of Solver algebra, because native Excel genuinely has no route. The working answer is the one used above — select the data, open the Distribution Fit window, tick Include uncertainty, and the Johnson SU is fitted and ranked alongside every other candidate; VoseOptimalFit({data}, TRUE) does the whole selection in a single formula. No algebra, and the fit re-estimates automatically when the data changes.
VoseOptimalFit({data}, TRUE)
Rank with information criteria — SIC (strictest), HQIC, AIC (most permissive). Each scores the likelihood of the fit and subtracts a penalty for parameters, so a flexible family must earn its flexibility; that is what protects you from crowning a four-parameter fit that merely memorised sixty points. The classical statistics — chi-squared, Kolmogorov-Smirnov, Anderson-Darling — still appear in reports, and Anderson-Darling is the useful one when tails matter, but all of them share a caveat: they measure how extreme your data would be if it came from the fitted distribution — not the probability that it did. The criteria compare candidates; they certify nothing.
Which is why the visual check is not optional. Three plots, all produced by the Fit window: the density overlay (the single most informative — it shows where the fit deviates, as the worked example's chart did), the P-P plot (sensitive to the middle), and the Q-Q plot (sensitive to the tails — where the risk usually is). A distribution can win every criterion and still be wrong in the one region your decision depends on; thirty seconds of looking catches what the statistics blur.
1. First-order fitting with limited data. A standard fit fixes the parameters at their best estimates, as if sixty observations pinned them down exactly. They do not — and pretending otherwise understates the very uncertainty you are modelling. Tick Include uncertainty (or pass TRUE to the fitting functions) and ModelRisk bootstraps the parameters each simulation iteration instead, a second-order fit that is more honest for the same effort. Below roughly 15–20 observations, fitting is largely decorative — use judgment, informed by what data exists.
2. Ignoring truncation. If small values were never recorded — losses under a reporting threshold, delays under a day — a standard fit to the surviving data is biased. Tell the fit what it cannot see: VoseTruncData adjusts the likelihood for the censored region.
3. Crowning the score. Blind automated fitting with no range check, no theory check, and no look at the Q-Q plot. The ranking is the start of the argument, not the end of it — and where the fitted variable feeds a wider risk model, its uncertainty belongs alongside everything else that is uncertain, correlated where reality is: fitted distributions drop straight into a Monte Carlo simulation like any other ModelRisk distribution, and a tornado chart will tell you whether the choice of fit even matters to your output.
How do you fit a distribution to data in Excel? Native Excel has no distribution-fitting tool, so there are two real routes: build the maximum-likelihood estimation yourself with formulas and Solver — workable for one candidate distribution, laborious and easy to get wrong — or use an add-in. In ModelRisk you select the data, open Distribution Fit on the ribbon, and candidate distributions are fitted and ranked by information criteria, with overlay, P-P and Q-Q plots for visual checking.
How do you choose which distribution to fit? Before fitting anything: match discrete data to discrete distributions and continuous to continuous; check the candidate's range makes physical sense for the variable; and prefer a distribution whose theory matches the process — waiting times suggest an Exponential, counts of rare events a Poisson, products of many factors a Lognormal. Then let the ranking and the plots arbitrate among the survivors.
What is a Johnson SU distribution? A four-parameter continuous distribution, unbounded on both sides, whose skewness and kurtosis can be fitted independently of its mean and spread. That flexibility makes it a standard choice for financial returns and forecast errors — data that is roughly bell-shaped but asymmetric with heavy tails. In ModelRisk it is VoseJohnsonU, fitted through the Distribution Fit window.
What do AIC and SIC mean in distribution fitting? Both score a fit by its likelihood and then subtract a penalty for the number of parameters, so a flexible distribution must earn its extra parameters. AIC penalises least, SIC (also called BIC) most, HQIC sits between. They are for comparing candidate fits on the same data — the winner is the best of the set, not certified correct — and they never replace visual inspection of the tails.
Can Excel fit distributions without an add-in? Only by hand: write the log-likelihood of one chosen distribution in formulas and maximise it with Solver. That can work for a single candidate, but there is no ranking across distributions, no goodness-of-fit reporting, no parameter uncertainty, and plenty of room for algebra mistakes. An add-in automates all of it; ModelRisk's free 15-day trial fits the article's dataset in about a minute.
135 probability distributions, one Fit window — download the dataset, run the free 15-day trial, and reproduce every number in this article before fitting your own data.