Reshaping the table
Turn columns into rows and rows into columns, turn the whole table on its side, summarize a table with one row for each group, give each value of a cell a row of its own, and put those rows back together.
Six commands leave the table with another shape: other columns, or other rows. The first four are in the Table menu, and the last two in the Rows menu.
| Command | What it does |
|---|---|
| Columns to Rows… | turns several columns into two, one with their names and one with their values: a column for each month becomes a row for each month |
| Rows to Columns… | the way back: each name of a column becomes a column, with its values under it |
| Swap Rows and Columns | turns the whole table on its side: each row becomes a column, and each column a row |
| Summarize… | makes one row for each group, with the other columns counted, added up or averaged |
| Split Values into Rows… | gives each value of a cell that holds several a row of its own |
| Combine Rows… | the way back from that one: makes one row of the rows that only differ in one column |
Each one but Swap Rows and Columns, which has nothing to choose, opens a window with its choices and the first rows of the result, and says how many rows there will be. Nothing changes until you press its button, and one Ctrl+Z⌘Z puts the table back as it was.
They take every row of the file. With a filter on, that includes the rows it leaves out, and the window says so: a table with some rows in one shape and the rest in another would be broken. Columns to Rows…, Rows to Columns…, Swap Rows and Columns and Summarize… clear the filter too, since it was a filter of columns that are no longer there.
Columns to rows
A table with a column for each month, each year or each product (a wide table) is easy to read and hard to work with: to filter by month, sort by it or add the months up, the month has to be a value, not a column. does that, and leaves a long table: other programs call it unpivoting, or melting. It takes these rows
city,jan,feb,mar
Madrid,10,12,9
Lugo,3,,5
and makes these of them:
city,name,value
Madrid,jan,10
Madrid,feb,12
Madrid,mar,9
Lugo,jan,3
Lugo,feb,
Lugo,mar,5
Select the columns first, two or more: click a column’s number in the bar above the rows and Shift+Click⇧Click the last one, or Ctrl+Click⌘Click each of the others (see Selecting columns and rows). Then:
- Names go in and Values go in: the names of the two new columns,
nameandvalueunless you type others (month,sales). They take the place of the first of the columns you selected. - Leave out empty values: an empty cell gives no row, so
Lugo,feb,above wouldn’t be there. A row whose values are all empty goes altogether.
The other columns are repeated in each new row, exactly as they were written. In a file without a header row the names are Column 2, Column 3…

Rows to columns
goes the other way, from the long table to the wide one (pivoting, in other programs): it takes the second table above and gives back the first. Each different value of one column becomes a column, with the values of another column under it, and rows next to each other that are the same in every other column become one row.
- Names from: the column whose values will be the names of the new columns (
nameabove, withjan,febandmar). - Values from: the column whose values go under them (
value).
The window proposes name and value if the table has columns called that; if not, the column of the cursor and the one after it. Every other column says which row a value belongs to: Madrid’s three rows become one because they are the same in city. So a column that differs in every row (a row number, a date of entry) keeps the rows apart, and each one ends up with a single value: delete that column first, or see Summarizing.
The new columns go where the column of names was, in the order their names first appear, and a combination the file doesn’t have (Lugo in February, if that row were missing) is an empty cell.
Rows that are the same in the other columns but not next to each other stay rows of their own: that is usually a coincidence. In a table of election results with the columns group, category, candidate and votes, turning group into columns, one candidate may have 28 % among voters aged 18 to 29 and, further down, 28 % among Hispanic voters; those are two rows, each with its own category, not one. When the other columns do say which row a value belongs to but the file is written one name after another (every city for January, then every city for February), tick Rows that are the same in the other columns become one row even when they aren’t next to each other. The window shows that box only when the file has rows like that, and says how many there are.
More than one value says what a cell holds when several rows give it a value: two rows for Madrid in January.
- Join them with “; ”: the cell holds them all, in order, so nothing is lost; the window says how many cells are like that. It is the one to begin with.
- Keep the first one or Keep the last one: only that value.
To add those values up or count them instead, use Summarize first, grouping by the other columns and the column of names. Empty values never count as one of several.
A column whose values are nearly all different (an id) would make a column for each: past 200 the window says so and does nothing, since that is nearly always the wrong column. In a file without a header row the names have nowhere to be written: the columns are in the order their names first appear.
This isn’t turning the whole table on its side, with every row a column: that is Swap Rows and Columns.
Swapping rows and columns
turns the whole table on its side (transposing, in other programs): the first row becomes the first column, the first column becomes the first row, and every value goes from row 3, column 5 to row 5, column 3. It takes these rows
city,jan,feb,mar
Madrid,10,12,9
Lugo,3,,5
and makes these of them:
city,Madrid,Lugo
jan,10,3
feb,12,
mar,9,5
It is for a table written the other way round: one row for each measure and one column for each thing measured, or a form exported as a single row that reads better as a list. To make the months values of a column instead, so that you can sort, filter or add them up, use Columns to Rows: it keeps city as a column and gives each month a row of its own.
It has nothing to choose, so it doesn’t open a window: it swaps the table at once and says how big it was and how big it is (the header row counts as a row, since it becomes a column). The cursor goes to the start of the file, and one Ctrl+Z⌘Z swaps it back.
- Every value is written as it was, quotes and all, so a value of several lines moves whole.
- A row shorter than the others gives empty cells; blank lines aren’t rows, and go.
- The header row stays a header row: with on, the names of the new columns are the values of the old first column (
Madrid,Lugoabove). - Each row becomes a column, so it works on tables of up to 1,000 rows. With more, it says so and does nothing.
Summarizing
answers the questions that start with “how many” or “how much”: orders per country, sales per month, the last visit of each customer. It makes one row for each group, a group being the rows that have the same value in the columns you group by, and the result takes the place of the table.
The window lists every column, with what to do with it:
| Choice | The column holds, for each group |
|---|---|
| Group by | its value: there is a row for each value (or for each combination of values, with several columns) |
| Leave out | nothing: the column isn’t in the result |
| Count values | how many of its cells have a value |
| Count different values | how many different values it has |
| Sum, Average | the sum or the average of its numbers |
| Minimum, Maximum | its lowest or highest value: as numbers in a column of numbers, as dates in one of dates (their times too, and however each is written), and in alphabetical order otherwise |
| First value, Last value | the first or the last value that isn’t empty |
| All values | its different values, in order, separated by commas |
The column of the cursor starts as Group by (or the columns you selected in the bar above the rows) and the others as Leave out; Add a column with the number of rows, ticked to begin with, adds a count column. So, as it opens, the window is already the count of each value of a column: put the cursor in country, open it, and press Summarize.

