To copy a PDF into Excel, open the PDF, select the statement table with your mouse, copy it, click cell A1 in a blank sheet and paste. The text usually lands in one column, so you then split it with Text to Columns and repair wrapped rows. Expect several minutes of cleanup per page.
How to copy a PDF table into Excel without a one-column paste
A copied PDF table lands in a single column because a PDF stores positioned text, not cells. Excel receives each printed line as one string, with the gaps between columns turned into spaces. The fix is to split that string after pasting, not to find a better way to copy.
Start with a text-based PDF, one you can click into and highlight words. If you cannot highlight individual words, the file is a scan, and copy will return nothing. Scans need OCR instead, which is a different job.
The steps
- Open the statement PDF in any reader, such as a browser or Acrobat Reader.
- Click at the start of the first transaction row and drag to the end of the last row on the page. Include the header row if you want column names.
- Press Ctrl+C (Cmd+C on a Mac).
- In Excel, open a blank workbook and click cell A1.
- Press Ctrl+V. If PDF fonts and odd spacing come across, undo, then use Paste Special (Ctrl+Alt+V) and choose Text.
- Look at what landed. Each printed line should sit in one row of column A.
- Repeat for the next page, pasting under the last row of the previous page.
Stop after the first page and judge the result before you copy 12 more. Some statements paste cleanly, with a tab between columns, and Excel splits them for you. Many do not, and you need the next step.
Why the page headers get in the way
Each page of a statement repeats a header, a footer and sometimes a "continued" line. When you drag across several pages, those lines come along. They are harmless to look at, but they break totals and sorting, so delete them as you go. Clear them while the row numbers are small and easy to find, not after you have pasted 400 lines.
Pasting on a Mac works much the same way, with one extra quirk about rows collapsing. That case has its own write-up in the Mac paste edge case.
Text to Columns: splitting copied PDF data
Text to Columns splits the text in one column into several, using either a character you name as the separator or fixed positions you mark. It sits on the Data tab. For a pasted statement, fixed width is usually the better choice, because descriptions contain spaces that a space separator would cut apart.
Delimited or fixed width
Select column A, open the Data tab and choose Text to Columns. Excel asks which type you have.
- Delimited works when a clear character separates the columns, such as a tab or a comma. If your paste carries tabs, this is the quick path.
- Fixed width works when columns line up at the same character positions on every row. Excel shows a preview with a ruler, and you click to place a break line between the date, the description and the amounts.
With a statement, a date such as 03/14 has a fixed length and an amount sits at the right edge, but descriptions vary in length. Fixed width handles this well because the breaks come from position. A space delimiter would split "FLOUR SUPPLY CO" into three cells.
Here is an example of a before-and-after row. The values are made up.
| Stage | Column A | Column B | Column C | Column D |
|---|---|---|---|---|
| After paste | 03/14 FLOUR SUPPLY CO 412.80 9,318.27 | |||
| After Text to Columns | 03/14 | FLOUR SUPPLY CO | 412.80 | 9,318.27 |
Check the result in the preview
Before you click Finish, look at the data preview in the dialog. If two rows disagree, so that one description is longer and pushes its amount across your break line, move the line to the left of the shorter amounts and accept that a few rows will need hand repair. Then set the date column's format to Date in the last step of the wizard so Excel does not guess.
The step that catches people: amounts stored as text
After the split, an amount can look like a number and still be text. Excel then sums it as zero, and sorting puts 1,000 before 200. If SUM returns 0, or the cells show a small green triangle in the corner, you have text amounts. The fix is covered in fixing text numbers in an XLSX export, and it applies to pasted data in the same way.
Copy-paste edge case: descriptions that wrap onto two lines
A wrapped description is payee text too long for one printed line, so the bank prints it across two. When you copy it, the second line arrives as its own row with no date and no amount. Left alone, it leaves phantom rows and a description cut mid-word.
You can recognise these rows at a glance. They have text in the description position and nothing in the date or amount cells, and they sit directly under a normal row.
Joining wrapped rows by hand
For a handful of cases, do the repair directly:
- Click the description cell of the row below the one that wrapped.
- Cut the text and paste it at the end of the description above, adding a space.
- Delete the now empty row.
Joining wrapped rows in bulk
For a long statement, use a helper column. In a new column, flag every row where the date cell is empty, then work through the flagged rows from the bottom of the sheet upward. Going upward matters: each deletion shifts the rows beneath, and working from the bottom keeps the row numbers you have not reached yet unchanged.
A faster approach, if you are comfortable with it, is to fill the date down. Select the date column, use Go To Special, choose blanks, type = and press the up arrow, then confirm with Ctrl+Enter. Every blank date takes the value of the cell above. After that you can sort or filter on the amount column to find rows with no amount and fold them into the row above.
Whichever method you choose, finish by comparing your row count with the printed row count for one sample page. A skipped or doubled row is the most expensive kind of mistake, because it passes a glance test. The guide to typing bank statements into Excel with a faster setup includes a running balance check that works for pasted data as well as typed data, and catches both.
How long does copying PDF data into Excel take per page?
Copying one statement page into Excel and cleaning it takes several minutes, and this post's example estimate is 8 minutes for the first page and 6 for each page after. The first page is slower because you work out the break lines. Later pages go faster once you reuse the same split.
The table below is an example estimate. Your own times will differ, so time one page before you plan a whole statement. It assumes about 6 minutes a page after the first, covering paste, split, wrapped-row repair and a balance check, and prices your time at $25 an hour.
| Statement length (example) | Cleanup time | Cost of your time at $25 an hour |
|---|---|---|
| 1 page | 8 minutes | $3.33 |
| 5 pages | 32 minutes | $13.33 |
| 20 pages | 122 minutes | $50.83 |
Two things push the numbers up. A statement with many wrapped descriptions adds a repair step per row, and a long statement with a different layout on its last page, such as a fee summary, needs separate handling. Two things push them down: a PDF that pastes with tabs, and a second statement from the same bank, since the same break lines work again.
For your own pricing, plug your minutes and hourly rate into the formula in what manual data entry costs. The method is the same whether you type the rows or paste and repair them.
Is copying a PDF into Excel cheaper than converting it?
Copying is cheaper only when the statement is short or the paste is clean. For one page, the cost of your time is a few dollars at most. For five or more pages at a $25 hourly rate, a converter that charges $2.99 for a small statement costs less than the time you would spend on cleanup.
Compare the figures from the timing table with the early prices on the CheapBankConvert homepage: free to preview the first 2 pages, $2.99 for a statement of up to 10 pages, $5.99 for 11 to 40 pages, and $9.99 for a bundle of five statements. The product is in early access and not yet open, so these are planned prices that may change.
| Statement length (example) | Copy and clean at $25 an hour | Early converter price |
|---|---|---|
| 1 page | $3.33 | free (2 pages or fewer) |
| 5 pages | $13.33 | $2.99 |
| 20 pages | $50.83 | $5.99 |
Copying does have strengths. It costs nothing in cash, it works offline, and the file never leaves your machine. Those are real reasons to do it, especially for a sensitive statement. It also builds a feel for the data, which helps if you will be checking the numbers anyway.
It has costs beyond the clock. Pasted text loses table structure, so errors from broken splits are easy to miss. Dates can turn into text or flip day and month. And you repeat the whole routine each month.
If you are not sure whether your own software can do better than copy and paste, can you convert a PDF to Excel with tools you already have ranks the free methods by effort. If the paste route is for receipts and not statements, scanning a receipt into Excel or typing it covers that separate case.
When the numbers say conversion, get started to see your own statement's rows in a free preview before you decide to pay.