Exporting Invoices to Excel With Totals That Add Up

The finance team asks for September's invoices in Excel. The export arrives with the invoice total repeated on every line, so the column sums to four times the real revenue, tax that differs from the invoice by a cent here and there, and a gap in the numbering that nobody can explain. None of these is hard to prevent. They come from treating a two-level structure, a document with lines, as a flat list, and from not saying how tax is rounded. This article shows how to export invoices so the totals add up, with the checks that prove it. It is a case of Excel reports that finance teams rely on, and it relies on the rounding policy in rounding rules that survive audit in spreadsheets and SQL.

Two levels, two sheets

An invoice has a header (number, date, customer, currency, totals) and lines (product, quantity, unit price, tax rate). If you put both in one flat sheet with the header fields on every line, any column from the header, such as the invoice total, is repeated per line. Summing it counts it once per line. This is the commonest error in invoice exports.

Two sheets avoid it.

Lines (one row per invoice line): invoice_no, line_no, sku, description, qty, unit_price, net_amount, tax_rate, tax_amount, gross_amount.

Invoices (one row per invoice): invoice_no, invoice_date, customer, currency, net_total, tax_total, gross_total, status.

Each sheet sums correctly on its own: the sum of net_amount in Lines is total net revenue, and the sum of net_total in Invoices is the same number. If someone wants a flat view for a pivot, they can join the sheets themselves; give them both and the key.

Sample data for the rest of the article:

Compute amounts in SQL with exact decimals

Store money as numeric, and compute line and document amounts in the query, not in cell formulas.

SELECT l.invoice_no, l.line_no, l.sku, l.description, l.qty, l.unit_price,
       round(l.qty * l.unit_price, 2)                      AS net_amount,
       l.tax_rate,
       round(l.qty * l.unit_price * l.tax_rate, 2)         AS tax_amount,
       round(l.qty * l.unit_price, 2)
         + round(l.qty * l.unit_price * l.tax_rate, 2)     AS gross_amount
FROM invoice_lines l
JOIN invoices i ON i.invoice_no = l.invoice_no
WHERE i.invoice_date >= DATE '2026-09-01' AND i.invoice_date < DATE '2026-10-01'
ORDER BY l.invoice_no, l.line_no;

This rounds tax per line. For INV-1001, line 1 has net 29.97 and tax 5.99 (29.97 × 0.20 = 5.994, rounded to 5.99), line 2 has net 4.99 and tax 1.00 (0.998 rounds to 1.00). The tax total is 6.99. If the rule were to round tax on the invoice total instead, net 34.96 × 0.20 = 6.992 gives 6.99, which agrees here and will not always. For INV-1002, 10 × 0.33 = 3.30, tax 0.66. The rounding rule must be the one the invoicing system applied when it issued the documents; the export reproduces it and does not choose. Read the stored amounts if the system saved them, and recompute only to verify.

If the invoicing system stores line and document amounts, export those stored values. Recomputing is the check, not the source.

The document sheet

SELECT i.invoice_no, i.invoice_date, c.name AS customer, i.currency, i.status,
       sum(round(l.qty * l.unit_price, 2))                          AS net_total,
       sum(round(l.qty * l.unit_price * l.tax_rate, 2))             AS tax_total,
       sum(round(l.qty * l.unit_price, 2)
         + round(l.qty * l.unit_price * l.tax_rate, 2))             AS gross_total
FROM invoices i
JOIN customers c ON c.id = i.customer_id
JOIN invoice_lines l ON l.invoice_no = i.invoice_no
WHERE i.invoice_date >= DATE '2026-09-01' AND i.invoice_date < DATE '2026-10-01'
GROUP BY i.invoice_no, i.invoice_date, c.name, i.currency, i.status
ORDER BY i.invoice_no;

For INV-1001: net 34.96, tax 6.99, gross 41.95. For INV-1002: net 3.30, tax 0.66, gross 3.96. Credit notes appear as negative documents (with a link to the original) and belong in the same export so that the totals net out; make status or a doc_type column say what each row is, and decide whether canceled invoices are included (usually yes, with status, so the numbering is complete).

