How to use
- Paste a Markdown table, CSV, TSV or cells copied from Excel into the box. You can also drop a
.csv,.tsvor.mdfile. - Tick what you want done: trim spaces, remove empty rows, remove duplicate rows, a column to sort by, and swapping rows and columns (transpose). They can all be combined.
- Check the preview and the counts of removed rows, pick an output format (Markdown with aligned columns by default), then copy or download.
The order of processing is trim spaces, remove empty rows, remove duplicates, sort, transpose.
Options
| Option | What it does |
|---|---|
| First row is the header | When on, the first row is excluded from sorting and duplicate removal. When off, columns are named Col 1, Col 2 and the first row is treated as data |
| Trim spaces around cells | Strips leading and trailing spaces from every cell |
| Remove empty rows | Drops rows whose cells are all empty or whitespace |
| Remove duplicate rows | Rows count as duplicates when the columns you pick hold the same values. With none picked, every column must match. Surrounding spaces are ignored in the comparison |
| Keep | The first or the last of each set of duplicates |
| Ignore case | Treats Kim and kim as the same value |
| Sort | Column and direction. Number columns sort by value, text columns in natural alphabetical order, and blanks always go last |
| Swap rows and columns | Rows become columns and columns become rows |
Example 1: dedupe and sort
In this table Jane Doe / Dev / 90 and John Smith / Sales / 8 each appear twice. The third row has an extra leading space.
| name | team | score |
|---|---|---|
| Jane Doe | Dev | 90 |
| John Smith | Sales | 8 |
| Jane Doe | Dev | 90 |
| Ann Lee | Dev | 100 |
| John Smith | Sales | 8 |
With duplicate removal (all columns) and score descending, the two duplicates are removed and the result is this.
| name | team | score |
|---|---|---|
| Ann Lee | Dev | 100 |
| Jane Doe | Dev | 90 |
| John Smith | Sales | 8 |
The score column is recognized as numbers and sorted 100, 90, 8. Sorted as text it would be 100, 8, 90.
Comparing only the team column and keeping the last row of each team gives this instead.
| name | team | score |
|---|---|---|
| Ann Lee | Dev | 100 |
| John Smith | Sales | 8 |
Example 2: swap rows and columns
With duplicate removal off and only the rows and columns swapped, the same table becomes this.
| name | Jane Doe | John Smith | Jane Doe | Ann Lee | John Smith |
|---|---|---|---|---|---|
| team | Dev | Sales | Dev | Dev | Sales |
| score | 90 | 8 | 90 | 100 | 8 |
When there is a header, the original first column (name, team, score) becomes the first column of the new table, and the names that used to be the first row become the new header. Repeated names are kept as they are. Doing it again returns the original table.
Tidying a Markdown table
Paste a Markdown table whose columns drifted out of line while you typed, leave the other options off, and copy it as Markdown to get aligned columns. Wide CJK characters count as two columns, number-only columns are right-aligned, a | inside a cell becomes \| and a line break becomes <br>. To build a table from scratch or fine-tune column alignment, Markdown Table Generator is the better fit.
Good to know
- Limits are 50,000 rows, 500 columns and 20 MB. Beyond that only the beginning is processed, with a notice.
- A line with the wrong number of fields is reported with its line number and padded with blanks. An unclosed quote is reported with the line where it started.
- When exporting JSON, repeated header names are made unique like
name (2), because JSON keys cannot repeat. - When exporting CSV, an option that prefixes values starting with
=,+or@with'(so Excel will not run them as formulas) and a UTF-8 BOM option for Excel are on by default.
FAQ
How do I swap rows and columns in Excel?
Copy the cells from Excel, paste them here, turn on Swap rows and columns, then copy the TSV output and paste it into Excel. It gives the same result as Paste Special with Transpose.
I want rows removed when only one column matches.
Under Columns that decide whether rows match, tick just that column. If you tick only the email column, one row per email is kept.
After sorting, 100 comes before 8.
When every value in a column is a number, it sorts numerically. If even one cell has text, such as 8 pcs, the column sorts as text. Keep only the number and move the unit into the header.
Does it handle .xlsx files?
This tool works on pasted tables and CSV, TSV and Markdown. For xlsx files use Remove Duplicate Rows or Sort & Filter Spreadsheet.
Related tools: CSV Table Viewer, JSON to Table, Remove Duplicate Lines, Sort Lines
Your text and files are processed in this browser and never sent to a server.