cancel
Showing results for 
Search instead for 
Did you mean: 
Intuit

Move lists and balances from QuickBooks Online to QuickBooks Desktop

Starting your books fresh can sometimes be the best way to clean them up. You can do this yourself, but we highly recommend talking to your accountant before you do this to make sure this is the right step.

Note: You will have read-only access to your QuickBooks Online account for 1 year. You can reference it as you transition to QuickBooks Desktop. During that year, you'll also be able to export reports and lists for audit or tax reasons.

Export lists, balances, and products/service from QuickBooks Online

What to Expect
  • This section includes instructions for exporting Account List, Customer list, Vendor list, and Products and Services List and moving them into QuickBooks Desktop.
  • This method does NOT bring over transactions other than a beginning balance transaction to account for Accounts Receivable and Accounts Payable balances tied to customer and vendor list items.
  • Inventory adjustments will be entered to establish asset value based on the quantity on hand entered when importing inventory items.
  • This is a multi-step process that will require attention to detail and time based on the number of list items being moved over.
  • We recommend talking to your accountant when working through this process.

Export lists

You can export your lists with balances by following these steps:

    1. Open your QBO company.
    2. Run Account List report.
      1. Select Reports from the left menu.
      2. In the Search field, type Account and select Account List.User-added image
      3. Select Customize.
      4. Select Rows/Columns and Change Column
      5. Select and order the available columns in the order seen below.User-added image
      6. Verify columns selected and Run report
      7. On the upper right, select Export to Excel.User-added image
      8. Save the Excel file to your computer.
    3. Run the Customer Contact List report.
      1. Select Reports from the left menu.
      2. In the Search field, type Customer Contact List.
      3. Select Customize.
      4. Select Rows/Columns and Change Columns.
      5. Select and order the available columns in the order seen belowUser-added image
      6. Verify columns selected and Run report.
      7. On the upper right, select Export to Excel.
      8. Save the Excel file to your computer.
    4. Run the Vendor Contact List report.
      1. Select Reports from the left menu.
      2. In the Search field, type Vendor Contact List.
      3. Select Customize.
      4. Select Rows/Columns and Change Columns.
      5. Select and order the available columns in the following order:
        • Vendor
        • Company Name
        • Email address
        • Phone numbers
        • Street
        • City
        • State
        • Zip
        • Country
      6. Verify columns selected and Run report.
      7. On the upper right, select Export to Excel
      8. Delete rows 1-4 and Save.

Add Open AR/AP Balance to your Customer Contact List & Vendor Contact List

  1. From the left menu, select Sales then Customers.
  2. Select Export to Excel.
    User-added image
  3. Copy contents of column L, the Open Balance column.  Click "L", then right click and select "Copy".
    User-added image
  4. Open the Customer Contact List Excel file saved earlier.
  5. Add column to the Excel file and name it Open Balance (column H).
  6. Paste the Open Balance data/figures from step 3 to column H in the Customer Contact List Excel file.
  7. Save the Excel file.
  8. Run through steps 1-7 again for Vendors (Select Expenses then Vendors).

Export Products and Services

  1. Select the Gear icon at the top.
  2. Under Lists, choose Products and Services.
  3. Select the Export to Excel icon at the top right of the item table.
  4. Save the Excel file to your computer.
  5. Prepare the Excel file for import.

Service Items

  1. Filter the Master Product and Service Excel file from QBO for Services.
    1. Go to the Type column and filter it to only Services.
    2. Copy all of the data, then select File > New and create a new worksheet.
    3. Paste the Services data on this worksheet.
  2. Arrange the columns in this order and delete columns that are not in this list:
    • Product/Service Name
    • Sub Item of (column must be added if sub-items are used in QBO)
    • Sales Description
    • Sales Price
    • Income Account
    • Purchase Description
    • Purchase Cost
    • Expense Account

    Note: If you have sub-items or categories included in your Products and Services list, the item name will need to be edited.

    • If you are importing Sub-Items, the Parent item must either already exist in the Item List or be above the Sub-item, in list order, on the spreadsheet you are importing.
    • The item name must have everything after the colon moved into its own column. Example: Landscaping:Trimming
      Item Name Sub Item of
      Trimming Landscaping
      Gardening Landscaping
  3. Save As: example: Services prepped for import
  4. In the next three sections, you'll separate your services, inventory, and non-inventory items into three separate workbooks.

Inventory Items

  1. Filter the Master Product and Services Excel file from QBO for Inventory
    1. Go to the Type column and filter it to only Inventory
    2. Copy all of the data, then select File > New and create a new worksheet.
    3. Paste the Inventory data on this worksheet.
  2. Arrange the columns in this order and delete columns that are not in this list:
    • Product/Service Name
    • Sub Item of (column must be added if sub-items are used in QBO)
    • 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 used in QBO)

    Note: If you have sub-items or categories included in your Products and Services list, the item name will need to be edited.

    • If you are importing Sub-Items, the Parent item must either already exist in the Item List or be above the Sub-item, in list order, on the spreadsheet you are importing.
    • The item name must have everything after the colon moved into its own column. Example: Landscaping:Flowers
      Item Name Sub Item of
      Flowers Landscaping
      Shrubs Landscaping
  3. Save As: example: Inventory prepped for import

