How to convert a wide excel spreadsheet to long format (unpivot / melt)
- Step 1Upload your wide-format file — Drop an
.xlsx,.xls,.ods, or.csvwhose headers include repeated measurements across columns (years, months, scenarios). The tool reads the first sheet only — if your wide table lives on sheet 2, move or copy it to the first tab before uploading. - Step 2Confirm your headers match exactly — Open the file once and note the precise header text. The tool matches by exact string after trimming surrounding spaces.
Sales 2021andSales_2021are different keys; a value column you type that does not exist in the header still produces rows — just with empty cells. - Step 3Enter the ID columns (comma-separated) — In the ID columns (comma-sep) box, list the columns that should repeat on every output row — for example
Country, Region. These are copied verbatim into each emitted row and are the only non-value columns that survive. - Step 4Enter the value columns (comma-separated) — In the Value columns (comma-sep) box, list the wide headers to melt — for example
2021, 2022, 2023. Each listed column becomes one row per input row. Columns you omit from both boxes are dropped entirely. - Step 5Name the variable and value columns — Set Variable col name (default
variable) to hold the original header text — rename it toYear,Period, orMetric. Set Value col name (defaultvalue) to hold the cell value — rename it toRevenueorAmount. There are no other options to configure. - Step 6Run and download unpivoted.xlsx — Click run; the tool processes in the browser and offers
unpivoted.xlsxwith a single sheet namedUnpivoted. Cross-check the row count: it should equal your input row count multiplied by the number of value columns you listed.
The four option fields (the entire control set)
These are the only inputs the Un-Pivot tool exposes. There is no auto-detect, no drag-reorder, no null-skip toggle, and no multi-group stacking — every behaviour below is what the code actually does.
| Field (UI label) | Schema key | Default | What it does |
|---|---|---|---|
| ID columns (comma-sep) | idColumns | (empty) | Columns copied unchanged onto every output row. Split on comma, each part trimmed; blanks ignored. Leave empty and the output has only the variable + value columns |
| Value columns (comma-sep) | valueColumns | (empty) | Wide headers to melt into rows. Each listed column emits one row per input row. A name that matches no header still emits rows, with an empty value cell |
| Variable col name | variableColName | variable | Output column that stores the original wide header text (e.g. 2021) verbatim. Rename to Year, Period, etc. |
| Value col name | valueColName | value | Output column that stores the cell value pulled from each wide column. Rename to Revenue, Amount, etc. |
Reshape math and output contract
Exact behaviour from the unpivot transform — useful for predicting row counts and verifying results.
| Aspect | Behaviour |
|---|---|
| Output row count | input rows × number of value columns — always, including rows where the value cell is empty |
| Unspecified columns | Dropped. Only ID columns survive alongside the two generated columns |
| Empty / blank value cell | Still emits a row; the value cell is empty (no row is skipped, no aggregation) |
| Sheets read | First sheet only (sheet index 0). Other tabs are ignored |
| Output | Single sheet named Unpivoted in unpivoted.xlsx (binary XLSX download) |
| Dates / numbers | Read with formatting applied (not raw serials), so a date column melts to its displayed string |
Tier limits for Excel-family tools
The Un-Pivot tool itself requires Pro or higher. Real numbers from the tier configuration.
| Tier | Max file size | Row limit | Files per run |
|---|---|---|---|
| Free | 5 MB | 10,000 | 1 (Un-Pivot is Pro-only) |
| Pro | 50 MB | 100,000 | 5 |
| Pro-media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
Concrete before/after reshapes. Each shows the input headers, the four field values, and the resulting long table so you can predict your own output exactly.
Three year-columns melted to a Year column
The canonical case: one row per country with three year columns, reshaped to one row per (country, year). Output row count = 2 input rows × 3 value columns = 6 rows.
Input (wide): Country,2021,2022,2023 USA,100,120,140 UK,80,90,95 Fields: ID columns: Country Value columns: 2021, 2022, 2023 Variable col: Year Value col: Revenue Output (unpivoted.xlsx, sheet 'Unpivoted'): Country,Year,Revenue USA,2021,100 USA,2022,120 USA,2023,140 UK,2021,80 UK,2022,90 UK,2023,95
Multiple ID columns kept on every row
Two ID columns (Region, Product) repeat verbatim across each melted year. Listing several ID columns is just a longer comma-separated list — the order you type is the order they appear.
Input (wide): Region,Product,Q1,Q2 West,Widget,10,15 East,Gadget,7,9 Fields: ID columns: Region, Product Value columns: Q1, Q2 Variable col: Quarter Value col: Units Output: Region,Product,Quarter,Units West,Widget,Q1,10 West,Widget,Q2,15 East,Gadget,Q1,7 East,Gadget,Q2,9
A dropped column you forgot to list
Any column not named as an ID or value column is removed from the output. Here Notes is neither, so it disappears. If you need it, add it to the ID columns list.
Input (wide): Country,Notes,2022,2023 USA,priority,120,140 Fields: ID columns: Country Value columns: 2022, 2023 Output (Notes is gone): Country,variable,value USA,2022,120 USA,2023,140 To keep it: ID columns: Country, Notes Country,Notes,variable,value USA,priority,2022,120 USA,priority,2023,140
Empty cells still produce rows
The melt does not skip blanks. A missing 2023 value emits a row with an empty value cell rather than dropping it — important when you expect a tidy table with no gaps. Filter empties downstream if you need a sparse result.
Input (wide, UK missing 2023): Country,2022,2023 USA,120,140 UK,90, Fields: ID columns: Country Value columns: 2022, 2023 Variable col: Year Value col: Revenue Output (4 rows — the empty one is kept): Country,Year,Revenue USA,2022,120 USA,2023,140 UK,2022,90 UK,2023,
A mistyped value column emits empty rows
The tool matches headers by exact text. If you type 2024 but the header is actually Sales_2024, no cells match — the tool still emits one row per input row for that column, all with empty values. The empty block is the signal you mistyped the header.
Input (wide): Country,Sales_2022,Sales_2023 USA,120,140 Fields: ID columns: Country Value columns: Sales_2022, 2024 <- '2024' has no match Output: Country,variable,value USA,Sales_2022,120 USA,2024, <- empty, because '2024' matched no header
Edge cases and what actually happens
Value column name does not match any header
Empty cells, no errorThe tool reads each named value column off the row by exact key. If the name does not exist (typo, wrong delimiter, leading space inside the cell text), the lookup returns nothing and the value cell is empty — but the row is still emitted. There is no error and no warning. A block of empty value cells in the output means a value-column name did not match a header.
A column was listed in neither box
Dropped by designOnly ID columns and value columns reach the output. Any other column is silently removed. This is intentional — it lets you discard noise columns — but it surprises people who expect a passthrough. If you want a column preserved, add it to the ID columns list.
Empty value cell
PreservedThe melt emits one row per value column regardless of cell content, so blank cells become rows with an empty value. No row is ever skipped. If you need a sparse long table (no empty-value rows), drop them afterward in your downstream tool or with a follow-up filter.
Data lives on the second or third sheet
Not readProcessing reads the first sheet of the workbook only. A wide table on sheet 2 is ignored and the output will be empty or wrong. Move or copy the table to the first tab before uploading, or split sheets first with another tool.
Output row count looks too large
ExpectedOutput rows = input rows × number of value columns. Listing 12 month columns against 5,000 rows yields 60,000 rows — correct, not a bug. Check this against the Pro 100,000-row limit before running large reshapes.
No ID columns specified
SupportedLeaving the ID box empty is allowed: the output then contains only the variable and value columns. Useful when you only care about the (header, value) pairs and not which entity they came from, though in practice you usually want at least one ID column.
File exceeds the tier size or row cap
RejectedFiles over the tier file-size limit (Pro 50 MB) or over the row cap (Pro 100,000 input rows) are rejected before processing. Note the cap is on input rows; the larger output can still be opened, but the input must fit. Upgrade tiers raise both limits (Developer is unlimited).
Free tier without Pro
Requires ProUn-Pivot is a Pro-tier tool — the processor throws 'Un-Pivot requires Pro tier' for free accounts. The reshape itself is lightweight, but the gating is by tier. Free users can still test the simpler Excel cleaners; reshaping needs Pro or higher.
Dates melt as formatted strings, not serials
By designThe reader applies number/date formatting, so a date column melts to its displayed text (e.g. 2026-01-15) rather than an Excel serial. This keeps the long output human-readable. If you need a true date type downstream, parse the string in pandas/R or run the result through the date standardizer.
Frequently asked questions
What ends up in the variable column?
The original wide header text, verbatim. If your value columns are 2021, 2022, 2023, the variable column holds 2021, 2022, 2023. Rename the column itself to Year via the Variable col name field, but the cell contents are always the exact header string you melted.
Can it handle multiple value-column groups (stacked melt)?
No. The tool melts one group of value columns into a single (variable, value) pair. There is no multi-group / multi-measure stacking. For two measures (e.g. Revenue_2021 and Cost_2021), melt once, then split the variable column into measure + period with the regex extractor, or run two passes and join the results.
What happens to columns I do not list as ID or value?
They are dropped from the output. Only ID columns and the two generated columns survive. To keep a column, add it to the ID columns list so it repeats on every row.
Why does my output have more rows than my input?
That is the point of melting. Output rows equal input rows multiplied by the number of value columns. Three year columns on 100 rows gives 300 output rows. Verify against that formula to confirm the reshape ran as expected.
Does it skip empty cells?
No. Every value column emits a row even when the cell is blank, so empties become rows with an empty value. If you want a sparse long table, filter out the empty-value rows in your downstream tool.
What output format do I get?
A binary .xlsx file named unpivoted.xlsx with a single sheet called Unpivoted. It is a clean static workbook — no embedded query, no refresh dependency — that opens in Excel, Google Sheets, or LibreOffice and imports cleanly into pandas, R, Power BI, and Tableau.
Which input formats are accepted?
.xlsx, .xls, .ods, and .csv. CSV is treated as a single-sheet table. Whatever you upload, the tool reads only the first sheet and outputs .xlsx.
Is this equivalent to pandas melt()?
Functionally yes for a single value group: pd.melt(df, id_vars=ID, value_vars=VALUE, var_name='Year', value_name='Revenue') produces the same long shape. The difference is pandas drops nothing by default either, but unlike pandas this runs in the browser with no Python install. The inverse operation — long back to wide — is the pivot generator.
Does my spreadsheet get uploaded anywhere?
No. The reshape runs entirely in your browser using SheetJS. The file bytes never reach a JAD server — only an anonymous usage counter is recorded for signed-in dashboard stats, which you can opt out of.
How many value columns can I melt at once?
As many as you can type in the comma-separated box, bounded only by the output row cap for your tier (Pro 100,000). Twelve month columns is routine; just confirm input rows × value columns stays under your limit.
Can I leave the ID columns empty?
Yes. With no ID columns, the output has only the variable and value columns — every (header, value) pair with no entity context. Most users list at least one ID column so each row stays traceable to its source entity.
My headers have spaces or symbols — will matching work?
The tool trims spaces around each comma-separated name you type, then matches the header by exact text. Sales 2021 (with a space) and Sales_2021 (underscore) are different keys. Copy header text exactly. If many headers are messy, clean them first with the header sanitizer.
Can I automate this in a pipeline?
Yes. GET /api/v1/tools/excel-unpivot returns the option schema so an SDK or MCP client can build the payload. Execution happens through a paired @jadapps/runner on your own machine — JAD's API never receives file content. Pair the runner once, then dispatch reshapes locally on a schedule.
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.