Excel Function

Filter a table and SUM keeps counting every row — including the hidden ones. SUBTOTAL fixes this. It is the only native Excel function that automatically ignores filtered-out rows, updating the moment a filter is applied or removed. This guide covers all 11 aggregation types across both function number ranges (1–11 and 101–111), with eight practical examples: filter-aware SUM, COUNT, AVERAGE, MAX and MIN; group subtotals with a grand total that avoids double-counting; the difference between function 9 and 109 when rows are hidden manually; using SUBTOTAL(103) as a per-row visibility indicator for filter-aware conditional sums; Excel Table Total Row integration; AGGREGATE for median, LARGE, and error-tolerant totals; a live KPI dashboard with a “Showing N of M deals” label; and why SUMIF fails on filtered data — and how to fix it.

A list of 500 numbers tells you almost nothing at a glance. Group them into bins and the shape of the distribution becomes immediately visible — where values cluster, where they thin out, and whether the data skews left or right. The FREQUENCY function performs that grouping in a single formula. This guide covers eight practical examples: building a basic frequency table, charting it as a histogram with gap width set to zero, converting counts to relative frequencies and cumulative percentages, generating dynamic bins with SEQUENCE that update as data changes, counting students per grade band, measuring manufacturing defect rates across tolerance zones, comparing two distributions side-by-side, and using the FREQUENCY distinct-count trick to find unique values. It also covers the key behaviour most analysts miss: FREQUENCY always returns one more value than the number of bin boundaries — that extra row is the overflow bucket, and forgetting it silently drops data.

Two columns of numbers — do they move together? The CORREL function answers with a single number between −1 and +1. A result of +0.967 means a very strong positive relationship: when one variable rises, the other rises proportionally. A result of −0.841 means a strong inverse relationship. A result near zero means little or no linear association exists.
This guide covers eight practical examples: basic correlation between two variables, building a 4×4 correlation matrix with conditional formatting, testing statistical significance using the t-test and TDIST, filtering to a specific category with conditional CORREL, rolling correlation to track how the relationship changes over time, finding the strongest pairs across a matrix using LARGE and SMALL, calculating Spearman rank correlation for non-normal or outlier-heavy data, and building a self-updating dashboard that outputs a plain-English label like “r = 0.967 (Very strong positive).” The guide also covers the most important limitation: a high CORREL result does not mean one variable causes the other.

Learn how to use Excel’s LOGEST function for exponential regression. This tutorial covers growth factor extraction, CAGR, R², F-statistic, p-values, exponential decay, half-life, multi-variable models, and 8 practical examples.

Learn how to use Excel’s LINEST function for full linear regression analysis in the given dataset. This blogpost covers slope, intercept, R², F-statistic, p-values, standard errors, multiple regression, polynomial curves, and residual analysis with 8 real world examples.

Excel has no dedicated tick-mark button, but it offers five distinct ways to insert one. The Symbol dialog works for one-off insertions with no setup. Alt+0252 in Wingdings is the fastest keyboard-only method. Shift+P in Wingdings 2 is even quicker once the column is pre-formatted. The CHAR function brings ticks inside formulas — combine it with IF to show a tick when a task is done and a cross when it isn’t. Unicode ✓ and ✔ paste directly into any font without changing a thing. This guide covers all five methods, then shows how to count ticks with COUNTIF, restrict a column to ticks and crosses only using data validation, and color entire rows green or red with conditional formatting

Excel has no built-in SPELLNUMBER function, but three methods fill the gap. The VBA NumToWords function is the most flexible — paste the code once into a module, save as .xlsm, and use =NumToWords(A1) anywhere in the workbook. The AmountToWords extension adds currency and sub-unit names, producing invoice-ready output like “One Thousand Two Hundred Fifty Dollars and Seventy-Five Cents” automatically. For macro-restricted environments, a LAMBDA function defined in the Name Manager achieves the same result with no VBA at all — fully compatible with .xlsx files. This guide covers all three methods with eight practical examples: basic conversion, multi-currency invoice lines (USD, GBP, AED, INR, EUR), cheque format with the XX/100 fraction convention, a dynamic currency table driven by XLOOKUP, and how to lock the output as static text before sharing a finalised document.

Use Excel’s GROWTH function to model exponential relationships and forecast future values from existing data. This practical tutorial explains the GROWTH syntax, how known x and y values are used to calculate an exponential trend, and how to generate predictions for new data points. You’ll learn how GROWTH differs from linear forecasting functions and how it can be applied to sales projections, revenue growth, population models, financial analysis, and other situations where values increase or decrease exponentially. Ideal for analysts, finance professionals, students, researchers, business planners, and advanced Excel users working with forecasting and trend analysis.

Learn how to forecast future values in Excel with the powerful FORECAST.ETS function. This practical tutorial explains how Excel uses exponential smoothing to analyze historical time-series data, identify trends and seasonality, and generate forecasts for future periods. Explore the FORECAST.ETS syntax, timeline and target-date requirements, seasonal settings, confidence intervals, and practical forecasting examples. Ideal for analysts, finance professionals, business planners, sales teams, students, and Excel users who need to turn historical data into useful forward-looking forecasts.

Learn how to calculate variance in Excel and measure how widely data points are spread around the mean. This practical tutorial explains the difference between sample variance and population variance, when to use VAR.S and VAR.P, and how to calculate variance step by step with Excel formulas. You’ll also learn how variance relates to standard deviation and how to interpret the results in real-world data analysis. Ideal for students, analysts, researchers, accountants, finance professionals, and Excel users working with statistical datasets.

Learn when to use STDEV.S and STDEV.P in Excel. This tutorial blog covers Bessel’s correction, quality control, financial volatility, z-scores, rolling standard deviation, and 8 practical examples with formulas. Explore other tutorials to learn more about Excel Functions.

Master Excel’s PERCENTILE.INC, PERCENTILE.EXC, QUARTILE.INC, PERCENTRANK, and RANK.EQ functions. This tutorial blog covers eight practical examples covering performance banding, salary analysis, outlier detection, grading curves, and executive dashboards.