# Sales Report PDF to Excel: Totals by Product and Day

- Author: FastBankConvert (https://fastbankconvert.com)
- Published: 2026-10-17
- Category: Other financial documents
- Canonical: https://fastbankconvert.com/blog/sales-report-pdf-to-excel

> Turn a sales report PDF to Excel: grouped rows by product or day, subtotals to remove, returns as negatives, and rebuilding the totals.
To turn a sales report PDF to Excel, extract its rows, then move each group heading (a product or a day) into a column on every row beneath it. Delete the subtotal and grand total rows, enter returns as negative quantities and amounts, and rebuild the totals with a pivot table. The rebuilt grand total must equal the printed one.

## Group headings in a sales report PDF

Most sales reports are grouped. A product name prints once as a heading, and the rows under it list each day or each sale for that product. The heading labels the rows that follow; it is not data. In Excel, copy it into a Product column on each of those rows so every row stands on its own.

### Why the heading has to move

After extraction, the product name sits in one cell and the rows below have blank product cells. Sort that sheet by date and the link between rows and products disappears. A filter on one product shows only the heading. Every later step, from SUMIFS to pivot tables, needs the product on the same row as the numbers.

### Filling the column down

Add a Product column. In its first data row, use a formula that takes the heading when the row is a heading and the value above otherwise: `=IF(C2="", A2, F1)`, where C is the quantity column (empty on heading rows) and A holds the heading text. Fill down. Then copy the column and paste it as values, so the formula no longer depends on the heading rows you are about to delete.

### Two levels of grouping

Some reports group twice, for example by store and then by product, or by day and then by register. Repeat the fill for each level, outer first. You end up with Store, Product and the line values on every row, which is all a pivot table needs to rebuild either grouping.

## Subtotal rows inside a sales report

Subtotal rows print after each group, such as "Total Coffee beans 1kg", and the grand total prints at the end. They are sums of rows already in the sheet. Leave them in and every total you build counts the same sales twice. Copy them to a check table first, then delete them from the data rows.

### Finding them

Subtotals usually carry the word "Total" or "Subtotal" in the label column, or a quantity and amount with no date. Filter for either pattern. Some reports also print page totals and "carried forward" lines at the bottom of each page; those are subtotals too, and they appear in the middle of a group, which makes them easy to miss.

### Keep them as checks

Before deleting, copy each subtotal into a small table: group name, printed quantity, printed amount. Once the pivot table exists, a lookup next to each printed subtotal compares it with the rebuilt figure. That turns the report's own arithmetic into a proof that your extraction is complete.

### Header and footer rows

Page headers can repeat the report title, date range, store name and column labels on every page. Filter them out the same way. The date range is worth keeping once, in a cell above the table, because a sales report with no period is hard to use a month later.

## Should returns in a sales report be negative numbers?

Returns in a sales report should be negative numbers in Excel: a negative quantity and a negative amount. Reports show them in many ways, such as brackets, a minus sign, a separate Returns column, or a positive number on a row labelled "Return". Convert each style to a signed number, so a plain SUM nets sales and returns correctly.

### Recognising each style

- **Brackets**, such as (18.00): replace "(" with "-" and remove ")" in the amount columns only.
- **Trailing minus**, such as 18.00-: some point-of-sale exports print this. Excel does not read it as a number. `=-VALUE(SUBSTITUTE(D2,"-",""))` on those rows fixes it.
- **A Returns column**: keep it, but also add a Net column of sales minus returns.
- **Positive amount, "Return" label**: multiply the amount and quantity by -1 on rows where a Type column says return.

### Voids and discounts

A void cancels a sale that was rung up by mistake. Some reports list the sale and the void; others leave both out. If both are listed, keep both, with the void negative. Discounts may print as their own rows or reduce the line amount. Give discount rows a type of their own so you can see gross sales, discounts and net sales separately.

### Returns of products sold earlier

A return processed today can refer to a sale from last month. In a day-by-day report it lands on today's date. That is correct for cash and daily sales totals. If you need returns matched to the original sale, the report will not do it; you need a transaction export with the original receipt reference.

## Rebuilding sales report totals by product and day

With the group headings filled down, subtotals removed and returns signed, the sheet is a flat list. A pivot table on it rebuilds every total the PDF printed: by product, by day, or by both. If each rebuilt subtotal matches the printed one, the extraction is complete.

### Worked example: a small report

The report below is an example for two products over two days. As printed:

| Row | Date | Qty | Amount |
| --- | --- | --- | --- |
| Coffee beans 1kg | | | |
| | 2026-10-01 | 12 | 216.00 |
| | 2026-10-02 | 9 | 162.00 |
| Return | 2026-10-02 | (1) | (18.00) |
| Total Coffee beans 1kg | | 20 | 360.00 |
| Ceramic mug | | | |
| | 2026-10-01 | 5 | 60.00 |
| | 2026-10-02 | 7 | 84.00 |
| Total Ceramic mug | | 12 | 144.00 |
| Grand total | | 32 | 504.00 |

As flat rows:

| Product | Date | Type | Qty | Amount |
| --- | --- | --- | --- | --- |
| Coffee beans 1kg | 2026-10-01 | sale | 12 | 216.00 |
| Coffee beans 1kg | 2026-10-02 | sale | 9 | 162.00 |
| Coffee beans 1kg | 2026-10-02 | return | -1 | -18.00 |
| Ceramic mug | 2026-10-01 | sale | 5 | 60.00 |
| Ceramic mug | 2026-10-02 | sale | 7 | 84.00 |

A pivot with Product as rows gives 20 and 360.00 for coffee beans, 12 and 144.00 for mugs, and 32 and 504.00 overall. Those match the printed subtotals and grand total. Put Date as rows instead and you get the daily view: 276.00 on October 1 and 228.00 on October 2.

### Totals that the PDF never printed

The flat list answers questions the printed report could not: returns as a share of sales by product, sales by weekday, or the best day for each item. That is often the reason to convert the report at all.

### Linking sales to stock

Quantity sold by product is half of a stock count. [Turning an inventory report PDF into items, counts and values](/blog/inventory-report-pdf-to-excel) produces the other half with the same Product column, so a lookup joins the two.

## Why doesn't the rebuilt sales total match the sales report grand total?

When a rebuilt sales total does not match the printed grand total, the cause is usually one of four things: a subtotal row left in the data, a return entered as positive, tax included in one figure but not the other, or a date range that differs. The size of the gap normally tells you which one.

### Reading the gap

- **Gap equals a printed subtotal**: a subtotal row is still in the data.
- **Gap equals twice a return**: a return is positive when it should be negative.
- **Gap is a steady percentage of sales**: tax is in one total and not the other. Check whether the report's amount column is before or after tax.
- **Gap equals one day's sales**: the extraction picked up a page from another period, or missed one.

### Rounding

A report that shows prices to the cent but computes tax per line can produce a grand total that differs from the sum of the rounded lines by a cent or two. If the gap is that small and the subtotals match, record it and move on.

### Sales on credit

A sales report counts a sale when it is made, whether or not the customer has paid. Unpaid invoices show up on an accounts receivable report instead, and [converting an aging report PDF into buckets that still total](/blog/aging-report-pdf-to-excel) covers that one. The two together explain the gap between sales and the cash that reached the bank.

## A year of daily sales reports in one workbook

A busy shop can produce a sales report every day. A year of them stacked into one sheet can mean tens of thousands of rows, and a workbook full of formulas grows slow and heavy. Keep the data as values in one table, convert formulas to values once a step is done, and let a pivot table do the totals.

### Keeping the file lean

Fill-down formulas, lookups on every row and conditional formats over whole columns all add weight. After you fill the Product column and sign the returns, paste them as values. Keep the check formulas on the small subtotal table, not on every data row. If the file still grows too big, [trimming a converted Excel file that is too large](/blog/converted-excel-file-too-large) explains where the size comes from and what to remove.

### Appending each new day

Add each day's flat rows to the bottom of one Excel table. The pivot table picks them up on refresh. Keep one column with the report date the rows came from, so a duplicate import is easy to spot: two sets of rows with the same report date and product.

### Card sales and the bank

Card sales in the report reach your bank account later, as deposits from the card processor. The bank statement is the record of what arrived. FastBankConvert is getting ready to open as a converter for exactly that file, turning a bank statement PDF into Excel or CSV and flagging balances that do not add up; [sign up on the get-started page](/get-started) to be told when it does.