20 free Excel templates

Score every customer 1-5 on Recency, Frequency, Monetary using percentile rank. Classifies them into 10 named segments. Outputs revenue contribution per segment.

• 5 sheets: Instructions · Transactions · Customers · Segments · SegmentMap
• 800+ sample transactions across 120 customers with realistic behaviour profiles
• Auto-segmenting via VLOOKUP into 45-entry segment grid (Champion, At Risk, Lost, etc.)
• Revenue chart showing contribution per segment
• 1,122 working formulas, zero errors validated

Methodology: Step-by-step RFM tutorial · RFM glossary

Build a cohort retention table from any list of customer transactions. Heatmap rendered via conditional formatting. No PivotTable required.

• 5 sheets: Instructions · Transactions · Helper · Cohort Matrix · Retention %
• 150 customers × ~700 transactions across 12 monthly cohorts (Jan-Dec 2024)
• Auto-computed cohort + offset per transaction using SUMPRODUCT/EOMONTH/DATEDIF
• Heatmap via conditional formatting (white → violet by retention %)
• 3,419 working formulas, zero errors validated

Methodology: Step-by-step cohort tutorial · Cohort glossary · In-browser version

A self-updating project tracker: enter tasks, owners and due dates, pick a status, and it calculates days left, a RAG flag and overall completion automatically.

• 3 sheets: Instructions · Tasks · Dashboard
• 10 sample tasks with owners, dates and statuses to see it working immediately
• Auto RAG status — overdue rows turn red, due-soon amber, on-track green (conditional formatting)
• Status dropdown and a Dashboard that rolls up totals and overall % complete
• Plain formulas, no macros — Excel 2016+, 365, Google Sheets

Methodology: Step-by-step project tracker tutorial

A monthly KPI dashboard driven by one clean data table. Enter revenue, costs and customer numbers and it computes totals, margins, growth and averages automatically.

• 3 sheets: Instructions · Data · Dashboard
• 12 months of sample data (revenue, costs, new & churned customers)
• 8 live KPIs — gross margin, MoM growth, net customer growth and more
• Editable ranges sized for 24 months; add rows and it keeps calculating
• Plain formulas, no macros — Excel 2016+, 365, Google Sheets

Methodology: Step-by-step KPI dashboard tutorial

Track budget against actual spend by category. Enter your figures and the template computes variance, variance % and flags overspend automatically.

• 3 sheets: Instructions · Budget · Summary
• 10 sample categories with budget and actual figures
• Auto variance & variance % — over-budget rows turn red, under-budget green
• Summary sheet totals budget, actual, variance and counts categories over budget
• Plain formulas, no macros — Excel 2016+, 365, Google Sheets

Methodology: Step-by-step budget vs actual tutorial

Turn a start value, a set of increases and decreases, and an end value into a waterfall chart. Pre-built helper columns make the stacked-bar trick render as a bridge.

• 2 sheets: Instructions · Data
• Sample revenue bridge (start → new sales, upsell, churn, discounts → end)
• Auto Base / Decrease / Increase helper columns with formulas
• Insert one stacked bar chart and you have a waterfall
• Works in Excel 2016+, 365

Methodology: Step-by-step waterfall chart tutorial

A clean monthly P&L: enter revenue and cost lines and it computes gross profit, operating profit, net profit and every margin automatically.

• 2 sheets: Instructions · P&L
• Sample figures across revenue, COGS and operating costs
• Auto subtotals (gross / operating / net profit) and % of revenue on every line
• Green-highlighted subtotal rows
• Works in Excel 2016+, 365

Methodology: Step-by-step P&L tutorial

A sales dashboard driven by a transactions table. Enter deals and the dashboard summarises totals by region, rep and product with formulas — no PivotTable.

• 3 sheets: Instructions · Sales · Dashboard
• 40 sample deals across reps, regions and products
• SUMIF/COUNTIF/AVERAGEIF aggregations by region and rep
• Total sales, deal count and average deal size
• Works in Excel 2016+, 365

Methodology: Step-by-step dashboard tutorial

A conditional-formatting Gantt that draws itself from Start and End dates — no chart wrangling.

• 3 sheets: Start here · Gantt · stacked-bar method
• Auto-drawn bars with progress fill, a today marker and an OVERDUE flag
• 35-day rolling timeline driven by one start-date cell
• 127 working formulas

How to build this one yourself →

Percentile-based colour scales that survive outliers, plus a calendar heat map and a live correlation matrix.

• 5 sheets: Start here · Heat map · Calendar heat map · Correlation matrix · Data
• Percentile scaling so one big number doesn't flatten the palette
• CORREL-driven matrix with a red–white–green diverging scale
• 12 months × 8 regions of sample data

How to build this one yourself →

Budget vs actual with the favourable/adverse logic done properly — cost overruns are adverse, revenue overruns are not.

• Correct F/A treatment for revenue and cost lines separately
• Materiality threshold so only variances worth chasing get flagged
• Net position roll-up via SUMIF by line type
• Colour-coded Investigate flags

How to build this one yourself →

Break-even in units and revenue from three inputs, with a profit chart and a price sensitivity grid.

• 3 sheets: Start here · Break-even · Price sensitivity
• Contribution margin per unit and as a percentage
• Revenue vs cost chart showing the crossover point
• 7 × 7 sensitivity grid across price and variable cost

How to build this one yourself →

A 12-month cash flow with carried-forward balances and a runway warning that names your first negative month.

• Receipts and payments blocks, each totalled automatically
• Closing balance carries forward across all 12 months
• Runway indicator that names the first month you go negative
• Red conditional formatting on any negative balance

How to build this one yourself →

Churn rate, retention, MRR lost and tenure — plus where churn concentrates by plan and by channel.

• 60 sample customers with plan, channel, dates and MRR
• Auto tenure via DATEDIF and a churn flag
• Churn by plan and by channel using COUNTIFS
• 221 working formulas

How to build this one yourself →

A centred funnel chart with step conversion and drop-off already calculated.

• Spacer-series technique for a properly centred funnel
• Step conversion and % of top at every stage
• Colour scale that highlights your worst-converting step
• Chart pre-wired to the table

How to build this one yourself →

Reorder points and safety stock derived from lead time and demand variability, not guesswork.

• Safety stock = Z × σ × √lead time, with an adjustable service level
• Reorder point and days of cover per SKU
• URGENT / REORDER / OK action flags
• Stock value and a total inventory valuation

How to build this one yourself →

1,000 live trials of a profit model, with P10/P50/P90 outcomes and the probability of a loss.

• 5 sheets: Start here · Model · Trials · Histogram · VBA version
• Triangular distribution from min / most-likely / max inputs
• Press F9 to resample — every statistic updates live
• Full VBA loop included for 10,000-trial runs

How to build this one yourself →

One- and two-variable sensitivity grids built from live formulas, so they work in Google Sheets too.

• 5 sheets: Start here · Model · 1-variable · 2-variable · Tornado
• No Data Table feature required — plain formulas throughout
• Tornado chart ranking which input moves the answer most
• Diverging colour scale centred on zero profit

How to build this one yourself →

Z-score, modified Z-score and IQR fences side by side, so skewed data doesn't fool you.

• Adjustable Z threshold — 3.0 conservative, 2.0 sensitive
• Modified Z-score (median absolute deviation) for skewed series
• IQR fences as an independent second opinion
• Live outlier count and colour-coded verdicts

How to build this one yourself →