Month-over-month (MoM) percent change is one of the fastest ways to see whether your KPI is accelerating, stalling, or slipping. In this guide you’ll build a reusable Excel template that calculates MoM % safely, handles missing months and zero priors, and auto-updates your chart the moment you add a new row.
Quick formula preview (modern Excel): =LET(cur, [@Value], pm, EDATE([@Month],-1), prev, XLOOKUP(pm, Table1[Month], Table1[Value], ""), IF(prev="","", IF(prev=0, "—", (cur-prev)/prev)))
Key takeaways
Store your data in an Excel Table so formulas and charts auto-extend when you append a new month.
Use a LET + XLOOKUP pattern to fetch the prior month, then compute MoM % with guards for blanks and divide-by-zero.
Normalize Month values to the first day of each month (true dates, not text) to ensure exact matches.
Bind a combo chart (columns for Value, line for MoM %) to the Table so visuals update automatically.
Set the data foundation with an Excel Table
Why a Table? Tables bring structured references, auto-expanding ranges, and calculated columns that fill down automatically—ideal for templates you’ll reuse every month. Microsoft documents these behaviors in guidance on using structured references with Excel tables and on calculated columns in an Excel table. See:
Using structured references with Excel tables: https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e
Use calculated columns in an Excel table: https://support.microsoft.com/en-us/office/use-calculated-columns-in-an-excel-table-873fbac6-7110-4300-8f6f-aafa2ea11ce8
Create the Table: Paste your data with two headers—Month, Value. Select any cell in the range > Insert > Table > check “My table has headers.” In Table Design, rename it to Table1 (or a meaningful name like KPI_Monthly).
Normalize dates (critical): Ensure the Month column contains true date values representing the first day of each month (e.g., 1/1/2026, 2/1/2026). If your data has mid‑month dates, transform them. A quick approach: in a helper column, use
=EOMONTH([@Month],-1)+1, then paste values back into Month and delete the helper. When Month is text (e.g., "Jan-26" as text), convert with=DATEVALUE([@Month])or rebuild with=DATE(year, month, 1).Add headers for calculations: Add two new columns to the right—Prior and MoM %. We’ll fill these with calculated-column formulas next.
Compact numeric example (for testing): Jan 1, 2026 → 100; Feb 1, 2026 → 150; Mar 1, 2026 → 150; Apr 1, 2026 → 0; May 1, 2026 → 200. Expected: Jan (blank, blank), Feb (Prior 100; 50%), Mar (Prior 150; 0%), Apr (Prior 150; −100%), May (Prior 0; em‑dash in MoM %).
Calculate Excel month-over-month percentage change (modern Excel)
We’ll use a readable LET + XLOOKUP pattern that works in Microsoft 365 and Excel 2021+. Function references: XLOOKUP and LET are available in these versions.
Prior (calculated column)
=LET(
pm, EDATE([@Month],-1),
prev, XLOOKUP(pm, Table1[Month], Table1[Value], ""),
prev
)
MoM % (calculated column)
=LET(
cur, [@Value],
pm, EDATE([@Month],-1),
prev, XLOOKUP(pm, Table1[Month], Table1[Value], ""),
IF(prev="","", IF(prev=0, "—", (cur-prev)/prev))
)
Formatting: Select the MoM % column > Home > Number > Percentage with 1–2 decimals. Optional conditional formatting: green for positive, red for negative.
Older Excel fallback (no XLOOKUP/LET)
=IFERROR(INDEX(Table1[Value], MATCH(EDATE([@Month],-1), Table1[Month], 0)), "")
and
=IF(Table1[@Prior]="","", IF(Table1[@Prior]=0, "—", (Table1[@Value]-Table1[@Prior])/Table1[@Prior]))
Version note: XLOOKUP/LET are included in Excel 2021 and Microsoft 365.
Create an auto-extending chart for MoM analysis
Bind your visuals to the Table so they grow as you add months.
Insert a combo chart: Select any cell in Table1 > Insert > Recommended Charts > All Charts > Combo. Set Value as Clustered Column; set MoM % as Line (on Secondary Axis). Click OK. If a series is missing, use Chart Design > Select Data to add it from Table1.
Verify auto-extension: Append a new month row at the bottom of Table1. The Prior and MoM % columns should calculate automatically (calculated columns), and the chart should include the new point without range edits. If not, ensure the chart’s series reference Table1 columns via Chart Design > Select Data.
Save the look as a chart template (optional): With the chart selected, go to Chart Design > Save as Template to create a reusable .crtx. Later, apply it from Insert > All Charts > Templates or Chart Design > Change Chart Type > Templates.
Save the workbook as a reusable template: File > Save As > Browse > Save as type: Excel Template (*.xltx).
Optional: Pre-clean and complete months with Power Query
If your data comes from multiple systems or contains gaps/duplicates, Power Query can generate a complete month list and merge it with your facts before you load to Excel.
High-level flow (Advanced): Use Date.StartOfMonth to normalize each record’s date to the first day of the month; generate a continuous month list via List.Dates; merge the list with your fact table on Month to ensure every month exists; fill or leave nulls; then Close & Load to an Excel Table. After that, the same Prior and MoM % formulas apply.
Practical example: Pre-clean with hiData before Excel
If you receive CSVs or PDFs with messy month labels (e.g., duplicates like "Jan 2026" appearing twice, or mixed formats), a lightweight data agent can help you land a clean Month–Value table before opening Excel. For example, hiData can be used to normalize dates to first‑of‑month, deduplicate repeated months by summing or keeping the latest record, and export a tidy two‑column table to .xlsx. After that, open the file and apply the same Table, Prior, and MoM % steps described above.
This is optional; the Excel method here works independently once your Month and Value columns are clean.
QA checklist and quick fixes
Before you save your template and each time you append a new month, confirm the following: dates are first‑of‑month true dates (not text); Prior is blank only for the very first data month or a genuinely missing prior; MoM % shows an em‑dash when Prior is zero (no #DIV/0!); the chart includes the newest month automatically (otherwise rebind series to Table1 via Select Data); and if XLOOKUP/LET aren’t available in your version, switch to the INDEX/MATCH fallback.
Troubleshooting (symptom → likely cause → fix)
Symptom | Likely cause | Fix |
|---|---|---|
MoM % blanks where you expect numbers | Text dates or a missing prior month | Convert to true dates; ensure Month is first‑of‑month; check for real gaps |
#N/A in Prior | Lookup mismatch from non‑identical dates | Normalize all months with EOMONTH+1; verify exact match using pm = EDATE(Month,-1) |
#DIV/0! in MoM % | Prior equals 0 and guard missing | Use IF(prev=0, "—", …) or apply IFERROR appropriately |
Chart doesn’t grow | Chart bound to static ranges | Reselect data so each series points to Table1 columns |
Next steps and reuse tips
Each month, paste the new Month (first day) and Value as a new row at the bottom of Table1. The Prior and MoM % columns fill automatically, and your chart updates instantly. To track multiple KPIs, duplicate the Table and chart on separate sheets (one measure per Table is simplest), or consider a PivotTable approach using Show Values As → % Difference From if your source is wide. When you’re happy with the layout and formatting, save the workbook as an .xltx template so you can create fresh copies without overwriting prior months.
Keyword reminder: This guide showed how to compute and visualize the Excel month-over-month percentage change with modern formulas, resilient date handling, and a chart that grows as your data grows.