Move lists and balances from QuickBooks Online to QuickBooks Desktop

By QuickBook ExpertsOther Questions, Quickbooks, Quickbooks Mobile, Quickbooks Online

Learn how to move your customers, vendors, products, and services, and accounts from QuickBooks Online to QuickBooks Desktop or you can say Move lists and balances from QuickBooks Online to QuickBooks Desktop

Contents
Move your accounts, customers, vendors, and opening balancesStep 1: Export account, customer, and vendor list reports to ExcelColumn order for each list reportStep 2: Add your customer and vendor open balancesStep 3: Map QuickBooks fields and importMove your products and servicesStep 1: Export your products and services report to ExcelStep 2: Add your sub-items (optional)Step 3: Turn on inventory preferencesStep 4: Set up inventory tracking accountsStep 5: Import inventoryStep 6: Adjust your inventory items’ value and quantityMore Topics: How to Print 1099 in QuickBooks OnlineQUICKBOOKS ONLINE DOWN, NOT WORKING IN 2020?

Move lists and balances from QuickBooks Online to QuickBooks Desktop

If you need a hand moving from QuickBooks Online to Desktop, we’re here to help. Here’s how to move your lists and balances to your new desktop file, so you can spend less time on data entry. We’ll also show you how to transfer the open balances of customers, vendors, and accounts to your desktop file.

Already canceled your QuickBooks Online? Don’t worry. You still have access to your data for a year—plenty of time to move everything you need.

Note: Before you start, reach out to your accountant. Make sure this change is right for you. Don't have an accountant? We can help you find one.

Move your accounts, customers, vendors, and opening balances

Keep in mind that this won’t bring over transactions associated with your customers, vendors, or accounts, just their current open balances in QuickBooks Online. If you want to move your transactions too, export all of your data.

Step 1: Export account, customer, and vendor list reports to Excel

  1. Sign in to QuickBooks Online as an admin.
  2. Go to the Reports menu.
  3. Search for and open an Account List, Customer Contact List, or Vendor Contact List.
  4. On the report, select Customize.
  5. Select Rows and Columns and then Change columns.
  6. Select and reorder columns. Check the table below to learn what columns to select and their correct order:

Column order for each list report

 Account List Customer Contact List Vendor Contact List
  • Account
  • Type
  • Detail Type
  • Balance
  • Customer
  • Company Name
  • Email
  • Phone Numbers
  • Billing Street
  • City
  • State
  • ZIP
  • Country
  • Vendor
  • Company Name
  • Email
  • Phone Numbers
  • Street
  • City
  • State
  • ZIP
  • Country

When you’re done, select Run report. Then follow the steps to export the report, and save it in a folder you can quickly access to Move lists and balances from QuickBooks Online to QuickBooks Desktop.

Step 2: Add your customer and vendor open balances

  1. Go to the Sales menu and select the Customers tab. Or go to the Expenses menu and select the Vendors tab.
  2. Select the Export to Excel icon. Save the Excel file in a folder you can access quickly.
  3. To get the open balances, open the Excel file you just saved, and then copy the Open Balance column.
  4. Open the Excel file of customers or vendors that you saved in Step 1.
  5. You don’t need the first 4 rows, so be sure to remove them from the worksheet.
  6. Add the Open Balance column you copied, then save your changes.

Step 3: Map QuickBooks fields and import

  1. Sign in to your company file in QuickBooks Desktop as an admin.
  2. From the File menu, select Utilities, then Import.
  3. Select Excel Files. On the window that appears, select No.
  4. Select Advanced Import.
  5. Select Browse… and go to the Excel file of account, customer, or vendor list you need to import.
  6. Select the Choose a mapping ▼ drop-down, then Add New.
  7. Add a mapping name, depending on the list you’re importing. For example, add “Account List.”
  8. Select an import type, and then start matching QuickBooks fields with your worksheet headers. Select Save once you’re done. Note: For customer list, check this mapping sample to map the billing address details correctly.
  9. Select Preview to make sure there are no mapping errors.
  10. In the error handling section, select what you’d like to happen if QuickBooks finds errors:
    • Import rows with errors and leave error fields blank
    • Do not import rows with errors
  11. Select OK. Once you’re ready, select Import.