Non-inventory Items

  1. Filter the Product and Services Excel file from QBO for Non-Inventory
    1. Go to the Type column and filter it to only Non-Inventory.
    2. Copy all of the data, then select File > New and create a new worksheet.
    3. Paste the Non-Inventory data on this worksheet
  2. Arrange the columns in this order and delete columns that are not in this list:
    • Product/Service Name
    • Sub Item of (column must be added if sub-items are used in QBO)
    • Sales Description
    • SKU
    • Sales Price / Rate
    • Income Account
    • Purchase Description
    • Purchase Cost
    • Expense Account
  3. Save As: example: Non-Inventory prepped for import

At this point you should now have these Excel files saved to your computer:

  • Account List with Open Balance
  • Customer Contact List with Open Balance
  • Vendor Contact List with Open Balance
  • Products and Services List
    • Services prepped for import
    • Inventory prepped for import
    • Non-inventory prepped for import

You can now sign out of QuickBooks Online and open QuickBooks Desktop.

Import Excel files into QuickBooks Desktop

Advanced Import

Use this option if you are importing Customers, Vendors,  and Accounts list data and you have an Excel file for each type of list.

  1. From the QuickBooks File menu, select Utilities then Import > Excel Files.
  2. On the Add/Edit Multiple List Entries, select No.
  3. Select Advanced Import.User-added image
  4. Set up a mapping.
    1. Select Browse then choose the Account List Excel file saved earlier.
    2. Choose the correct sheet in the Excel workbook. (example: Account List)
    3. On the Choose a mapping drop-down, choose Add New.
  5. On the Mappings window:
    1. On the mapping name field, type a name to easily identify the mapping (example: Account List)
    2. From the Import type drop-down, choose the data you are importing (example: Account).
    3. Match the QuickBooks and Import Data columns, then select Save.User-added imageNote: QuickBooks column displays the available customer fields in QuickBooks and Import Data column displays the available row headers on your Excel file. If you don’t have data from your file that will match the fields in QuickBooks, leave it blank.
    4. Select Preview to verify the mapping.
    5. Select Import to complete the import.
  6. If you receive the Duplicate Record Found error, you will be presented with these options:
    • 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.
      To fix the errors:
      1. Choose the appropriate option.
      2. Click Apply or Apply to all.
      3. You will receive a notification indicating the number of successful imports and the number of errors. If you have errors, select Save for the error log and review it to determine necessary action to resolve the errors.
      4. Re-import list as needed.
      5. Follow the same steps to bring over your Customer List, Vendor List, and Products and Services List (Items in QuickBooks Desktop).

      Mapping for Customer List

      User-added image

      Mapping for Vendor List

      User-added image

Add/edit multiple lists

Use this option to move Product and Services to Items list in QuickBooks Desktop. This option allows you to add and edit multiple items at once. This is for list information (name, description, etc.) and cannot be used to import transactions (invoices, bills, etc…).

  1. Before you begin, some preparations are needed in your QuickBooks Desktop company.
    1. Turn on inventory preferences.
      1. Sign in to your company file as Admin.
      2. Make sure you are on the Single-user mode. (On the File menu, option to Switch to Multi-user Mode should be available. If not, select Switch to Single-user Mode).
      3. From the QuickBooks Edit menu, select Preferences.
      4. Select Items & Inventory on the left pane, then go to the Company Preferences tab.
      5. Put a check mark on the Inventory and Purchase Orders are Active box, then select OK.
    2. Set up accounts in your data file.
      1. From the Lists menu, select Chart of Accounts.
      2. Right click anywhere in the Chart of Accounts then select New.
      3. Select the Account Type. For this step, you need to create the following account types if not already present:
        • Income Account to track sales
        • Cost Of Goods Sold (COGS) Account
        • Inventory Asset Account
  2. Add multiple entries.
    1. From the Lists menu, select Add/Edit Multiple List Entries.
    2. Select the List drop-down then choose the list you want to work with.
    3. Filter the list to see a particular record.
    4. Select the Customize Columns button to choose which columns you want to see in the Add/Edit Multiple List Entries window. Reference the Excel file saved earlier: Services prepped for import.User-added image
      • To add: From the left pane, select a column then Add.
      • To remove: From the right pane, select a column then Remove.
      • To rearrange the column order: From the right pane, select the column then Move Up or Move Down.
    5. Click Ok.
    6. Add or edit the entries by clicking the appropriate fields. Note that you can copy (Ctrl+C) data from Excel and paste (Ctrl+V) it into this window. You can also Copy down and Duplicate rows (highlight any field and click Copy Down and Duplicate Row). Reference the Excel file saved earlier: Services prepped for import.
    7. Select Save Changes.
    8. After saving your changes, you will receive a warning telling you the number of records saved. QuickBooks will also tell you if there are errors that need to be fixed.
      To fix the errors:
      1. Select or hover your mouse in any field to see what the error is.
      2. Update the record to fix the errors.
      3. Select Save Changes and repeat the process until all errors are fixed. Note that If you don’t want to fix an error, you can save the changes you’ve made so far and select Close without making further changes. QuickBooks will not save the changes that contain errors.
      4. Repeat these steps to bring in Inventory Items and Non-inventory Items by referencing both the Inventory prepped for import, and Non-inventory prepped for import files.

Related Task

Import or Export MS Excel file in QuickBooks Desktop