A few things to know:
- Values group when they are written exactly the same:
Madridandmadridare two groups. Tidy them first, with Quality Check or Group Similar Values. - The groups come in the order they first appear in the file. To order them by a result, sort afterwards (see Sorting rows).
- The columns keep their names and their order; the count goes last.
- Numbers are read with the decimal separator of their column, and the results are written with it: the sum, with as many decimals as the values have; the average, with two more. Currency symbols and units are read past and not written (
$1,200and$300add up to1500). - Empty cells and missing values (
NA,-…) never count, and aren’t part of a sum or an average. - Without any column to group by, the result is one row for the whole table: its totals.
The result replaces every row of the file. It is one step of Ctrl+Z⌘Z; to keep both, the table and its summary, save the summary with another name with and undo.
A cell’s values to rows
Some files keep a list in a cell: red; green; blue in a colours column, three e-mail addresses in one. gives each value its own row, with the other columns repeated:
id,colours
1,red; green
2,blue
becomes
id,colours
1,red
1,green
2,blue
It works on the cursor’s column. Separator is the text between the values; the window proposes the one most cells have. Type any other: a comma, |, \t for a tab, or \n for the line breaks of a cell with one value per line. As in Split Column, a dash stands for any dash. The spaces around each value are removed, and an empty part (red;;green) isn’t a value.
Rows whose cell has one value, or none, stay as they are.
Repeat the other columns in each new row is ticked to begin with: every new row is a whole row, which is what sorting, filtering and counting need. Untick it and only the first row of each keeps the other columns; the ones after it have them empty, the way a list is written in a report:
id,colours
1,red
,green
2,blue
To give each value a column instead of a row, see Split Column.
Combining rows
goes the other way. The rows that are the same in every column but the cursor’s become one row, the first of them, with their values of that column together, in order, with the text you choose between them (Between them, ; to begin with):
id,name,colour
1,Ana,red
2,Luis,blue
1,Ana,green
with the cursor in colour becomes
id,name,colour
1,Ana,red; green
2,Luis,blue
The rows don’t have to be one after another. A value that repeats is written once, and empty ones are left out. Rows that differ in any other column, also by a space or a capital, are different rows and stay apart: the window says how many rows will become how many, and shows the first ones.
Rows that only have the value
Some files don’t repeat the other columns: the first row of each list has them, and the rows under it have only the value.
id,name,colour
1,Ana,red
,,green
2,Luis,blue
,,black
Rows that are empty in the other columns go with the row above, ticked to begin with, reads them that way: green is Ana’s and black is Luis’s, and the result is 1,Ana,red; green and 2,Luis,blue; black. The row above is the last one before it that has something in another column. The window says how many rows went with the one above them.
Unticked, those rows are rows like any other, and since all of them are the same in the other columns (empty), every one in the file ends up in a single row. That is rarely what you want; untick it only when a row without the other columns really is a thing of its own.