How to Analyze a QuickBooks Online Export in Excel

QuickBooks Online is built to keep the books correct. It is less good at the questions an owner or finance lead actually asks on a Tuesday afternoon: which vendors grew the most this year, why software spend jumped in March, whether one category is quietly creeping up every month. The standard reports can answer some of this with enough customizing, but most people end up exporting to Excel. The export works — it just isn't shaped like data. It's shaped like a printed report. This guide covers which reports to export, the structure traps that break sums and pivots, and the questions worth asking once the file is clean.

Which report to export

Every QuickBooks Online report can go to a spreadsheet: open it from Reports, set the date range, then use the Export menu and choose Export to Excel. Which report you start from decides what you can ask.

For analysis, Transaction Detail by Account is usually the best starting point: it has the individual lines, so you can group them any way you like later. The Profit and Loss by month is a summary — useful to check against, but you can't get vendor-level answers out of it.

Typical columns on a transaction detail report include the date, the transaction type (Expense, Bill, Check, Invoice, Journal Entry and so on), the reference number, the name (vendor or customer), a memo or description, the account, a split column, and the amount. The exact set depends on how the report is customized — you can add or remove columns under Customize before exporting, and it's worth adding any you'll want to group by later.

Trap 1: title rows above the header

The export starts with a few lines of report furniture — the company name, the report name, and the date range — before the real column headers. Some exports also end with a footer line showing the accounting basis and when the report was run. If you build a pivot from row 1, Excel treats the company name as a header and everything below it as one text column.

Fix: delete the rows above the real header row and anything below the last transaction. The header row is the one with Date, Transaction Type, Num, Name and so on.

Trap 2: grouping and subtotal rows

This is the one that inflates every number. By default the report is grouped by account: each account gets a heading row with its name, then its transactions, then a "Total for …" row. Parent accounts get their own totals on top of their sub-accounts' totals. Summing the amount column in that export counts each transaction once, then again in its account total, and possibly again in a parent total.

Two ways to fix it:

• Before exporting, customize the report and remove the grouping (set the group-by option to none) so you get a flat list. You lose the visual grouping but get one clean row per line — exactly what a pivot wants. If you do this, make sure the Account column is included, because the account name no longer appears as a heading.
• After exporting, delete every row whose first cell starts with "Total for", and every heading row that has an account name but no date. Then fill the account name down into a new column so each transaction knows which account it belongs to (the same Go To Special → Blanks trick from cleaning messy Excel data).

The first option is less work and less error-prone. Use it whenever you can.

Trap 3: split transactions

A single bill can be split across several accounts — one vendor invoice for $1,200 might be $900 to Software and $300 to Office Supplies. In an account-grouped report each part appears under its own account, which is correct. But the Split column shows the other side of the entry, and for multi-line transactions it shows the word -Split- rather than an account name.

What this means in practice:

• For "spend by category", use the Account column (the account this line hits), not the Split column.
• For "spend by vendor", sum lines by Name — the parts of a split bill add back up to the full bill.
• Don't count rows as transactions. One bill split three ways is three rows. Count distinct reference numbers or transaction dates-plus-names if you need a transaction count.

Trap 4: signs and account types

In a transaction detail report, the sign of the amount depends on the account and the transaction type. Expenses usually show as positive under expense accounts, but credits, refunds from vendors, and reversing journal entries show as negatives in the same column. That's correct — they reduce spend — so keep them in the sum rather than filtering them out. The mistake is mixing income and expense accounts in one total. Filter on account (or account type, if you added that column) before summing.

Trap 5: numbers stored as text

Depending on your Excel settings, amounts can arrive with thousands separators or in accounting format, and dates can come through as text. Check with a quick =SUM on the amount column: if it returns 0, the numbers are text. Data → Text to Columns → Finish on the column usually converts them in one step.

The questions worth asking

Once you have a flat file with Date, Name, Account, Memo and Amount, the useful questions are all group-and-filter:

1. Spend by account, this year vs last year. The first table any owner wants. Sort by the change, not the total.
2. Top vendors by spend, and each vendor's share of total spend. The top ten usually tell you where negotiation is worth it.
3. Month-over-month spend by account. A line chart per category shows creep that a single annual number hides.
4. New vendors this quarter — names with no transactions before the quarter started. Useful for catching subscriptions nobody approved.
5. Vendors whose spend grew fastest — this quarter vs the same quarter last year.
6. Anomalies — transactions more than, say, three times the usual amount for that vendor. Duplicated bills (same vendor, same amount, same week) are worth a separate check; see finding duplicates in Excel.
7. Uncategorized lines — anything in Uncategorized Expense or Ask My Accountant. Clean these up before trusting any category total.
8. Memo text search — every line whose memo mentions a project, client or keyword, summed.

In Excel these are pivot tables and a few SUMIFS — the pivot table tutorial covers the mechanics.

Asking in plain English instead

If you upload the flat export to ChatExcel, you can skip the pivot-building and ask the questions directly:

• "Total spend by account for Q2, largest first, and chart the top five."
• "Which vendors did we pay for the first time this quarter?"
• "Show every transaction that is more than three times that vendor's average amount."

If you couldn't remove the grouping before exporting, say so in the first question — "ignore rows that start with 'Total for' and rows without a date" — and the assistant applies that to the rest of the conversation. Every answer is computed from the whole file, and you can ask for the Excel formula behind any number so you can rebuild it in your own workbook.

Sanity checks before you share a number

• The total for one expense account over a month should match the same account on the Profit and Loss for that month, on the same accounting basis (cash vs accrual).
• Total spend across all expense accounts should match Total Expenses on the P&L for the period. If yours is higher, a subtotal row is still in the data.