A converted Excel file is usually too large because of what surrounds the data, not the data itself: formatting applied to whole columns down to the last row, logos and page images copied from each PDF page, and empty ranges or hidden sheets left behind. Clear those three and a statement workbook shrinks without losing a single transaction.
Why is a converted Excel file too large?
A converted workbook grows large when the converter stores things you never see. The transactions on a few pages of statement take very little space as plain text. Formatting, pictures and leftover sheets take far more, and a converter that tries to copy the look of each PDF page adds all three.
Excel's limits explain how this can get out of hand. Microsoft's Excel specifications and limits page sets a worksheet at 1,048,576 rows by 16,384 columns. A format applied to "the whole column" covers every one of those rows, and Excel has to record it even though the cells are empty.
The five usual causes
Large converted files tend to have one or more of these:
- Formatting below or beside the data, such as borders, fills or fonts on entire rows and columns.
- Embedded images, often one logo or one full-page picture per PDF page.
- Hidden or extra sheets, for example one sheet per page that was later combined but never deleted.
- Stray objects, such as text boxes and lines drawn over the cells.
- Formulas filled down whole columns, which make Excel store and recalculate a value in each cell.
A size problem is worth fixing for more than storage. A heavy file opens slowly, may be too big to attach to an email, and can make a bookkeeping import time out.
Formatting stretched over a million empty rows of the converted sheet
Formatting on empty cells is the most common reason a converted sheet is heavy. When a converter or a later edit applies a border, fill or number format to entire columns, Excel keeps that formatting for every row of the sheet. Clearing it below and to the right of the real data removes the weight without touching a value.
Microsoft's article on cleaning excess cell formatting says formatting whole rows and columns of unused cells can make a workbook's size grow quickly, and can slow Excel down as well.
Finding where Excel thinks the data ends
Press Ctrl+End. Excel jumps to the last cell it considers in use. On a clean statement sheet, that is the bottom-right corner of your table. If it lands on row 1,048,576 or far off to the right, the space between your data and that cell holds formatting.
Clearing it by hand
- Click the row number just below your last transaction.
- Press Ctrl+Shift+Down to select every row to the bottom of the sheet.
- Right-click a selected row number and choose Delete. Deleting rows removes their formatting; pressing the Delete key only clears contents.
- Do the same for the columns to the right of your last column, with Ctrl+Shift+Right.
- Save the file, close it and open it again. Ctrl+End should now stop at the edge of the table.
The save matters, because Excel resets its idea of the last used cell when the file is saved.
The Inquire tab
Some Windows versions of Excel include an Inquire tab with a Clean Excess Cell Formatting command. The same Microsoft article says it is part of Microsoft 365 Apps for enterprise and similar plans, and that the add-in can be switched on under File, Options, Add-Ins. It warns that the change cannot be undone, so save a copy first.
Embedded images and page logos in a converted workbook
Pictures are the second big cause. A converter that copies the look of each page may insert the bank's logo, a page background or even an image of the whole page onto the sheet, once per page. A twenty-page statement can carry twenty copies of the same logo, each stored in full.
Finding the pictures
Press F5, choose Special, then Objects, and choose OK. Excel selects every picture, shape and text box on the sheet, even ones hidden behind cells or shrunk to a dot. If something is selected and you do not need it, press Delete. Repeat on every sheet.
Compressing instead of deleting
If a picture should stay, for example a signature or a scanned page you need as evidence, compress it instead. Microsoft's guide to reducing the file size of a picture in Office describes the route: select the picture, choose Picture Format, then Compress Pictures in the Adjust group. Clear "Apply only to this picture" to compress all of them, tick "Delete cropped areas of pictures", and pick a lower resolution. The same guide notes that compression is not available in Excel for the web.
For a statement, the bank's logo adds nothing to the data. Delete it.
Hidden sheets, stray objects and full-column formulas in the converted file
Weight can also hide where you do not look: sheets that are hidden or left over from combining pages, text boxes laid over the grid, and formulas filled down to the bottom of the sheet. None of these show on the main table, and each can make up a large share of a converted workbook.
Hidden and leftover sheets
Right-click any sheet tab and choose Unhide. If the option is greyed out, there are no hidden sheets. Some converters put each PDF page on its own sheet, and once you have stacked them into one table, the page sheets are duplicates. Delete them after checking that the combined sheet holds every row.
Full-column formulas
A helper formula filled down a whole column, for example to turn CR and DR suffixes into signs, stores a result in every row of the sheet. Fill formulas only to the last data row, or convert the table to an Excel Table so formulas extend as rows are added. Better still, once a helper column has done its job, paste it as values and delete the formula. Turning CR and DR amounts into signed numbers shows that kind of helper column at its proper length.
Format styles
Workbooks built by copying formats from many pages can pile up custom cell styles. Excel caps a workbook at 65,490 unique cell formats or styles, according to the same specifications page. If you see a "Too many different cell formats" message, styles are the cause, and the excess formatting fix above usually brings the count down.
How do you tell which part of a large converted file is the problem?
Three quick tests point to the cause before you change anything. Press Ctrl+End to check for formatting below the data, run Go To Special, Objects to check for pictures, and save a copy as CSV to see how small the data alone is. The gap between the CSV and the workbook is the weight you can remove.
Diagnosis table
| Symptom | Quick test | Fix |
|---|---|---|
| Ctrl+End lands far below the data | Ctrl+End | Delete rows and columns beyond the table, then save |
| Logos or shapes appear on every page area | F5, Special, Objects | Delete them, or compress pictures you need |
| More sheet tabs than you expected | Right-click a tab, Unhide | Delete page sheets once combined |
| Recalculation is slow after edits | Click a cell far down a helper column | Fill to the last row only, or paste as values |
| "Too many different cell formats" | Open the Cell Styles gallery | Clear excess formatting, remove unused styles |
Looking inside the file
An .xlsx file is a zip archive of XML files and media. Make a copy, rename the copy's extension from .xlsx to .zip, and open it. The xl/media folder holds the pictures and xl/worksheets holds one file per sheet. The largest items tell you where the space went. Work on the copy only, and delete it afterwards.
Shrinking the converted Excel file step by step
Shrink the file in an order that removes the biggest weight first and lets you check the data after each pass: save a backup, delete objects, clear formatting beyond the table, remove leftover sheets, replace finished formulas with values, then save and compare. Each step leaves the transactions untouched.
- Back up. Save a copy under a new name before you change anything.
- Count the data. Note the number of transaction rows and the debit and credit totals. You will compare against these.
- Remove pictures and shapes on each sheet with Go To Special, Objects.
- Delete rows and columns beyond the table on each sheet, then save to reset the used range.
- Delete hidden and leftover page sheets that the combined table already covers.
- Paste finished helper columns as values.
- Save, close and reopen. Check the size, the row count and the totals against step 2.
Reapplying formats the right way
When you put formatting back, apply it to the table, not to whole columns. A Percentage format on a rate column, for example, should cover the rows that hold rates. Fixing percentages after PDF to Excel covers getting those values right before you format them.
Trimming a converted workbook can delete data you need
Trimming is safe for empty formatting and decorative pictures, but it can remove things that matter. Data parked far below or to the right of the table, conditional formatting that runs past the data, and a picture that is the only copy of a scanned page can all disappear. Back up first and check after every step.
Microsoft's article on excess formatting gives one example: if your data ends at column V, the cleanup may remove conditional formatting from the columns after V. If you rely on a rule that runs down a whole column, reapply it to the table range after the cleanup.
Where to stop
- A note far below the table. Some people park a reconciliation or a total in row 2,000. Press Ctrl+Down from the last transaction before deleting rows, and move anything you find.
- A scanned page kept as evidence. Compress it rather than deleting it.
- A workbook made from a spreadsheet. If the PDF was produced from someone's workbook, the original is smaller and keeps its formulas. Converting a PDF back to Excel explains why asking the sender beats any cleanup.
A lean result also depends on the converter. What separates a good PDF to Excel converter from a bad one covers the output to expect. FastBankConvert is being built to return statement data with dates, descriptions, amounts and balances in their own columns, as Excel, CSV or JSON; it is not open yet, and Get started lets you ask to hear when trials begin.