Free Excel Analytics Guides
Excel Analytics Tutorials
Master advanced Excel analytics with these free, step-by-step guides. Learn Holt-Winters forecasting, RFM customer segmentation, cohort analysis, anomaly detection and KPI dashboards — with downloadable templates. Then automate everything in DataHub Pro with a single click.
✓ Free guides ✓ Downloadable templates ✓ No-code automation available
All Excel Analytics Tutorials
Forecasting
Holt-Winters Forecasting in Excel
Build a triple exponential smoothing forecast model using Excel's FORECAST.ETS function. Capture trend and seasonality in your time-series data — no add-ins required.
⏱ ~15 min read
Retention
Cohort Analysis in Excel
Create a customer retention matrix by cohort using pivot tables and COUNTIFS. Identify exactly which acquisition periods produce your most loyal customers.
⏱ ~15 min read
Segmentation
RFM Analysis in Excel
Score every customer by Recency, Frequency and Monetary value using DATEDIF, COUNTIFS and PERCENTRANK. Segment your customer base into Champions, At-Risk and Churned groups.
⏱ ~15 min read
Anomaly Detection
Anomaly Detection in Excel
Use rolling averages and standard deviation bands to automatically flag unusual spikes and dips in your KPI data. Catch problems before they become crises.
⏱ ~15 min read
Dashboards
KPI Dashboard in Excel
Design a professional KPI dashboard in Excel with dynamic charts, conditional formatting, and summary cards. Export-ready for PowerPoint or PDF presentation.
⏱ ~15 min read
Project Management
Project Tracker in Excel
Build a self-updating project tracker with a status dropdown, automatic RAG flags, in-cell progress bars, effort-weighted completion, and a conditional-formatting Gantt timeline.
⏱ ~12 min read
Operations
Inventory Management in Excel
Track stock with SUMIFS, calculate statistically sound reorder points and safety stock, automate low-stock alerts, run ABC analysis, and value your inventory — with a live calculator.
⏱ ~14 min read
Time Series
Moving Average in Excel
Master simple, weighted, and exponential moving averages with AVERAGE, SUMPRODUCT, and chart trendlines. Smooth noisy data and forecast — with an interactive smoothing chart.
⏱ ~12 min read
Finance
Profit and Loss in Excel
Build a formula-driven P&L statement from revenue to net profit, with gross, operating, and net margins, period variance, and red-flagged losses — plus a live P&L calculator.
⏱ ~13 min read
Finance
Budget vs Actual in Excel
Calculate absolute and percentage variance, auto-label each line favourable or unfavourable (income vs expense), colour-code, and roll up totals — with an interactive variance calculator.
⏱ ~12 min read
Formulas
XLOOKUP in Excel
Master all six XLOOKUP arguments: exact and approximate match, left lookups, multi-column returns, if-not-found, wildcards, and reverse search — with an interactive formula builder.
⏱ ~11 min read
Dashboards
Excel Dashboard Templates
The reusable three-tab structure behind every great dashboard, plus a full build — PivotTables, KPI cards, slicers, conditional formatting — with a live sales/finance/HR preview.
⏱ ~14 min read
Statistics
Regression Analysis in Excel
Linear and multiple regression: scatter trendlines, SLOPE/INTERCEPT/RSQ, FORECAST.LINEAR, the Data Analysis ToolPak, LINEST, and reading p-values — with a live least-squares demo.
⏱ ~13 min read
Finance
Financial Dashboard in Excel
Build a board-ready CFO dashboard: revenue, margin, cash and budget KPIs, P&L trend, variance, and financial ratios — with an interactive live financial dashboard.
⏱ ~14 min read
Finance
Cash Flow Forecast in Excel
Build a 12-month cash flow forecast: inflows/outflows, running closing balance, receivables timing, seasonality, best/base/worst scenarios, a 13-week version, and a minimum-cash alert — with a live 12-month projector.
⏱ ~15 min read
Visualisation
Heat Map in Excel
Create heat maps with conditional formatting: 3-color scales, custom midpoints and percentiles, single-color scales, calendar and correlation-matrix heatmaps — with a live color-scale sandbox.
⏱ ~14 min read
Finance
Break-Even Analysis in Excel
Fixed vs variable costs, contribution margin, break-even units and revenue, the classic break-even chart, margin of safety, and Goal Seek what-ifs — with a live break-even calculator.
⏱ ~14 min read
Risk & Simulation
Monte Carlo Simulation in Excel
Run 1,000 trials with RAND, NORM.INV, and a Data Table: random inputs, percentile summaries, a histogram, and reading P10/P50/P90 — with a live Monte Carlo simulator.
⏱ ~15 min read
Visualisation
Funnel Chart in Excel
The built-in funnel chart (2019+/365) and the centred stacked-bar funnel for older Excel, plus conversion-rate columns and drop-off analysis — with a live funnel builder.
⏱ ~13 min read
Retention
Customer Churn Analysis in Excel
Monthly churn rate with COUNTIFS, cohort views, revenue vs logo churn, net revenue retention, a retention curve, and at-risk segments — with a live churn calculator.
⏱ ~14 min read
Data Prep
Power Query in Excel
Import CSV, web, and folder data, clean it in the Power Query Editor, unpivot cross-tabs, merge and append queries, and refresh with one click — with an interactive query step builder.
⏱ ~16 min read
Modelling
Sensitivity Analysis in Excel
One- and two-variable Data Tables, conditional-formatted sensitivity grids, Goal Seek for break-even, Scenario Manager, and tornado charts — with a live profit sensitivity grid.
⏱ ~15 min read
Core Skills
Running Total in Excel
Six methods: the expanding SUM($B$2:B2), Excel Tables, SUMIF/SUMIFS conditional totals, pivot Running Total In, SCAN/LAMBDA, and group resets — with a live cumulative-sum playground.
⏱ ~13 min read
Risk Analysis
Monte Carlo Simulation in Excel VBA
Complete macro code: the Randomize/Rnd loop, Box-Muller normal and triangular distributions, fast variant-array output, P10/P50/P90 percentiles, histograms, and a one-button simulator — with a live 100k-iteration speed demo.
⏱ ~17 min read
Visualisation
Interactive Charts in Excel
Dropdown series switching with INDEX, dynamic OFFSET/FILTER ranges, slicers, scroll-bar time panning, checkbox series toggles, and dynamic titles — with a live interactive chart playground.
⏱ ~15 min read
Core Skills
Pivot Table in Excel
From your first pivot to slicers, calculated fields, date grouping, and PivotCharts — the complete beginner-to-advanced guide with an interactive field builder.
⏱ ~15 min read
Formulas
VLOOKUP in Excel
The classic lookup function explained from scratch: exact vs approximate match, common errors, and when to switch to XLOOKUP or INDEX MATCH.
⏱ ~13 min read
Formulas
INDEX MATCH in Excel
The flexible alternative to VLOOKUP: left lookups, two-way lookups, and why INDEX/MATCH still matters in every version of Excel.
⏱ ~13 min read
Project Management
Gantt Chart in Excel
Build a chart-based project timeline with stacked bars, milestones, dependencies, and a today marker — step by step.
⏱ ~14 min read
Dashboards
Sales Dashboard in Excel
A full sales dashboard build: revenue KPIs, rep leaderboards, pipeline funnel, and interactive slicers driven by PivotTables.
⏱ ~15 min read
Forecasting
Exponential Smoothing in Excel
Single, double and triple exponential smoothing — by hand, with the Data Analysis ToolPak, and with FORECAST.ETS. A worked example and honest parameter tuning.
⏱ ~13 min read
Forecasting
Seasonal Forecasting in Excel
Detect seasonality, build seasonal indices, deseasonalise and re-seasonalise — the ratio-to-moving-average method plus FORECAST.ETS.SEASONALITY, with a worked example.
⏱ ~14 min read
Finance
Year-over-Year Growth in Excel
The YoY percentage formula, multi-year CAGR, the same-month-last-year variant, and the pitfalls — zero bases, negative bases, partial years — that quietly corrupt the number.
⏱ ~11 min read
What you'll learn
These tutorials build a complete Excel analytics skill set — from foundational techniques to advanced statistical methods used by professional data analysts:
- Time-series forecasting: Understand seasonal decomposition and apply exponential smoothing models to predict future values with confidence intervals
- Customer analytics: Build RFM scoring models and cohort retention matrices that reveal the true health of your customer base
- Statistical anomaly detection: Apply rolling Z-score and standard deviation methods to automatically surface outliers in any metric
- Dashboard design: Structure KPI dashboards that communicate clearly to stakeholders — combining calculated fields, dynamic ranges and clean chart formatting
Go further with DataHub Pro
Once you understand the methodology, DataHub Pro automates all of these techniques from your Excel or CSV file — no formulas needed. Upload your data and get AI-powered results in seconds.
Frequently asked questions
Can I do Holt-Winters forecasting in Excel without VBA?
Yes. Excel 2016 and later includes the
FORECAST.ETS function which implements triple exponential smoothing — the same statistical model as Holt-Winters. Our tutorial walks through the full setup step by step. DataHub Pro also automates the entire process so you get seasonality-adjusted forecasts without writing any formulas.
How do I perform RFM analysis in Excel?
RFM analysis in Excel involves three steps: (1) calculate Recency using
DATEDIF or TODAY()-last_purchase_date, (2) calculate Frequency with COUNTIFS, (3) calculate Monetary with SUMIFS. You then score each customer into quintiles using PERCENTRANK and combine the three scores into an RFM segment. Our full tutorial includes a downloadable template.
What is cohort analysis and how is it done in Excel?
Cohort analysis groups customers by a shared characteristic — typically the month they first purchased — and tracks what percentage return in subsequent periods. In Excel, you build this with a pivot table that crosses cohort month against months-since-first-purchase.
COUNTIFS and SUMPRODUCT are the key functions. The result is a retention heat-map showing exactly where you lose customers.
Is there an easier way to run these Excel analyses automatically?
Yes. DataHub Pro automates all five techniques covered in these tutorials — Holt-Winters forecasting, RFM segmentation, cohort retention, anomaly detection and KPI dashboards — directly from your uploaded Excel or CSV file. No formulas, no pivot tables, no VBA. Upload your data, click once, and get AI-powered analytics results in seconds. Try it free — no credit card required.
Skip the formulas. Get the insights.
DataHub Pro automates every technique in these tutorials — Holt-Winters forecasting, RFM, cohort analysis, anomaly detection — from your Excel file in seconds.
Free tier available · No credit card required · Cancel anytime