How to turn a QuickBooks® Desktop general ledger export into an Excel PivotTable

Step 1: Export the report with the right settings

1. In QuickBooks Desktop, go to Reports › Accountant & Taxes › Transaction Detail by Account. A memorized report built on it works too.
2. Pick the date range. Year to date is fine.
3. Customize Report › Display: tick Type, Date, Num, Name, Memo, Debit and Credit. Debit and Credit need to be separate columns rather than a single Amount column. Balance is optional.
4. Customize Report › Header/Footer: keep Company Name and Report Title ticked.
5. Leave the accounts indented the way QuickBooks shows them. That indentation is what tells you the account levels.
6. Click Excel › Create New Worksheet and choose in new workbook. Under Advanced, untick Send header to Page Setup in Excel, so the company name and report title stay in cells A1 and A2.
7. Save the workbook as Excel Workbook (.xlsx).

Step 2: Find the heading row and the account label columns

Open the export and look at its layout before you write any formulas. In the example used on this page it looks like this:

Your file may not match letter for letter. QuickBooks sometimes leaves narrow empty columns between the headings, and a chart of accounts with fewer levels uses fewer label columns. Note your own heading row and column letters, and adjust the formulas in the next step to match.

Step 3: Add helper columns that carry each account down

In the first empty columns to the right of the report (M onward in the example), type these headings in row 4 and these formulas in row 5. Then copy the formulas down to the last row of the report.

How they work:

• Category repeats the last label found in column B, and clears when it reaches that account’s Total row.
• Sub-account does the same for column C, and starts again whenever the category changes, so a sub-account never leaks into the next category. Sub-sub-account does the same for column D, one level down.
• Account is the deepest level the line actually sits in. A line posted straight to a parent account gets the parent’s name, so nothing is lost.
• Net Amount is credit less debit, so income comes out positive and expenses negative. Use =J5-K5 instead if you’d rather see debits as positive.

If your accounts only go two levels deep, leave out Sub-sub-account and use =IF(N5<>"",N5,M5) for Account. If they go four deep, add one more column in the same pattern.

Step 4: Keep only the transaction rows

1. Select row 4 and turn on Data › Filter.
2. Open the filter arrow on Type and untick (Blanks). The letterhead, the account headings, every Total row and the grand TOTAL have an empty Type, so only real transactions stay visible.
3. Select the visible rows from the heading row down and copy them. Excel copies only the rows the filter shows.
4. On a new sheet, click A1 and use Home › Paste › Paste Values, so the formulas become fixed text and numbers.
5. Delete the old label columns (A–D in the example) and the Balance column. Balance is a running balance, so adding it up in a pivot gives a meaningless number.

Step 5: Check that dates and amounts are real dates and numbers

Excel exports from QuickBooks Desktop usually arrive with real dates and numbers, but check before you pivot. Dates and numbers line up on the right of the cell by default, and text lines up on the left. If a column has come through as text, select it and run Data › Text to Columns › Finish to convert it in place. Blank Debit or Credit cells are fine.

Step 6: Turn the range into an Excel table

Click anywhere in the data, press Ctrl+T and confirm My table has headers. Then, on the Table Design tab, change the table name to something like Transactions. A table grows when you paste more rows under it, and a PivotTable built on it picks up the new rows when refreshed.

Step 7: Insert the PivotTable and check it against QuickBooks

1. With a cell in the table selected, choose Insert › PivotTable and put it on a new worksheet.
2. Drag Category and then Account into Rows. Add Name under them if you want to see payees.
3. Drag Debit, Credit and Net Amount into Values.
4. Drag Type into Filters, so you can look at just invoices or just cheques.
5. If a value shows as “Count of…”, click it, choose Value Field Settings and pick Sum. Excel picks Count when a column contains blanks or text.

Check the result

Before you rely on the pivot, compare it with the original export. For each account, the pivot’s Debit and Credit should equal that account’s Total row in the QuickBooks report. If an account is out, the usual causes are a label column copied down too far or not far enough, or a filter that let a Total row through.