How to build monthly or quarterly summary pivot tables from date-based data
- Step 1Confirm Developer tier — The run is Developer-gated; lower tiers are rejected. Upgrade before processing if needed.
- Step 2Normalise dates if they are inconsistent — If your date column mixes
01/02/2026,2026-02-01, andFeb 1 2026, standardise it first with the Date Standardizer so your period formula derives correct periods. - Step 3Add an ISO period column in Excel — For months use
=TEXT(date_col,"YYYY-MM")giving2026-01. For quarters use=TEXT(date_col,"YYYY")&"-Q"&ROUNDUP(MONTH(date_col)/3,0)giving2026-Q1. ISO order ensures the columns come out in chronological order under the tool's alphabetical sort. - Step 4Put the data on the first sheet and upload — Only sheet one is read. Move your prepared data to the first tab, then drop the file onto the tool — both
.xlsxand.csvwork. - Step 5Map dimension to row, period to column — Type your grouping (e.g.
Region) intorowField, the period column header (e.g.Period) intocolField, and your measure (e.g.Revenue) intovalueField. Match header text exactly, case included. - Step 6Pick SUM and download the trend — Leave
aggFnonsumfor revenue/units trends (orcount/averageas needed). Downloadpivot-table.xlsx: rows are your dimension, columns are periods in chronological (ISO) order, with totals.
Period column formats
Build one of these in Excel before uploading. ISO ordering is what makes the columns chronological under the alphabetical sort.
| Granularity | Excel formula | Example value | Sorts chronologically? |
|---|---|---|---|
| Month (ISO) | =TEXT(d,"YYYY-MM") | 2026-01 | Yes |
| Quarter (ISO) | =TEXT(d,"YYYY")&"-Q"&ROUNDUP(MONTH(d)/3,0) | 2026-Q1 | Yes |
| Week (ISO) | =TEXT(d,"YYYY")&"-W"&TEXT(WEEKNUM(d),"00") | 2026-W05 | Yes (zero-padded) |
| Fiscal quarter | custom (FY26-Q1) | FY26-Q1 | Yes if year/quarter zero-aligned |
| Month name only | =TEXT(d,"MMM") | Jan | No (sorts Apr, Aug, Dec...) |
| Unpadded month | =YEAR(d)&"-"&MONTH(d) | 2026-1 | No (2026-10 before 2026-2) |
Time-series field mapping
Standard layout for a period-across-the-top trend pivot.
| Input | Value | Notes |
|---|---|---|
rowField | Region / Product / Rep | The series you want one row per |
colField | Period | Your ISO period column — becomes the time axis |
valueField | Revenue / Units | The measure to trend |
aggFn | sum | Use average for rates, count for volumes |
Tier access
Run requires Developer.
| Tier | Pivot run | Excel limits |
|---|---|---|
| Free | Rejected | 5 MB / 10,000 rows |
| Pro | Rejected | 50 MB / 100,000 rows |
| Pro-media | Rejected | 200 MB / 500,000 rows |
| Developer | Allowed | 500 MB / unlimited rows |
Cookbook
Period-based pivots with the exact period format and option text. Figures illustrative.
Monthly revenue by region (ISO months sort right)
With YYYY-MM periods the columns come out chronologically because alphabetical order matches time order. Grand Total column present (SUM).
Input (sheet 1, Period derived as YYYY-MM): Region,Period,Revenue EMEA,2026-01,1200 EMEA,2026-02,1500 APAC,2026-01,800 EMEA,2026-01,300 Options: rowField=Region colField=Period valueField=Revenue aggFn=sum Output (pivot-table.xlsx): Region,2026-01,2026-02,__Grand Total APAC,800,0,800 EMEA,1500,1500,3000 Grand Total,2300,1500,3800
Quarterly revenue by product (YYYY-Qn)
Quarter labels in YYYY-Q1 form sort chronologically. A product with no Q2 sales shows 0 for that quarter.
Input (Period derived as YYYY-Qn): Product,Period,Revenue Widget,2026-Q1,5000 Widget,2026-Q2,6000 Gadget,2026-Q1,3000 Options: rowField=Product colField=Period valueField=Revenue aggFn=sum Output: Product,2026-Q1,2026-Q2,__Grand Total Gadget,3000,0,3000 Widget,5000,6000,11000 Grand Total,8000,6000,14000
Why month names alone go out of order
Using Jan, Feb, Mar as the period gives a wrong-looking trend because the tool sorts alphabetically: Apr, Aug, Dec... Use ISO YYYY-MM instead.
Input (Period = month name): Region,Period,Revenue EMEA,Jan,1200 EMEA,Feb,1500 EMEA,Mar,900 Options: rowField=Region colField=Period valueField=Revenue aggFn=sum Output (alphabetical: Feb, Jan, Mar - misleading): Region,Feb,Jan,Mar,__Grand Total EMEA,1500,1200,900,3600 Grand Total,1500,1200,900,3600
Unpadded months collide too
2026-1 ... 2026-10 sort with 2026-10 before 2026-2 because it's a string sort. Zero-pad with TEXT(d,'YYYY-MM') so months stay in order.
Input (Period = YEAR&'-'&MONTH, unpadded): Region,Period,Revenue EMEA,2026-2,1500 EMEA,2026-10,900 EMEA,2026-1,1200 Options: rowField=Region colField=Period valueField=Revenue aggFn=sum Output (string sort: 2026-1, 2026-10, 2026-2 - wrong): Region,2026-1,2026-10,2026-2,__Grand Total EMEA,1200,900,1500,3600 Grand Total,1200,900,1500,3600
Average monthly value (AVERAGE, no total column)
For an average trend, set aggFn=average. There is no Grand Total column for averages — only per-cell means and the row of period averages.
Input: Region,Period,OrderValue EMEA,2026-01,120 EMEA,2026-01,80 EMEA,2026-02,200 Options: rowField=Region colField=Period valueField=OrderValue aggFn=average Output (Jan = mean of 120,80 = 100): Region,2026-01,2026-02 EMEA,100,200 Grand Total,100,200
Edge cases and what actually happens
Periods sort alphabetically, not chronologically
By designBoth axes use a plain string sort. Month names (Jan, Feb) and unpadded periods (2026-1, 2026-10) come out in the wrong order. Always use zero-padded ISO labels — 2026-01, 2026-Q1, 2026-W05 — so alphabetical order matches chronological order.
Raw date values used as the column field
Wide / unorderedPointing colField at a raw date column makes every distinct date its own column — hundreds of one-day columns, sorted as strings. Derive a period column (month/quarter) first; the tool does not auto-group dates into periods.
Mixed date formats in the source
Inconsistent periodsIf the date column mixes 01/02/2026, 2026-02-01, and Feb 1 2026, your period formula will derive inconsistent or wrong periods. Standardise the dates first with the Date Standardizer, then build the period column.
A row group with no activity in a period
ExpectedIf a region had no sales in a given month, that cell shows 0, keeping the trend grid rectangular. The 0 is a fill value indicating no recorded activity, not necessarily a literal zero figure.
Dates auto-formatted by the parser
Format driftSheetJS reads with cellDates enabled, so genuine date cells may render as formatted date strings. If you accidentally use that date column as the period, the labels may not match your expected ISO form. Use an explicit text period column built with TEXT() to avoid surprises.
Period header typed with wrong case
Invalid fieldHeader matching is case-sensitive. period won't match a Period header — the column axis collapses to a single (blank) column. Copy the exact header text from row one.
Data not on the first sheet
Wrong sheetOnly sheet one is read. A workbook with the time-series data on a later tab will be pivoted from the first sheet. Move the data tab to position one.
Fiscal-year periods
SupportedCustom fiscal labels like FY26-Q1 work — the tool treats them as ordinary strings. Just keep them zero-aligned (consistent width, Q1..Q4) so the alphabetical sort orders them correctly within and across fiscal years.
Account below Developer tier
RejectedThe run throws Pivot Table Generator requires Developer tier. on Free, Pro, and Pro-media. Time-series pivots are produced only on Developer.
Frequently asked questions
Does the Pivot Generator sort period columns chronologically?
No — it sorts both axes alphabetically as plain strings. The way to get chronological columns is to use ISO-style period labels: 2026-01 for months, 2026-Q1 for quarters, 2026-W05 for weeks. Because these are zero-padded and year-first, alphabetical order equals chronological order. Month names (Jan, Feb) and unpadded periods (2026-1, 2026-10) will come out misordered.
Can I use fiscal-year periods instead of calendar periods?
Yes. Build a fiscal period column in Excel (e.g. FY26-Q1) before uploading; the tool treats it as any other text column. Keep the labels consistently formatted and zero-aligned so the alphabetical sort orders them correctly.
Does the tool group raw dates into months or quarters automatically?
No. It pivots on whatever column you give it as-is — it does not bucket dates into periods. Add a period column in Excel first with TEXT(date,"YYYY-MM") (months) or the quarter formula, then point colField at that column. Using a raw date column makes one column per date.
How do I build a monthly period column in Excel?
Use =TEXT(date_col,"YYYY-MM"), which yields values like 2026-01. For quarters use =TEXT(date_col,"YYYY")&"-Q"&ROUNDUP(MONTH(date_col)/3,0) for 2026-Q1. Paste the formula down the column, then upload.
Why are my month columns in the wrong order even with year-month?
Almost certainly the months aren't zero-padded — 2026-1 sorts before 2026-10, which sorts before 2026-2, under a string sort. Use TEXT(d,"YYYY-MM") so January is 2026-01 (zero-padded), and the columns line up correctly.
What if a row group has no data in some periods?
Those cells show 0. The grid is filled to a complete rectangle so every row has every period column, which makes the trend easy to chart. A 0 means no recorded activity for that row in that period.
My dates are in mixed formats — will the periods come out wrong?
Likely, yes, if your period formula sees inconsistent inputs. Standardise the date column first with the Date Standardizer so every value is a clean, parseable date, then derive the ISO period column from it.
Can I get a quarter-over-quarter total per series?
Yes, with aggFn=sum: the Grand Total column on the right is each row's total across all periods, and the Grand Total row is each period's total across all rows. (For average/count/min/max there's no total column — only the Grand Total row.)
Does it read all sheets or just one?
Just the first sheet (index 0). Put your prepared time-series data on the first tab before uploading. The output workbook contains one sheet named Pivot.
Is my financial data uploaded anywhere?
No. Parsing and aggregation run in your browser via SheetJS; the file never leaves your device. Only an anonymous usage counter is recorded.
What tier do I need and how big can the file be?
The run requires the Developer tier; Free, Pro, and Pro-media are rejected. On Developer the limit is 500 MB and unlimited rows. Both .xlsx and .csv are accepted.
Can I trend two measures (revenue and units) together?
Not in one pivot — one value field per run. Build a revenue trend (valueField=Revenue) and a units trend (valueField=Units) separately, then place them side by side or join with the Sheet Joiner.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.