To convert a PDF to an Excel spreadsheet that works, run the conversion, then check six things before you build formulas: one header row, no blank rows, right-aligned numbers, real dates, no merged cells, and a column total that matches the PDF. A sheet that looks like the page but fails these checks will not sort or sum.
What makes a spreadsheet usable after a PDF conversion?
A usable spreadsheet is one where every row is one record and every column holds one kind of value. Sorting, filtering, totalling and pivoting all depend on that shape. A conversion can look perfect on screen and still break each of those, because the page layout is what it copied.
Think of the difference between a picture of a table and a table. A PDF page shows aligned text. Excel needs a grid where each cell knows whether it holds a number, a date or a word. After a conversion, your first job is to confirm that the grid is a grid.
The six-point readiness checklist
Run this list top to bottom on every converted sheet. It takes a couple of minutes on a short file, and it saves hours of strange results later.
- One header row. The column names sit in a single row at the top, one name per column. Page headers repeated every page, or titles split over two rows, must go.
- No blank rows. Blank rows split the data into islands, and filters, tables and pivots stop at the first gap.
- Numbers right-aligned. Numbers align to the right edge of a cell by default. Left-aligned amounts are usually text, and text does not add up.
- Dates as real dates. A real date sorts in calendar order and can be grouped by month. A date stored as text sorts like words, so "10/02" comes before "9/30".
- No merged cells. Merged cells break sorting and filtering, and a pivot cannot read across them. Unmerge them and fill each cell with its value.
- Column total equals the PDF total. Sum the amount column and compare it to the total printed on the PDF. This is the check that proves nothing was dropped.
How to check each point quickly
You do not need formulas for most of it. Click a column letter and look at the status bar at the bottom of Excel: if it shows Sum and Average for the selection, the cells are numbers. If it shows only Count, they are text. For dates, change the cell format to Number. A real date turns into a serial number such as 46001, and text stays as it was.
An example of a failed check
Here is an invented example. A converted list of 40 rows shows an amount column with only a Count in the status bar, and the cells sit on the left. The cause is text, probably from a currency symbol or a trailing space. That one finding explains why SUM returns zero while the figures look fine on screen. Repair steps are in why converted numbers are stored as text, so this post does not repeat them.
Turning the converted range into an Excel table
An Excel table is a named range with its own header row, filters and automatic growth. Pressing Ctrl+T on the converted range makes one. It matters after a conversion because the table enforces the shape you just checked, and everything you build on it follows the data as rows are added.
To make one, click any cell in the data and press Ctrl+T. Confirm that the range is right and that "My table has headers" is ticked, then press OK. Excel adds filter buttons to each header and applies banded rows.
Why a table beats a plain range
- Formulas use column names. A total reads like
=SUM(Table1[Amount])and it still works when you add 200 rows. - Filters and sorting stay attached to the data. You do not reselect a range each time.
- Pivot tables and charts update. Point them at the table and refresh after you paste in more rows.
- Headers stay visible. When you scroll in a long table, Excel swaps the column letters for the header names.
When Ctrl+T warns you
If Excel says a table cannot have blank headers, or it renames headers to Column1 and Column2, your header row has gaps. Fill every header cell with a distinct name first. If it refuses to make a table across merged cells, unmerge them and try again. Both warnings are the checklist catching an error early.
Naming the table
Click anywhere in the table, open the Table Design tab and type a short name such as Statement into the Table Name box. A named table is easier to find in formulas and pivots than the default Table1, and the name does not change when you move the sheet.
Can a converted spreadsheet feed a pivot table?
Yes, a converted spreadsheet can feed a pivot table once it passes the readiness checklist. A pivot table summarises rows by a category, such as total spent per month or per payee. It needs one header row, no blanks in the key columns, numbers in the value column and real dates for grouping.
The usual causes of a pivot that does not work are the same ones from the checklist. If a pivot shows a count where you wanted a sum, the amounts are text. If the months do not group, the dates are text. If you see a row labelled "(blank)", a blank row or an empty header is in the source.
Building a first pivot
- Click inside your table and choose Insert > PivotTable.
- Place it on a new sheet.
- Drag a category field, such as Description, to Rows and Amount to Values.
- Check that Values reads Sum of Amount, not Count of Amount.
- Compare the grand total at the bottom to the total on your PDF.
What to do about messy descriptions
Descriptions in a converted file tend to differ by small details: a date suffix, a branch number or a reference. A pivot sees "CAFE 101" and "CAFE 102" as two payees. Create a helper column that trims the description to the part that repeats, such as the first word or two, and pivot on that. Do it in a new column and keep the original, so you can undo it.
An example of the end result
Suppose 120 invented transactions across a quarter. After the checks, a pivot with months in rows and one column for Sum of Amount lets you see a spend of, say, 3,100.00 in one month against 2,400.00 in another. The numbers only mean something if the grand total matches the PDF, which is why the sixth checklist item comes first, not last.
Freeze panes and filters on a long converted spreadsheet
Freeze panes keeps your header row on screen while you scroll, and filters let you show only the rows you want. On a converted sheet of several hundred rows, they stop you losing track of which column is which, and they turn a long list into something you can interrogate.
To freeze the header row, open the View tab, choose Freeze Panes, then Freeze Top Row. If you made an Excel table, headers already replace the column letters when you scroll inside the table, but freezing is still useful when the table sits below a title. Select the row below your header, then choose Freeze Panes > Freeze Panes.
Filters worth using
- A text filter on Description to isolate one payee or a word such as "fee".
- A date filter to show one month or a custom range. This only works when dates are real dates.
- A number filter for amounts above a limit, which is a fast way to find the large items.
Sorting without scrambling rows
Always sort with the whole table selected, or use the table's filter menu, so each row moves as a unit. Sorting a single column in a plain range moves only that column and breaks the link between a date and its amount. If you did it by accident, press Ctrl+Z immediately. A habit that helps: add a first column with the original row number before you sort, so you can restore the original order later.
Repeated page headers
Many PDF conversions carry the column header text again at the top of every page. On a long file those rows pollute sorts and sums. Removing repeated page headers with Power Query filters shows a cleaner way than deleting them one at a time.
Spreadsheet totals that don't match the PDF
When a SUM differs from the total on the PDF, the cause is nearly always one of four things: text numbers excluded from the sum, rows that were not converted, rows counted twice, or signs lost on debits and credits. Compare the row count and a subtotal for one page to find which.
Work through these checks in order, because each one is quick.
- Count the rows. Compare the number of data rows in Excel with the number of lines on the PDF. If they differ, a row went missing or a wrapped line became a separate row.
- Check the status bar. Select the amount column. If it shows a Count but no Sum, or a Sum lower than you expect, some cells are text.
- Compare page by page. Sum the rows from page 1 and compare them to the page's printed subtotal, if there is one. Move on to the next page. The first page that does not match holds the problem.
- Look at the signs. A debit shown in brackets or with a minus sign in the PDF may arrive as a positive number. A total that is off by exactly twice one amount often means one sign flipped.
- Look for duplicates. A row repeated at a page break is a common cause of a total that is too high.
An example with numbers
Take a statement whose printed total of withdrawals is 8,415.60, and a converted sheet that sums to 8,215.60. The difference is exactly 200.00, so look for a single missing line of 200.00. Sort by amount, find that no such row exists, then search the PDF for 200.00. The missing item is usually a row at the foot of a page or one that wrapped onto a second line.
Why a clean-looking sheet can still be wrong
A converter reads layout, not accounting. It cannot know that a figure should add up. That is why the total check is yours to run. For statements, FastBankConvert is built to warn you before download if the balances do not add up; it is not open yet, and Get started takes your email for when trials open.
Where to go next
If your file works but you are on a Mac, the route to a clean sheet differs, and converting PDF to Excel on a Mac explains what changes. If you are not even sure your PDF can be converted at all, whether you can turn a PDF into an Excel file answers that first.