QuickBooks lets you know if the import is successful, or if there’s any error.

If QuickBooks finds an error, save the error log. Then open it to see how to fix the errors.

 If you received a “Duplicate Record Found” error QuickBooks will let you choose what you want to happen with the duplicates. You can:

  • Keep existing data and discard import data.
  • Replace existing data with import data, ignoring blank fields.
  • Replace existing data with import data, including blank fields.

Move your products and services

Step 1: Export your products and services report to Excel

Export your list of products and services to Excel. Then make sure each item type has its own worksheet in the Excel file.

  1. Sign in to QuickBooks Online as an admin.
  2. Go to Settings ⚙ and select Products and Services.
  3. Select the Export to Excel icon. Save the Excel file in a folder you can access quickly.
  4. Browse to the folder where you saved the file, then open it.
  5. Go to the Type column header, and then filter it to only “Services,” “Inventory,” or “Non-inventory.”
  6. Copy all of the data.
  7. Add a new worksheet. Rename the worksheet after the item type so it’s easier to transfer them later. For example, “Inventory items.”
  8. Paste the data you copied into the new worksheet.
  9. Check the “Column orders for each item type” table below and follow the correct column order for each item type.
  10. Follow the same steps for all your item types. Then save your changes.

Column order for each item type

ServicesInventoryNon-inventory
  • Product/Service Name
  • Sub Item of (Note: Add this if you have sub-items in QuickBooks Online)
  • Sales Description
  • Sales Price
  • Income Account
  • Purchase Description
  • Purchase Cost
  • Expense Account
  • Product/Service Name
  • Sub Item of (Note: Add this if you have sub-items in QuickBooks Online)
  • Sales Description
  • SKU
  • Sales Price
  • Income Account
  • Purchase Description
  • Purchase Cost
  • Expense Account
  • Quantity On Hand
  • Inventory Asset Account
  • Quantity as-of Date
  • Reorder Points (if you set reorder points in QuickBooks Online)
  • Product/Service Name
  • Sub Item of (Note: Add this if you have sub-items in QuickBooks Online)
  • Sales Description
  • SKU
  • Sales Price/Rate
  • Income Account
  • Purchase Description
  • Purchase Cost
  • Expense Account

Step 2: Add your sub-items (optional)

All your items, including your sub-items and their parent items (or categories), are in Product/Service Name column. Use these steps if you also need to move your sub-items to your desktop file.

  1. Add a “Sub Item of” column right after the Product/Service Name column, like this:
    Product/Service NameSub Item of
  2. Find your sub-items in the Product/Service Name column. They look like this: Landscaping: Trimming
  3. Arrange your sub-items into something like our sample below. Make sure sub-items are under their parent items or categories, to keep everything neat and organized.
    Product/Service NameSub Item of
     Landscaping
     Trimming Landscaping
     Gardening Landscaping

Step 3: Turn on inventory preferences

  1. Sign in to your company in QuickBooks Desktop as an admin.
  2. Make sure you’re on single-user mode. From the File menu, select Switch to Single-User Mode. If what you see is “Switch to Multi-user,” you’re already on single user.
  3. From the Edit menu, select Preferences.
  4. Select Items Inventory, and then select Company Preferences.
  5. Turn on Inventory and Purchase are Active.
  6. Select OK.

Step 4: Set up inventory tracking accounts

If you haven’t yet, set up your inventory accounts in your desktop file.

  1. From the Lists menu, select Chart of Accounts.
  2. Right-click anywhere, and then select New.
  3. Select an account type. Make sure to create one account for each of these types:
    • Income Account (for your sales)
    • Cost of Goods Sold
    • Inventory Asset

Step 5: Import inventory

  1. Open the Excel file of products and services that you prepared in Step 1.
  2. Follow the steps on adding multiple list items. You can customize the columns on the Add/Edit List window. This will help you focus on just the columns you want to work on.

Step 6: Adjust your inventory items’ value and quantity

After importing, add your items’ current value and quantity to Move lists and balances from QuickBooks Online to QuickBooks Desktop

More Topics: How to Print 1099 in QuickBooks Online

QUICKBOOKS ONLINE DOWN, NOT WORKING IN 2020?