Checks that prove the totals

Run these in the export job and write the results to the README.

Lines sum to documents. For each invoice, the sum of line gross amounts equals gross_total, and the document totals equal what the invoicing system stored. A mismatch means a rounding rule or a missing line.

SELECT i.invoice_no, i.gross_total AS stored, s.gross AS recomputed
FROM invoices i
JOIN (SELECT invoice_no, sum(gross_amount) AS gross FROM invoice_lines GROUP BY invoice_no) s USING (invoice_no)
WHERE i.gross_total <> s.gross;

Documents sum to the ledger. The sum of net totals for the period equals the revenue posted in the ledger, and the sum of tax totals equals the tax liability account movement. Differences point at timing (an invoice dated in one period and posted in another) or unposted documents.

Numbering has no gaps and no duplicates. Many jurisdictions require sequential invoice numbers. Check:

SELECT n AS missing_number
FROM generate_series(1001, 1250) n
LEFT JOIN (SELECT substring(invoice_no from '\d+')::int AS num FROM invoices) x ON x.num = n
WHERE x.num IS NULL;

Adjust the range and the pattern to your format. Gaps need an explanation (voided, never issued), and duplicates are errors. Report both.

Counts. The number of rows in Lines and Invoices equals the counts from the source for the period.

Tax rate sanity. Every tax rate on a line is one of the valid rates for the date and country. A rate of 0.02 instead of 0.20 is a keying error that the check finds.

Writing the workbook

Write amounts as numeric cells, with a number format, never as strings with symbols:

with pd.ExcelWriter("invoices_2026-09.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_formulas": False}}) as xw:
    inv.to_excel(xw, sheet_name="Invoices", index=False)
    lines.to_excel(xw, sheet_name="Lines", index=False)
    wb = xw.book
    money = wb.add_format({"num_format": "#,##0.00;[Red]-#,##0.00"})
    pct = wb.add_format({"num_format": "0%"})
    ws = xw.sheets["Lines"]
    ws.set_column("F:F", 12, money)
    ws.set_column("G:G", 12, money)
    ws.set_column("H:H", 8, pct)
    ws.set_column("I:J", 12, money)
    ws.freeze_panes(1, 0)
    ws.autofilter(0, 0, len(lines), len(lines.columns) - 1)

Convert Decimal values to floats at the point of writing; the stored double carries about 15 digits, which is plenty for money at normal scales, and the checks above ran in the database on exact values. Write invoice numbers as text. Add a totals row to each sheet using SUBTOTAL(109, ...) so it follows filters, and a README sheet with the period, the commit, the check results and the rounding rule (putting a README sheet into every generated workbook). User-supplied text such as customer names and descriptions is written as text so that nothing is evaluated as a formula (formula injection in Excel exports).

Currency and tax on the sheet

Keep the original currency on every row and do not sum across currencies. If a base-currency total is needed, add columns for the rate and the converted amounts and show the rate source and date (handling multi currency columns in workbooks and tables). Show tax by rate in a small summary table (net and tax for each rate), because tax returns are prepared that way and a reviewer wants to check each rate separately.

Privacy

Invoices carry customer names and sometimes addresses and tax IDs. Include only what the reader needs. Share through an access-controlled location, not an open email thread. Strip hidden data and metadata before release.

When an Excel export is the wrong invoice format

An Excel export is the wrong format for the invoice itself: the legal document is what the invoicing system issued, in a fixed format such as PDF or a structured e-invoice standard, and a workbook is a working copy. It is the wrong format for sending to customers, who need the document, and for submitting to tax authorities that specify a format. It is the wrong format when the volume is large enough that CSV or a database feed serves analysis better (sending database results to Excel covers the simple case). Use the workbook for reconciliation, analysis and review. The PostgreSQL manual describes round and numeric types, and the XlsxWriter documentation on number formats lists the format codes.