Unpivot Table (Wide to Long)
Keep the identifying columns, choose the columns to fold down, and get one row for each pair. This is the layout that pivot tables and charts prefer.
Drag a file here or choose one
Worked examples
Months into rows
Region,Jan,Feb North,120,70 South,95,
Region,Month,Sales North,Jan,120 North,Feb,70 South,Jan,95 South,Feb,
Region is kept; Jan and Feb are folded into Month and Sales. South has no February figure, so it shows blank unless you skip blanks.
Skipping blanks
Same table with Skip blank values ticked
Region,Month,Sales North,Jan,120 North,Feb,70 South,Jan,95
The blank South February row is left out.
About Unpivot Table (Wide to Long)
Reports are usually wide, with a column for each month, each year or each product. That layout reads well but is hard to analyse, because pivot tables, charts and databases want a long list with one row for each region and month. Unpivoting folds the repeating columns down into two new columns: one that holds the old column heading and one that holds the value.
Tick the columns that identify each row, such as Region, and the columns to fold down, such as Jan, Feb and Mar. A region with three months becomes three rows. Name the new columns, skip the blank values if you do not want empty rows, and download the long table. A column ticked in both lists stays as it is, so the identifying columns are never folded down by mistake.
How to use Unpivot Table (Wide to Long)
- Paste your wide table or open a file.
- Tick the columns to keep as they are, which identify each row.
- Tick the columns to turn into rows, and name the two new columns.
- Choose whether to skip blank values, then download the long table.
Common uses
- Preparing monthly columns for a pivot table or chart
- Loading a wide report into a database
- Turning survey answer columns into one list of answers
- Reshaping budget columns by year into rows
Frequently asked questions
What is the difference between wide and long?
A wide table has one row for each item and a column for each measurement. A long table has one row for each item and measurement. Wide suits reading, long suits analysis.
How many rows will I get?
The number of rows times the number of columns you fold down, less any blank values you skip. Ten rows and twelve month columns make 120 rows.
Can I name the new columns?
Yes. The first holds the old column headings, for example Month, and the second holds the values, for example Sales.
What does Skip blank values do?
It leaves out any row whose value is empty, so a region with no figure for March does not get a March row.
How do I reverse it?
Use the pivot table maker with the identifying column down the side and the old headings across the top.