How to use the csv wide to long converter
- Paste a table with a header row. The separator is detected, or you can choose it.
- Under Columns to keep, list the columns that identify a row, by name or number. In wide to long mode, list the columns to unpivot, or leave it empty to use every other column.
- Choose the headings for the name and value columns and whether blank cells are left out. For long to wide, name the column that holds the names and the column that holds the values.
- Check the row counts, read the first 100 rows, then download the CSV for the complete result.
Formula
long rows = data rows x unpivoted columns - blank cells left out
wide rows = distinct combinations of the kept columns
wide columns = kept columns + distinct names, in first-seen order
Unpivoting repeats each kept value for every column turned into a row. Pivoting joins rows that share the same kept values, and stops with a message if one combination would need two values in the same cell.
Four regions by four quarters
The sample table has 4 data rows and four quarter columns, Q1 to Q4. Unpivoting the quarters gives 4 x 4 = 16 rows, and three of the cells are blank, so with blanks left out the long table has 13 rows. Keeping them gives 16 rows with empty values.
Pivoting the long table back, with region and product as the kept columns, name as the names column and value as the values column, gives the original rows. A repeated pair, such as the same region, product and quarter twice, stops the pivot and names the row so you can decide which value is right.
Why reshape a table
Many tools expect one measurement per row, with a column for the name of the measurement. A sheet with a column for each month or quarter needs to be turned into that long form before it can be grouped, charted or loaded into a database. The reverse is useful for reports that read better as a grid.
Everything is treated as text, so numbers keep their exact spelling and leading zeros. Quoted cells, commas inside quotes and line breaks inside quotes are read as in standard CSV.
Checks built in
The page shows the row counts it expects and the counts it wrote, so you can see at once if blank cells changed the total. Column names are matched exactly, then without regard to case, and a name used by more than one column is refused with a request to use the column number.
Once the table is long, you can total it with the CSV Group By Summary Tool or flip the grid with the CSV Transpose Tool.
Assumptions and limits
- The first row is the header. Rows that are completely empty are dropped.
- Column names are trimmed of spaces before matching. A column can be named by number from 1.
- The result is limited to 100,000 rows and the table to 20,000 data rows.
- Pivoting keeps the first-seen order of names and combinations, and leaves missing cells empty.
- Nothing typed here leaves your browser.
Frequently asked questions
What is the difference between this and transposing?
Transposing swaps rows and columns of the whole grid. Unpivoting keeps some columns where they are and turns the others into pairs of a name and a value, one pair per row.
Why does the row count change when I leave out blanks?
Each blank cell would otherwise become a row with an empty value. Leaving them out removes exactly those rows, and the headline note says how many blank cells were left out.
What if two rows have the same kept values and name?
The pivot cannot decide which value to keep, so it stops and names the row. Remove the duplicate, or add another column to the kept list to make each combination unique.
Does it keep the order of my columns?
Kept columns come first in the order you list them. Unpivoted columns appear in header order, or the order you list them, and pivoted names appear in the order they are first seen.