To convert a PDF to Excel with Office 365, open the PDF in Word, let Word rebuild it as an editable document, copy the table and paste it into Excel. Then unmerge cells, fill down blanks and format the amounts as numbers. You pay nothing beyond the plan you already have, and the cleanup is the real work.
What can Office 365 do with a PDF already?
An Office 365 plan gives you three ways to work with a PDF: Excel can import it through the Data tab, Word can open it and rebuild it as an editable document, and the web versions of both apps cover part of the same ground. Which of them you have depends on your plan and your app version.
The three routes, in the order most people should try them:
- Excel, Get Data from PDF. Excel finds tables in the file and loads the one you choose. Microsoft documents it under Data, Get Data, From File, From PDF on its page about importing data from external data sources, which lists Excel for Microsoft 365 among the versions it applies to. When it is there, it is usually the cleanest route. The steps are in importing a PDF to Excel with Get Data, so this post does not repeat them.
- Word, open the PDF. Word converts the PDF into a document with real paragraphs and tables, then you copy the table into Excel. This is the route the rest of this post covers, because it works when Get Data is missing or picks the wrong table.
- Copy straight from a PDF viewer. You can select text in any viewer and paste it into Excel. The paste arrives as one column, and copying a PDF into Excel covers that mess.
A note on names: Microsoft renamed Office 365 to Microsoft 365 some years ago, and many people still say Office 365. A plan that includes the desktop apps will have both Word and Excel. A plan that includes only the web apps is covered in the last section.
All three routes need a PDF with real text. A quick check is to try to highlight one line of the statement in a viewer. If it highlights, the text is real. If you get a picture, you are looking at a scan, and none of these steps will read it. Converting a scanned PDF to Excel for free explains what to do about that.
The Word route: opening a statement PDF in Office 365 Word
The Word route has eight steps: open the PDF in Word, accept the conversion, find the transaction table, copy it, paste it into Excel, unmerge cells, fill down blanks and set the amount columns to currency. Word does the rebuild, and Excel does the cleaning.
Here are the steps for an example statement. Rename the columns to match your own.
- In Word, choose File, Open and pick the PDF. If it does not appear in the list, set the file type filter to All Files.
- Word shows a message that it will convert the PDF to an editable Word document and that the result may not look exactly like the original. Click OK.
- Wait. A long statement can take a minute to convert, and page breaks may shift.
- Scroll to the transactions. Statements often convert as a table, but some come in as text boxes or tabbed lines. Click inside the table, and the Table Design and Layout tabs should appear.
- Select the whole table. Click the small cross at its top left corner, then press Ctrl-C (Command-C on a Mac).
- In Excel, click cell A1 of a blank sheet and paste.
- Fix the merged cells, fill down blanks and format the columns, as described in the sections below.
- Save as .xlsx, and keep the PDF as your source of truth.
A cleanup checklist for the Excel side
Once the table is in Excel, work through these in order:
- Select all and choose Unmerge Cells from the Merge & Center menu (see the merged-cell section below).
- Delete empty rows and repeated page headers.
- Fill down any blank cells in the date column where several lines share a date.
- Set the date column to a date format and the amount columns to Currency or Number.
- Add a check: opening balance, plus deposits, minus withdrawals, should equal the closing balance.
When Word gives you text boxes
Sometimes Word cannot rebuild the page as a table, and the statement arrives as floating text boxes or lines separated by tabs. Copying that into Excel gives you one cell per line. When that happens, try the Get Data route first, or copy page by page. If neither works, the file is a poor match for the Word route, and trying harder rarely pays.
What formatting survives an Office 365 PDF conversion?
Text, table borders and most numbers survive the Word route. Column widths, fonts, page headers, shading and exact positions often change. For a bank statement that matters little, because you want the numbers, not the look. The things that can hurt you are merged cells, split rows and amounts that turn into text.
Here is a rough guide, from what usually carries over to what usually does not. Results vary by statement, so check your own.
| Element | Usually survives? | What to check |
|---|---|---|
| Transaction text | Yes | Wrapped descriptions that became two rows |
| Dates | Yes, as text | Convert to real dates before sorting |
| Amounts | Yes, often as text | Left-aligned values or a green triangle mean text |
| Negative signs and CR/DR marks | Partly | A trailing minus or CR stays text |
| Table borders | Often | Lost if Word built text boxes |
| Fonts and colors | Rarely matters | Not needed for data |
| Logo and page footer | Becomes junk rows or stray text | Delete before totaling |
| Column widths | Changes | Resize in Excel |
The search phrase people use here is "convert PDF to Excel without losing formatting." Be careful with that goal. A bank statement's layout is a print format, not a spreadsheet format. If you try to keep it exactly, you keep the merged cells, the spacer columns and the page headers that cause trouble later. The better goal is to keep the data, and to drop the layout. Rebuild a simple format in Excel afterwards, such as one header row, one transaction per row and one column per field.
Amounts that arrive as text
Statement amounts often paste as text, especially those with a trailing minus, a parenthesis or a CR. Excel then ignores them in sums. Select the column, use Data, Text to Columns and finish the wizard with a Number format, or use Find and Replace to strip the extra characters. The full fix is written up in fixing text numbers in a free XLSX export.
Office 365 merged cells: the mistake that breaks sorting
Excel cannot sort or filter a range that contains merged cells of different sizes, and the Word route often produces them. Merged cells combine several cells into one, which looks fine on a printed page and fails the moment you sort. Unmerge, then fill the gaps with the value above.
Here is why it breaks. A sort moves whole rows. When one row has a merged cell spanning two columns and the next row does not, Excel cannot map the cells to each other and shows an error that all merged cells need to be the same size. A filter has a similar weakness: a merged date cell belongs to one row only, so the rows below it look empty.
A before and after row
Here are the same two example rows before and after the fix, with made-up values.
Before, with the date merged across two lines and a description merged across two columns:
| Date (merged down) | Description (merged across) | Amount | |
|---|---|---|---|
| 03/02 | Example Grocer #114 | 48.20 | |
| (blank) | Example Power Co | 96.75 |
After unmerging and filling down:
| Date | Description | Amount |
|---|---|---|
| 03/02 | Example Grocer #114 | 48.20 |
| 03/02 | Example Power Co | 96.75 |
The second row now carries its own date, so it can move without losing it.
The fix, step by step
- Select the whole range with Ctrl-A.
- On the Home tab, open the Merge & Center menu and choose Unmerge Cells. Excel splits each merged area, keeping the value in the top left cell and leaving the others blank.
- Select the date column. Choose Home, Find & Select, Go To Special, Blanks, and click OK. Excel highlights only the blank cells.
- Type an equals sign and press the Up arrow, then press Ctrl-Enter. Every blank cell now points at the cell above it.
- Copy the column and paste back as Values, so the formulas become plain dates.
- Delete any column that is now empty because a description spanned two cells.
- Test: sort the date column and then undo. If Excel sorts without a complaint, the range is clean.
Keep a copy of the sheet before step 5. Pasting values over the formulas cannot be undone once you save and close.
Is Excel for the web in Office 365 enough for a PDF?
Excel for the web comes with an Office 365 plan, but it offers fewer tools than the desktop app, and whether it can import a PDF depends on what Microsoft has enabled for your account. Plan to use the desktop app for the Word route, and treat the web app as a place to clean and share.
You can check in two minutes. Sign in to your account on the web, open Excel and look at the Data tab for a Get Data entry that mentions PDF. If you see it, try it. If you do not, open Word for the web and see whether it can open your PDF for editing. If neither works, you need the desktop apps, which only some plans include.
What the web apps are good at, once the table has been pasted in:
- Unmerging cells and filling down blanks, using the same menu items as the desktop app.
- Sorting and filtering to test that the data behaves.
- Sharing the cleaned sheet with a client or an accountant.
Where they are weaker is the first step, the conversion. The web version of Word may not open a PDF the way the desktop one does, so do not build a monthly routine on it without testing.
When the subscription route stops making sense
The Office 365 route costs you no extra money, and it costs you time. For a two-page statement, expect about 10 to 20 minutes with practice, mostly spent on the merged cells and text amounts. For a 20-page statement, the page breaks and repeated headers push that toward an hour or more. These are estimates, so time one page on your own file and multiply.
If you convert statements often, or the files run long, paying per document can be cheaper than your hour. CheapBankConvert is getting ready to open, with early prices of $2.99 for a statement up to 10 pages and $5.99 up to 40. Its .xlsx file stores amounts as numbers, so the text-amount fix above is not needed. For a ranking of every free option you already own, read which tools you already have can convert a PDF to Excel.