To bring a PDF table with subtotals to Excel without counting them twice, convert it, then flag every subtotal and total row, copy each group's name into a column on its detail rows, and remove the flagged rows. Rebuild the subtotals with SUMIFS or a pivot table, and check them against the printed figures before you delete anything.
Why subtotal rows double a PDF table's total in Excel
Subtotal rows double a total because, once converted, they look like any other row. The PDF printed them in bold or with a line above, but Excel receives only the text and the number. A SUM over the amount column then adds every detail row and every subtotal, which counts each group twice.
A grand total row makes it worse. If the report has groups, subtotals and a grand total, a plain SUM over the whole column returns roughly three times the true figure: the detail once, the subtotals again, and the grand total a third time. Nobody mistakes three times for right, but a report with only one or two subtotals can be off by an amount that looks plausible.
The same rows cause other trouble:
- Averages and counts. An average over a column with subtotals in it is pulled towards the large subtotal values. A count of rows includes them.
- Sorting. Sort the sheet by amount and the subtotals jump to the top, cut off from the groups they summed.
- Pivot tables. A pivot built on the raw sheet treats each subtotal as one more record, so every pivot figure includes it.
- Charts. A chart of monthly spending shows a spike at every subtotal.
So the rule is simple: the data sheet holds detail rows only. Totals are calculated from the detail, never typed or pasted in alongside it.
How do you spot subtotal rows in a converted PDF table?
You spot subtotal rows by their wording, by what is missing from them, and by their arithmetic. Most carry a word such as "Subtotal", "Total" or "Group total". Many have no date, no reference or no quantity. And every true subtotal equals the sum of the rows directly above it, back to the previous subtotal.
A helper column makes the flags visible. With the description in column B, the amount in column D and the date in column A:
=IF(OR(ISNUMBER(SEARCH("total",B2)),AND(A2="",D2<>"")),"subtotal","")
That marks any row whose description contains "total", including "Subtotal" and "Grand total", and any row that has an amount but no date. Adjust the second test to the column your report leaves empty on subtotal lines.
Lines that look like subtotals but are not
Word matching has false positives. A detail row described as "Total Gym membership" or "Totally Fresh Produce" contains "total" and gets flagged. Look at every flagged row before you remove anything. A quick way is to filter the helper column for "subtotal" and read only those rows, which on most reports is a short list.
Subtotals with no label
Some reports print the subtotal as a bare number under the group, with no text at all. The arithmetic test is the reliable one there. A row is a subtotal if its amount equals the sum of the detail rows since the last flagged row. A running check in a second helper column can test this, but on a short report it is quicker to read the PDF and mark them by hand.
Text-only conversions
If you copied the report as text rather than converting it into columns, the subtotal lines are still there, just inside longer cells. Getting PDF text into Excel covers splitting that text into columns first, after which the same flags apply.
Moving PDF group names into their own column
Moving group names into their own column keeps the information a subtotal carried. A subtotal row usually says which group it closed, such as "Total Marketing", and the group heading often appears once above the detail. Once the subtotal row is gone, each detail row needs that name beside it, or the grouping is lost.
There are two common layouts, and each takes a slightly different formula.
Heading above the group
When the group name sits on its own line above its rows, with no amount, fill it down. In a new column E:
=IF(D2="",B2,E1)
A row with no amount is a heading, so it starts a new group name. Every row below it inherits that name until the next heading. Enter the first row's group name by hand in E1 or E2, then fill down. Paste the column as values when done, so the names stay put after sorting.
Name only on the subtotal line
When the name appears only at the bottom, as in "Total Marketing", the fill must run upwards. Sort a copy of the sheet in reverse row order with a row-number column, fill down with the same idea, then sort back. Or use a formula that looks for the next subtotal below each row:
=TEXTAFTER(INDEX(B2:B$500,MATCH("Total *",B2:B$500,0))," ")
This finds the first "Total" line at or below the current row and takes the group name after the word. It works for reports that label every subtotal the same way.
Group names are sometimes more than a word. A supplier report may print the supplier's full address above each group. If you need it as fields, extracting addresses from a PDF to Excel shows how to split it into street, city, state and postal code columns.
Rebuilding PDF subtotals with SUMIFS or a pivot table
Rebuilding subtotals means calculating them from the detail rows, once the printed ones are removed. SUMIFS gives a small summary table beside the data, one formula per group. A pivot table does the same in a few clicks and updates when you change the data. Either way, the figures come from the detail and cannot double.
Here is a worked example from a made-up expense report. All names and amounts are examples. As converted from the PDF:
| Date | Description | Amount |
|---|---|---|
| Marketing | ||
| 03/04 | Print ads | 1,200.00 |
| 03/11 | Trade show stand | 2,450.00 |
| Total Marketing | 3,650.00 | |
| Operations | ||
| 03/06 | Courier | 180.00 |
| 03/19 | Office supplies | 320.00 |
| Total Operations | 500.00 | |
| Grand total | 4,150.00 |
A SUM over that Amount column returns 12,450.00, three times the true total. After flagging and filling, the flat table:
| Department | Date | Description | Amount |
|---|---|---|---|
| Marketing | 03/04 | Print ads | 1,200.00 |
| Marketing | 03/11 | Trade show stand | 2,450.00 |
| Operations | 03/06 | Courier | 180.00 |
| Operations | 03/19 | Office supplies | 320.00 |
The rebuilt subtotals, with department names in G2:G3:
=SUMIFS($D:$D,$A:$A,G2)
That gives 3,650.00 for Marketing and 500.00 for Operations, and =SUM(D:D) now gives the true 4,150.00.
A pivot table built on the flat table, with Department in rows and Amount in values, gives the same figures and a grand total row at the bottom. Excel's own Subtotal command on the Data tab can also insert subtotal rows, but those rows are formulas using the SUBTOTAL function, which skips other SUBTOTAL results. Use it on a printed or shared copy, not on the sheet other formulas rely on.
Subtotal checks before deleting PDF total rows
Check subtotals before deleting the printed total rows, so you have proof the detail is complete. Put the printed subtotals beside your rebuilt ones and subtract. Every difference should be zero. A difference means a detail row was dropped, doubled or misread during conversion, and the printed figure is your only reference for finding it.
A short routine:
- Copy the flagged subtotal rows to a separate sheet before removing them. Keep the group name and the printed amount.
- Next to each, calculate the rebuilt subtotal with SUMIFS.
- Add a difference column. Any value other than zero needs investigating.
- Check the grand total the same way, against the sum of the detail.
- Only then delete the subtotal rows from the data sheet.
When a difference appears, its size is a clue. A difference that equals one detail amount usually means a row went missing, often one at the bottom of a page. A difference of a round amount, such as 100 or 1,000, points to a misread digit. A difference equal to the whole subtotal means the group was mislabelled, and its rows were counted under a neighbour.
Keep the check sheet in the workbook. Anyone who later asks whether the figures tie to the report can see the answer.
If you need only a few printed totals from a long report rather than the whole table, pulling single values out of a PDF is quicker than converting and cleaning everything.
When should PDF subtotal rows stay in the sheet?
Subtotal rows should stay when they are the data you need, or when the detail behind them is missing. A summary page that prints only department totals has no detail to rebuild from. A report that truncates its detail after the first page can be checked only against its subtotals. In both cases, keep the subtotals and label them clearly.
Keep them in a sheet of their own, not mixed with detail rows. A summary sheet with one row per group and a "Source: printed subtotal" note is honest about what it is, and it can sit beside a detail sheet without either polluting the other.
Schedules are a common case. A payment or depreciation schedule may print a subtotal for each year. Those yearly figures are often what the reader wants, and converting a PDF schedule to Excel covers the layouts that use them.
Other cases to keep the printed figure:
- Audit trail. The printed subtotal is what the author approved. Keep it for comparison, even after you rebuild your own.
- Rounded detail. If the detail amounts are rounded but the subtotals are not, rebuilt totals will drift by a few cents. Note the drift rather than forcing a match.
- Mixed units. A report that subtotals quantities in one group and money in another should not be summed as one column at all.
Bank statements rarely print subtotals, but many print a daily or monthly balance line that must not be added to the transactions. FastBankConvert is built to separate transaction amounts from balances, and to warn you if the balances do not add up before you download. It is not open yet, and the get-started page takes your email for the launch.