Transpose / Unpivot
Flip rows and columns, unpivot wide data into long format, or group repeated rows across into one row per group.
By Operelio team
On this page8
What it does
Transpose / Unpivot reshapes a spreadsheet in one of three ways. Transpose flips the entire table so every row becomes a column and every column becomes a row. Unpivot converts wide-format data into long format by turning a group of columns into a single column of values, with an extra column holding the original column name. Group across does the opposite of unpivot: it turns a long list with several rows per group into one row per group, with the repeating fields numbered across.
Which mode you pick depends on what the next tool or system expects. Excel pivot tables, BI tools, databases, and most charting libraries want long format. A report laid out for human reading usually wants wide format.
Which mode should I pick?
Both modes change the shape of your data without changing the values. The difference is what that new shape looks like.
| Use Transpose when | Use Unpivot when |
|---|---|
| You want to flip the whole table (rows become columns and vice versa) | You have one column per time period, region, or category (Q1, Q2, Q3, Q4) and need them stacked into rows |
| You want to re-orient a short, wide report for print or presentation | You're preparing data for a pivot table, chart, or database import |
| You have a single metric per cell and just want to rotate the layout | You want one row per observation (long-format analytics) |
Transpose mode
Transpose rotates the entire grid 90 degrees. If your file has 5 columns and 100 rows, the output has 100 columns and 5 rows.
Two options control how headers are handled. "Use first row as headers" (on by default) treats the first row of your input as column headers before transposing. "Use first column as headers" turns the first column of the original data into the header row of the output. If you leave that off, headers are auto-generated as Col_1, Col_2, Col_3, and so on.
Concrete example:
| Before (3 rows × 4 columns) | After (4 rows × 3 columns) |
|---|---|
| Metric · Jan · Feb · Mar | Month · Revenue · Costs |
| Revenue · 12,400 · 15,800 · 14,200 | Jan · 12,400 · 8,100 |
| Costs · 8,100 · 9,200 · 8,700 | Feb · 15,800 · 9,200 |
| Mar · 14,200 · 8,700 |
Unpivot mode (Pro)
Unpivot takes wide-format data and converts it to long format. You pick the ID columns (the ones that stay fixed) and the value columns (the ones that get turned into rows). Each value column becomes a new row per original row, so the total output row count is (original rows × value columns).
You also name the two new output columns. The label column holds the original column header (for example "Q1"); the value column holds the corresponding cell value (for example 120). Pick names that fit your domain. "Quarter" and "Revenue" are typical.
Concrete example:
| Before (3 rows × 5 columns) | After (12 rows × 3 columns) |
|---|---|
| Product · Q1 · Q2 · Q3 · Q4 | Product · Quarter · Revenue |
| Widget · 120 · 150 · 180 · 210 | Widget · Q1 · 120 |
| Gadget · 90 · 110 · 95 · 130 | Widget · Q2 · 150 |
| Sprocket · 200 · 210 · 225 · 240 | … · … · … |
The output has 3 rows × 4 value columns = 12 rows. If your data has 10 products across 12 months, that's 120 output rows. Unpivoting always multiplies rows by the number of value columns you pick.
Group across mode (Pro)
Group across is unpivot's inverse. You pick one column that defines a group (Company, Order number, Student) and the columns that repeat for every row in that group (Contact name, Phone). The output has one row per group, with each row's repeating fields laid across as a numbered set: Contact name 1, Phone 1, Contact name 2, Phone 2, and so on. Columns you don't pick ride along from each group's first row, or can be left out with a toggle.
The most common use is CRM imports that want contacts listed across, one row per company. There's a dedicated walkthrough for that job: see the "Get contacts listed across for your CRM" guide in this library.
| Before (4 rows × 3 columns) | After (2 rows × 5 columns) |
|---|---|
| Company · Name · Phone | Company · Name 1 · Phone 1 · Name 2 · Phone 2 |
| Meridian · David Garcia · +44 20 7946 0101 | Meridian · David Garcia · +44 20 7946 0101 · Wei Chen · +44 20 7946 0102 |
| Meridian · Wei Chen · +44 20 7946 0102 | Lumen Labs · Karen Wilson · +44 20 7946 0103 · (blank) · (blank) |
| Lumen Labs · Karen Wilson · +44 20 7946 0103 |
Sets cap at 50 per row. If a group has more rows than that, the first 50 are kept and the results page tells you how many rows were left out.
How to use it
The flow is the same for both modes; you pick the mode after upload, not before.
Upload your file
Drag and drop a CSV (.csv), Excel (.xlsx or .xls) file. Operelio reads the columns and shows you a file summary above the mode toggle.
Pick your mode
Transpose is free on every plan. Unpivot and Group across require Pro, Team, or Agency and show a Pro chip when your plan doesn't include them. Keyboard users can arrow-key between the tabs.
Configure
Transpose has one toggle: Use first column as output headers. Unpivot has four inputs: ID columns (stay fixed), value columns (turned into rows), and two names for the new label and value columns. Group across has a group column, the repeating columns, and a toggle for bringing the other columns along.
Preview
The preview banner shows the before / after dimensions and samples as soon as you have enough configuration. Very wide transpose outputs (>1000 columns) or very tall unpivot outputs (>100,000 rows) show a heads-up about Excel performance.
Run and download
Click the Transpose / Unpivot button. The job usually finishes in a few seconds. The results page gives you a download link for the new file plus a summary of what changed.
What the results show
The results page summarizes the run so you can confirm it matches what you expected:
| Field | Meaning |
|---|---|
| Original rows × columns | The input file's shape before running. |
| Output rows × columns | The new file's shape after the transformation. |
| Mode | Transpose, Unpivot, or Group across. |
| First column as headers (Transpose) | Yes or No: whether the first column of the input became the new headers. |
| ID columns (Unpivot) | The columns that stayed fixed. |
| Value columns (Unpivot) | The columns that got turned into rows. |
| Label column / Value column (Unpivot) | The names you gave the two new output columns. |
| Grouped by (Group across) | The column that defined the groups. |
| Repeating columns / Sets per row (Group across) | The fields numbered across, and how many sets the widest group produced. |
Frequently asked questions
What is the difference between transpose and unpivot?
Transpose flips your entire table so rows become columns and columns become rows. Unpivot takes specific columns and collapses them into rows. For example, if you have Jan, Feb, Mar columns, unpivot turns them into a Month column and a Value column. Transpose is free on every plan. Unpivot requires Pro, Team, or Agency.
Will it change any of my data?
Your original file is never modified. Operelio creates a new file with the transposed or unpivoted data. All cell values are preserved exactly as they were.
What does "use first column as headers" do?
When this option is on, the values in column A of your original file become the column headers in the transposed output. Useful when your first column contains labels like month names, metric names, or categories.
Can I transpose or unpivot Excel files, or only CSV?
Both. Upload .csv, .xlsx, or .xls files. The tool reads any of these formats and outputs the result as CSV or Excel.
How big a file can I process?
Up to 2,000 rows on Free, 50,000 rows on Pro, 75,000 rows on Team, and 150,000 rows on Agency. File size limits are 2 MB on Free, 25 MB on Pro, 35 MB on Team, and 100 MB on Agency.
When would I use unpivot?
When your data has one column per time period (Q1, Q2, Q3, Q4) or category and you need it in long format for a chart, pivot table, or database import. Most BI tools and visualization libraries expect long format.
What plan do I need?
Transpose is Free. Unpivot and Group across require Pro, Team, or Agency.
Ready to get started?
Upload a file and run your first transformation. Free, no credit card required.