What “messy” usually means
A spreadsheet can contain correct data and still be unusable. Reports exported from accounting software, bank portals and PDF converters are laid out for printing, not for analysis. The typical problems are:
- A title block above the header. “Sales by customer, 1–30 September” sits in rows 1 to 3, and the real column names start in row 4.
- A header spread across two rows. A merged “Customer” label spans three columns, with “Code”, “Name” and “Email” underneath.
- Merged cells in the data. A region name is merged down across ten rows, so only the first row actually holds it.
- Junk rows. Subtotals after each group, a repeated header at the top of every printed page and “Page 2 of 5” lines.
- Shifted rows. A PDF converter misread a line and pushed every value one column to the right, so an email address sits under “Name”.
- Empty columns on the left, left over from page margins.
Each of these breaks sorting, filtering, pivot tables and formulas.
How to fix these problems manually in Excel
Title rows and two-row headers
Select the rows above the header, right-click and choose Delete. To combine a two-row header, enter =A1&" – "&A2 in a spare row, fill it across, convert it with Paste Special › Values, then delete the two original header rows.
Merged cells
- Select the merged area, then choose Home › Merge & Center › Unmerge Cells.
- Keep the range selected, press F5, click Special, choose Blanks and click OK.
- Type
=, press the Up arrow once, then press Ctrl+Enter to fill every blank with the value above it. - Copy the range and paste it back as values so the formulas are replaced.
Repeated headers, totals and page lines
Turn on a filter (Data › Filter), filter the first column for the header text, “Total” or “Page”, select the visible rows, delete them, then clear the filter. Do it once for each kind of junk row.
Shifted rows
Select the misplaced cells in one row, then choose Home › Delete › Delete Cells › Shift cells left to pull them back by one column, or Home › Insert › Insert Cells › Shift cells right to push a row the other way. Repeat for every shifted row, which you have to find by eye.
Power Query
Remove Top Rows, Use First Row as Headers, Fill Down and row filters can automate most of this for a report you receive every month. Setting it up takes time and does not handle shifted rows.
Where this tool helps
PDF statements and invoices converted to Excel. Converters repeat the header on every page, keep page numbers and regularly misplace a column on long lines.
Accounting and ERP reports. Ledger and sales reports add titles, group headers, merged labels and subtotals that must go before you can analyse the data.
Files from colleagues. Sheets formatted for printing with merged headings and totals between sections, now needed as a plain list for a pivot table or import.
How detection works
The tool first finds the header: the first row from the top where most columns hold short text labels and real data follows. Merged labels count for each column they cover, and a second label row directly beneath it, followed by data, is treated as part of a two-row header.
Below the header it looks for rows that repeat the header, rows labelled Total, Subtotal or Grand Total and “Page X of Y” lines. It then builds a profile of each column, such as mostly emails, mostly dates or mostly amounts, and checks every row against those profiles. A row whose values only fit when moved one or two columns sideways is offered for moving back, and the overflow column it spilled into is removed once it is empty.
Every finding has a confidence level. Clear-cut fixes are ticked for you; doubtful ones are marked “Check first” and left unticked. Nothing changes until you press Apply selected fixes, and the preview highlights each affected row before and after.
After the layout is fixed
A repaired report often reveals smaller problems: stray spaces copied from the PDF, blank rows between sections or duplicates where two pages overlapped. The next steps panel lists anything else it finds and links to the right tool.