Titles, subtotals and blank rows around a Sage™ report export in Excel™
A report exported to Excel™ is built to be printed. It has a title block, a column heading row somewhere down the sheet, subtotals between groups, and blank rows for spacing. As a table of data it is unusable until those are removed.
What gets in the way
- Title lines above the real header.
- Subtotal and total rows mixed in with the data, so a sum counts everything twice.
- Blank rows that split the data, so sorting and filtering stop at the gap.
- Numbers and words mixed in an amount column, which turns the column into text.
Clean by hand
These steps apply to Excel™ in Microsoft 365. Work on a copy, and record the report's grand total first.
- Delete rows above the header row so the headers are in row 1.
- Select the amount column and use Home → Find & Select → Go To Special → Blanks to find spacer rows. Check the whole row is blank before deleting.
- Filter the label column for "Total" and delete those rows. Take the grand total from your note.
- Check the amount column holds numbers only. A count with no sum in the status bar means text.
- Press Ctrl+T to make the cleaned block a Table.
- Compare the sum of the Table's amount column with the grand total you recorded.
If those two numbers differ, stop. A row was lost, or a subtotal was left behind.
Find them first
Import Cleaner for Sage™ covers these in its export checks: report titles, subtotals and blank rows around the data (SE-06), and numbers and words mixed in an amount column (SE-07). Each finding names the cells. It scans the active sheet only, and the scan, explanations and Undo are free.
See Import Cleaner for Sage™ for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Sage is a trademark of The Sage Group plc. Import Cleaner for Sage is an independent product, not affiliated with or endorsed by Microsoft or The Sage Group plc.