Report exported to Excel? How to fix merged cells, blank rows and text numbers

Why exports look like this

Most business software builds the Excel export from the same layout it prints: a title block at the top, a customer or account name shown once above its rows, spacer rows between sections, subtotals, and number formats picked to look tidy on paper. Excel shows all of that faithfully, which is why the file looks right and still won't sort, filter or pivot.

Work on a copy of the export, and fix things in this order: merged cells first, then the rows that aren't data, then the numbers and dates. Each fix below takes a minute or two once you know where to click.

Unmerge cells and fill the blanks

A column like Customer or Region is often merged down across its rows, or shown only on the first row of each group. Either way, every other row in that group ends up blank once you unmerge, and sorting or filtering mixes the groups up.

1. Select the whole sheet (Ctrl+A) and choose Home > Merge & Center > Unmerge Cells.
2. Select just the column that should have a value on every row, from its first data row to its last.
3. Choose Home > Find & Select > Go To Special > Blanks (or press F5, then Special). Only the empty cells stay selected.
4. Type =, press the Up arrow once, then press Ctrl+Enter. Every blank cell now copies the value above it.
5. Copy the column and use Paste Special > Values, so the formulas don't change if you sort later.

In Power Query the same fix is one step: select the column and choose Transform > Fill > Down. Merged cells come into Power Query with the value in the first row and nothing under it, so Fill Down handles both cases.

Watch out: fill down only the grouping columns. A Notes or Memo column that is blank on purpose would get filled with the wrong text.

Delete blank rows, title rows and repeated headers

The rows that aren't data usually share one trait: the column that identifies a row (an invoice number, a date, an item number) is empty or holds something else. Use that column to find them all at once.

1. Delete the title block above the real heading row, so the headings are row 1.
2. Click in the heading row and turn on Data > Filter.
3. Open the filter on the identifying column and tick only (Blanks) plus any values that aren't data, such as the heading text repeated mid-sheet, "Total", or "Page 2 of 4".
4. Select the visible rows by their row numbers, right-click and choose Delete Row. In a filtered range, Excel deletes only the visible rows.
5. Clear the filter and check that the real rows are all still there.

Avoid selecting the whole sheet, using Go To Special > Blanks and deleting entire rows: it also deletes every real row that has one empty cell anywhere.

Remove subtotal and total rows

Subtotal lines double your totals the moment you pivot or sum the column. Filter the identifying column or the name column for words like Total, Subtotal or Group, delete those rows the same way, and write down the grand total first so you can check against it later.

If the subtotals sit in their own column instead of their own rows, just delete that column; a PivotTable or SUMIFS will rebuild them correctly.

Fix numbers stored as text

Numbers that sit at the left of the cell, show a small green triangle, or add up to zero are stored as text. Common causes and fixes:

• Plain text numbers. Select the column, click the warning icon and choose Convert to Number. Or use Data > Text to Columns and click Finish straight away, which converts the whole column in one go.
• Trailing minus signs such as 125.00-. Run Data > Text to Columns, go to the last step, click Advanced and make sure Trailing minus for negative numbers is ticked.
• Negatives in parentheses such as (125.00). Excel usually converts these to negative numbers with Convert to Number; if not, the bank statement guide shows a formula that does.
• Hidden spaces. Exports sometimes pad numbers with non-breaking spaces that TRIM doesn't remove. In a spare column, this formula strips them and returns a real number:

=VALUE(SUBSTITUTE(TRIM(A2), CHAR(160), ""))

Fix dates that are text or the wrong way round

If dates don't sort in order or Excel reads 03/04 as April 3 when it was March 4, the export and your computer disagree on the date format.

1. Select the date column and choose Data > Text to Columns.
2. Click Next twice. Under Column data format choose Date and pick the order the export uses, such as MDY or DMY.
3. Click Finish, then apply the date format you want with Home > Number Format.

Also look for the reverse problem: numbers that became dates. A value like 1-30 in an aging column can turn into January 30. Set that column to Text in the same Text to Columns step.

Zeros that look blank

Some systems export a custom number format that shows zero as nothing. The cell looks empty but holds 0, which throws off filters for blanks and counts. Select the number columns and set Home > Number Format to Number or General to see what's really there.

Check it against the original

Before you build anything on the cleaned sheet, compare it with the report: the row count, and a SUM of each amount column against the grand total you noted. If they match, the cleanup didn't lose or double anything. Then press Ctrl+T to make it a table so PivotTables and formulas grow with it.

If you get the same export every week

Doing this once is fine. Doing it every Monday is where the hours go. Two ways to stop:

• Power Query. Load the export with Data > Get Data > From File, record the steps above (remove top rows, fill down, filter out blanks and totals, set types), and next week just save the new export over the old one and Refresh All. See automating a weekly report.
• A tool built for the report. Wheelbarrow reads the export as it comes, Excel, CSV or PDF, and skips the title lines, blank rows, repeated headings and total lines for you. Describe the result in plain words, for example "one row per invoice, a tab per customer, red if over 60 days", and see it built from your own file before you pay. After that, next week's export becomes the finished workbook in seconds.