How to Export Your QuickBooks Desktop Data to Excel Before a Migration or Audit

Whether to migrate is a separate decision. See QuickBooks Desktop vs QuickBooks Online for manufacturers and distributors, and the alternatives to QuickBooks Desktop.

Before you start

• Back up the company file first: File > Back up Company > Create Local Backup. Intuit says a backup saves everything in the company file.
• Pick a cut-off date and run every report as of that date, so the numbers agree with each other.
• Use a newer version of Excel. Intuit says Excel 2003, 2007, 2010 and 2013 limit report exports to 65,536 detail lines.
• Make a folder for the exports and name each file after the list or report it holds.

Step 1: Export customers and vendors

1. 1Open the Customer Center.
2. 2Open the Excel dropdown and choose to export the customer list.
3. 3Choose to create a new worksheet, then select Export. Excel opens with names, balances and contact details.
4. 4Repeat in the Vendor Center for the vendor list.
5. 5To export transactions instead, choose Export Transactions from the same dropdown, by name or by transaction type.

Step 2: Export items

1. 1Go to Lists > Item List.
2. 2Open the Excel dropdown and choose Export all Items.
3. 3Choose to create a new worksheet, then select Export.

Step 3: Export customer price levels

Intuit says QuickBooks has no report of customers and their price levels. Add/Edit Multiple List Entries can show them, but that view can't be printed or exported. Intuit's workaround is an IIF file.

1. 1Sign in as admin and switch to single-user mode.
2. 2Go to File > Utilities > Export > Lists to IIF Files.
3. 3Select the customer list, plus any other lists you want, then select OK and save the file.
4. 4Find the saved IIF file, right-click it, select Open with, then Excel.
5. 5Find the row with NAME in column B. Scroll right along that row to PRICE LEVEL. Each customer's level sits below it.
6. 6Delete the rows above and the columns you don't need, then save.

Intuit says price levels set up per item can't be exported or imported as IIF files. Price levels also don't move to QuickBooks Online. Our guide to customer price levels shows where they live in Desktop.

Step 4: Export open transactions and key reports

1. 1Open the report from Reports > Reports Center or the Reports menu.
2. 2Set the date to your cut-off.
3. 3Select the Excel dropdown at the top of the report, then Create New Worksheet.
4. 4If QuickBooks says the report has too many columns, select Advanced and clear Space between columns.
5. 5Select Export. Excel gets the report data, subtotal formulas, row labels, and the report headers and footers.

Reports worth exporting

• Open Invoices and A/R Aging Detail, under Reports > Customers & Receivables.
• Unpaid Bills Detail and A/P Aging Detail, under Reports > Vendors & Payables. For Unpaid Bills Detail on a past date, set Open Balance/Aging to As of Report Date in Customize Report > Advanced.
• Open Purchase Orders, under Reports > Purchases.
• Open sales orders: Intuit's workaround customizes Reports > Purchases > Open Purchase Order by Job. Set Transaction Type to Sales Order and Paid Status to Open.
• Inventory Valuation Summary and Inventory Valuation Detail, under Reports > Inventory.
• Profit and Loss and Balance Sheet, under Company & Financial, and the Trial Balance and General Ledger, under Accountant & Taxes.
• The Audit Trail report, under Accountant & Taxes, and Previous Reconciliation reports, under Banking.

Intuit says the audit trail and past reconciliation reports don't transfer to QuickBooks Online, so export them before any move.

Bills of materials

We found no Intuit help article that exports a list of bills of materials. Each assembly's components show on its item record in Lists > Item List. For components your builds used, Intuit's custom Summary report filtered to Build Assembly exports like any other report. Our guide to bill of materials cost and where-used reports has the steps.

Limits to know

• IIF exports lists only. Intuit says you can import transactions through IIF but can't export them.
• IIF files don't move between countries' versions of QuickBooks, such as US and Canadian.
• Payroll has its own Excel-based reports under Reports > Employees & Payroll.
• To refresh an exported report later, keep the company file open and select Update Report on the QuickBooks tab in Excel.

Where QuickBooks Desktop leaves the work to you

• Intuit's export steps work one list or one report at a time.
• An exported report doesn't change as people keep working in the file, unless you select Update Report in Excel.
• Someone has to check that exported totals tie back to the balance sheet on the cut-off date.
• Intuit's export steps don't cover per-item price levels or a list of bills of materials.

How Logistify fits

Logistify doesn't export or migrate data. Our post on the QuickBooks Desktop migration readiness audit covers what to check in your file before a move. For the steps of the move itself, see how to migrate QuickBooks Desktop to QuickBooks Online.