Columns shifted after PDF to Excel when the converter guessed a column boundary wrongly on some rows, so a date, description or amount landed one cell to the right. Find the moved rows with a test formula that checks each column holds the right type, then pull their values back with helper columns instead of retyping them.
Why do columns shift after PDF to Excel conversion?
Columns shift because a PDF stores no columns. It stores pieces of text at x and y positions, and the converter draws column boundaries from where text tends to start. When one row's text starts a little left or right of the rest, it falls on the other side of a boundary and lands in the next cell.
Several things move text across a boundary:
- A long description. A merchant name that runs wider than usual pushes into the space the converter assigned to the next column. The tail of the name, or the whole amount after it, moves one cell right.
- An extra field on some lines. Some statements print a reference number or a check number only on certain transactions. Rows that have it gain a column; rows without it do not.
- Right-aligned numbers. Amounts are aligned on their right edge, so a seven-digit amount starts further left than a three-digit one. A boundary drawn from the short amounts can cut the long one in two.
- Empty cells. A row with no description, such as a fee line that prints only a code, gives the converter nothing to anchor on, so the amount may slide left into the description column.
The result looks random at first. It is not. Every shifted row has a reason in the layout, and the same reason tends to repeat down the sheet, which is what makes a bulk fix possible.
Spotting shifted rows with a column type test
A column type test is a formula that checks whether each cell holds the kind of value its column should hold. Dates belong in the date column, text in the description column, numbers in the amount and balance columns. Any row that fails the test has moved, and you can filter for it instead of scrolling.
Add a helper column, headed Aligned, and enter a formula like this in row 2. It assumes the date in A, the description in B, the amount in C and the balance in D:
=AND(ISNUMBER(A2), ISTEXT(B2), ISNUMBER(C2), ISNUMBER(D2))
Fill it down. TRUE means the row has the expected types. FALSE marks a row to inspect. Filter the Aligned column to FALSE and you see every suspect row at once.
Getting the test to work on converted values
The test only works when numbers are real numbers. If the converter left amounts as text, ISNUMBER returns FALSE on every row and the filter shows the whole sheet. Fix that first: making converted numbers editable and summable turns text amounts into values. Amounts that still carry a symbol, such as "$1,250.00", fail too, so removing currency symbols from the amount column comes before the test.
Dates need the same care. A date that arrived as the text "03/14" is not a number. Either convert the date column to real dates, or loosen the test for that column with LEN(A2)>0 while you hunt for shifts.
A worked example
The table below is an example of part of a converted page with one shifted row. The values are made up for illustration.
| Row | A (Date) | B (Description) | C (Amount) | D (Balance) | E | Aligned |
|---|---|---|---|---|---|---|
| 21 | 04/02 | Grocery store 0412 | -63.20 | 2,114.55 | TRUE | |
| 22 | 04/03 | Payroll deposit ACME | 1,850.00 | 3,964.55 | TRUE | |
| 23 | 04/03 | Online transfer to | savings acct 7719 | -400.00 | 3,564.55 | FALSE |
| 24 | 04/05 | Phone bill autopay | -48.99 | 3,515.56 | TRUE |
Row 23 failed because C holds text. The description was split across B and C, which pushed the amount into D and the balance into E. The pattern is "everything from C onward moved one cell right".
Which statement pages shift columns and which stay aligned?
Shifts usually cluster on particular pages because the converter often sets column boundaries page by page. A page full of short descriptions yields tight boundaries. A later page with one long merchant name, a wider amount or a different header can produce a slightly different grid, so its rows shift while earlier pages stay clean.
Page-level patterns worth checking:
- The first page. It often has an account summary box above the transactions. The summary pushes the table down and sometimes changes where the converter thinks columns start.
- The last page. It may hold only a few lines and a closing summary. With little text to measure, the boundaries are guesses.
- Pages with a section change. Statements that list deposits and withdrawals in separate sections can print each with its own column set.
- Pages in another number style. A statement converted on a computer set to a different region can read the amounts on some lines with commas as decimal marks. Changing a decimal comma to a point in Excel covers that case, which can look like a shift when a number splits at the comma.
To see the pattern, add a page column if the converter did not supply one, then build a quick count of FALSE rows per page with COUNTIFS. If every failure sits on pages 3 and 4, fix those pages as a block. If failures are spread thinly across the file, the cause is probably row content, such as long descriptions, rather than page layout.
Realigning shifted rows in bulk with helper columns
Helper columns rebuild each row in the right order without cutting and pasting cell by cell. For every row, a formula picks the value from its normal cell if the row is aligned, or from the cell one to the right if it is not. Once the helper columns are correct, you paste them over the originals as values.
For the example above, where the description split across B and C and everything after moved right, add four new columns:
| New column | Formula in row 2 | What it does |
|---|---|---|
| Date | =A2 |
The date never moved |
| Description | =IF(F2, B2, B2&" "&C2) |
Joins the two halves when the row failed |
| Amount | =IF(F2, C2, D2) |
Takes the amount from one cell right |
| Balance | =IF(F2, D2, E2) |
Takes the balance from one cell right |
F is the Aligned column, next to the overflow cell in E. Fill the formulas down, check a few corrected rows against the PDF, then copy the four new columns and use Paste Special, Values over A to D. Delete column E and the helpers.
When rows shift in different directions
Not every sheet has one shift pattern. Some rows may move right and others left, for example when a missing description pulls the amount into B. Write one test per pattern, such as ISNUMBER(B2) for a left shift, and nest them: =IF(F2, C2, IF(ISNUMBER(B2), B2, D2)). Keep each pattern simple. A formula that tries to handle five cases at once is harder to check than three passes that each handle one.
When to fix by hand
If only two or three rows failed, move them by hand. Select the cells from the first wrong one to the end of the row, drag them one cell left, and join the description. Helper columns pay off when the same shift repeats a dozen times or more.
Shifted amount columns that swap debits and credits
A shift can move money between columns that both hold numbers, which the type test cannot catch. On statements with separate Withdrawals and Deposits columns, a withdrawal that slides one cell right sits under Deposits. Every cell still holds a number, so the row passes, but the sign of the transaction is now wrong.
This is the most expensive kind of shift because totals look plausible. A $400 withdrawal recorded as a deposit puts the account $800 off, and nothing flags it until you compare balances.
The running balance catches it. Add a check column:
=ROUND(D1 + C2 - B2 - D2, 2)
Here B holds withdrawals, C deposits and D the printed balance, with row 1 holding the opening balance. A result of 0 means the row agrees with the balance above it. Any other value is a row where money moved into the wrong column, or where a line is missing. Swap the two cells on the flagged rows and the check returns to 0.
If the statement prints no running balance, compare column totals with the summary box on the first page instead. Total withdrawals and total deposits should each match the printed figures. If withdrawals come out short by an amount and deposits over by the same amount, a row of that amount sits in the wrong column.
Can you stop columns shifting before the next conversion?
You can reduce shifts at the source, but you cannot remove them for every layout. Shifts come from how a tool decides column boundaries, so the most useful changes are using the original digital PDF rather than a printed scan, choosing a converter that reads statement layouts, and testing with a month that has long descriptions.
Things that help:
- Use the bank's own download. A digital statement has exact text positions. A scan or a photo adds tilt and blur, and boundaries drawn on a tilted page drift from one row to the next.
- Avoid print-to-PDF copies. Printing a statement to a new PDF can change fonts and spacing, which moves text against the boundaries a converter expects.
- Convert one statement before a batch. Run the type test on the result. If a month converts cleanly, similar months from the same bank usually do too.
- Prefer converters that check balances. A tool that reconciles the running balance during conversion finds the debit and credit swap described above before you see the file.
When you try a different converter, read what it does with your file afterwards. A bank statement holds account numbers and spending history, so deleting your data from a PDF converter is worth reading before you upload a batch.
FastBankConvert is built to read the layout of a statement and put dates, descriptions, amounts and balances in their own columns, and it tells you before download if the balances do not add up. It is getting ready to open; Get started puts you on the list for trial conversions.