How to unpivot an Excel table from wide to long format
Unpivot a wide table by identifying stable identifier columns and converting repeated measure columns into attribute-value rows. Preserve true blanks, parse the new attribute into a date or category only after inspection, and check totals against the source by original row and measure.
Why the obvious method fails
A sheet with one column per month is readable to a person but difficult to filter, chart, append, or analyze over time. Naive unpivoting can turn notes, totals, and labels into data rows.
Reshape only columns that share one meaning
Unpivoting is not just rotating a table. It declares that several headings are values of one variable. That declaration is valid only when the columns share metric, unit, grain, and population.
Where this usually goes wrong
Do not combine Jan Revenue and Feb Margin merely because both begin with a month. Split compound headers into month and metric or unpivot each measure family separately.
A useful mental model
- State what one original row represents and which columns uniquely identify it.
- Choose only repeated columns with the same meaning and unit for unpivot.
- Convert the new header field into an approved month, metric, or category.
- Check expected row multiplication, blanks, and sums against the wide source.
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Unpivot Excel month columns into long analysis-ready rows. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Data profiling · Reviewable cleaning · Reusable workflows · Version history
From the field · Marketing analyst
Turn campaign month columns into a reusable performance table
A campaign workbook stores Jan Spend, Feb Spend, and Mar Spend in separate columns, alongside annual target and owner notes. The analyst needs one row per campaign-month for trend charts.
Columns used in the example
campaign_id · channel · owner · Jan Spend · Feb Spend · Mar Spend · annual_target
Follow it with your own file
- Keep campaign_id, channel, and owner as identifiers.
- Select only monthly spend columns and exclude annual_target and notes.
- Unpivot headers into month_label and spend, then parse month_label explicitly.
- Retain source column and reconcile spend by campaign and overall.
Check: Expected rows equal campaigns multiplied by selected month columns minus intentionally excluded blanks. Each campaign's long-format spend must equal its original monthly cells.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| Manual copy/stack | Very small one-off table | Missed cells and lineage |
| Power Query unpivot | Repeatable wide tables | Column selection and refresh drift |
| Reviewed reshape | Mixed headers and validation needs | Grain and measure meaning |
Patterns that need context
- A new month requires adding another formula or chart range.
- Headers contain dates, measures, and units mixed together.
- Subtotal or target columns sit beside monthly actual columns.
A method you can follow
- Identify the grain: State what one original row represents and which columns uniquely identify it.
- Select measure columns: Choose only repeated columns with the same meaning and unit for unpivot.
- Parse the attribute: Convert the new header field into an approved month, metric, or category.
- Reconcile shape and totals: Check expected row multiplication, blanks, and sums against the wide source.
What to verify before acting
- Original what one row represents stated
- Identifier columns preserved
- Measure family consistent
- Attribute parsed explicitly
- Shape and totals reconciled
A long-format table that supports filtering, monthly charts, and future appends without losing the source row or mixing incompatible measures.
Questions readers usually ask next
What is the difference between wide and long data?
Wide data stores repeated values across columns; long data stores the repeated dimension in one column and the measure in another.
Should blank cells become rows after unpivoting?
Only if blank is a meaningful observed state. Otherwise retain a missingness rule and reconcile how blanks are excluded.