Skip to content
Safitab

Remove blank rows and subtotals

Reports are made for reading, not for analysis. Blank lines separate groups, every group ends with a subtotal, and a grand total sits at the bottom. Put that into a pivot table and every number is counted two or three times.

Blank rows are dropped and the groups become one continuous table. Subtotal and total rows are recognised by their labels and taken out — but not thrown away: each one is used to check that the rows above add up, and you see the result of that check.

Drop files here

or browse your files

XLSX, XLS, CSV, TXT, PDF · up to 25 MB · 1 file without an account

Or try a sample:

Example: before and after

Blank lines and the “Subtotal East”, “Subtotal West” and “Total” rows are gone; all three totals matched the rows they cover.

Beforesales-by-region.csv
1Sales by region
2
3RegionRepMonthRevenue
4EastM. ChenJanuary12,400.00
5EastA. MorganJanuary8,150.00
6Subtotal East 20,550.00
7
8WestK. O'BrienJanuary9,900.00
9WestS. PatelJanuary11,275.00
10Subtotal West 21,175.00
11
12Total 41,725.00
After4 rows
RegionRepMonthRevenue
EastM. ChenJanuary12,400.00
EastA. MorganJanuary8,150.00
WestK. O'BrienJanuary9,900.00
WestS. PatelJanuary11,275.00

How to do it

  1. 1Drop the exported report.
  2. 2See the removed rows grouped by reason, and the totals check.
  3. 3Download one clean table, ready for a pivot table or a database import.

Questions

How are subtotal rows recognised?
By their labels (Subtotal, Total, Grand total and similar) and their position after a group of rows. Rows like “Total (excl. tax)” are recognised as a different kind of aggregate and not checked as plain sums.
What if a total doesn’t match?
You see which total and by how much. If it isn’t clear which rows a total covers — common in nested reports — it is marked as not checked rather than reported as an error.
Can I keep the subtotal rows?
Yes. Switch the step off and they stay in the table exactly where they were.

Related tasks

See all tools