Import or export MS Excel files (2024)

by Intuit•4• Updated 2 months ago

Learn how to import and export QuickBooks data with MS Excel files.

You can import and export various list and transaction types to and from QuickBooks Desktop using different file formats.

Import Excel files

You can import Customer, Vendor, Items, and Chart of Accounts from an Excel spreadsheet. There are several options available when importing Excel files from QuickBooks.

For items

Follow the steps below if you are importing items and don't have an Excel or CSV file yet:

Step 1: Turn on inventory preferences

1. Sign in to your company file as Admin.
2. Make sure you are on the Single-user mode.
   Note: On the File menu, an 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. Select the Inventory and Purchase Orders are Activecheckbox, then select OK.

Step 2: Set up accounts in your data file

1. From the Lists menu, selectChart of Accounts.
2. Right-click anywhere in the Chart of Accounts then select New.
3. SelectAccount Type. For this step, you need to create the following account types:
   • Income Account to track sales.
   • Cost Of Goods Sold (COGS) Account.*
   • Inventory Asset Account.*

Step 3: Create your data with the following for each item:

• Item Name: Name you want on your item list.
  1. If you are importing Sub-Items, the Parent item must either already exist in the Item List or be above the Sub-item, in the list order, on the spreadsheet you are importing.
  2. If you are importing Sub-Items, the Item Name would be formatted with the Parent separated from the Sub-Item by a colon.

     Example:
     Widgets
     Widgets:Widget001
     Widgets:Widget002
     Widgets:Widget003

• Item Type: Include the type of item, i.e. inventory part, service, etc. Item name must be spelled out just as you see it in QuickBooks.
• Description of the Item: Written description of the item that will appear in the Item List, Sales Orders, Sales Receipts, and Invoices.
• Income Account: Name of the Income Account in which sales of the item will be recorded.
• Inventory Asset Account: Name of the Asset Account in which the inventory value will be recorded.
• Name of the COGS Account: Name of the COGS Account in which purchases of the item will be recorded.
• On Hand Quantity: The Quantity On Hand of the Item (Inventory Items only).
• Cost of Item: The Cost of the Item.
• Sales Price: Sales Price of the Item.
• Total Value: The Total Value of the Item (New Inventory Items only).
• As Of Date: When the Item was last purchased (New Inventory Items only).
• Is Passed Thru: Be sure that you put a Y in this column so you can track both the cost and price of the item.

IMPORTANT: You must use the same name, spelling, and capitalization as it appears in the Chart of Accounts.

Optional Information:

• Manufacturers Part Number or SKU: You can also import the part/SKU number of an item.
• Reorder Point: Once the Item On Hand count reaches this amount, QuickBooks will prompt you to reorder more Items.

Step 4: Create the spreadsheet

Create the spreadsheet using these tables as guides:

Step 5: Prepare to import

Make sure you know the location of the Excel spreadsheet file. QuickBooks will sync with the spreadsheet during the process.

1. Start QuickBooks.
2. Open your company data file.
3. Back up the QuickBooks company file without overwriting any previous backup.

Step 6: Import the spreadsheet

1. Go to theFile menu, selectUtilities then Import and then Excel Files.
2. On the Add/Edit Multiple List Entries, selectNo.
3. SelectAdvanced Import.
4. Setup a mapping.
   1. SelectBrowse and choose the Excel file.
   2. Choose the correct sheet in the Excel workbook.
   3. Select theThis data file has header rows checkbox to identify the available headers from your file.
   4. On theChoose a mapping dropdown, select Add New.
5. On the Mappings window:
   1. On the mapping name field, type a name to easily identify the mapping (Customer, Vendors, etc…).
   2. From the Import type dropdown, choose the data you are importing.
   3. Match the QuickBooks and Import Data columns, then select Save.
      Note: 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. SelectPreview to verify the mapping.
   5. SelectImport to complete the import.
6. If you receive the Duplicate Record Found error, you will be presented with these options:
   • Keep the existing data and discard the import data.
   • Replace the existing data with import data, ignoring blank fields.
   • Replace the existing data with import data, including blank fields.

   To fix the errors:

   1. Choose the appropriate option.
   2. SelectApply 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 the necessary action to resolve the errors.
   4. Re-import list as needed.

For customers, vendors, and other lists data

Use this option if you are importing Customers, Vendors, Items and other lists data and you have an Excel file or CSV data already formatted for QuickBooks.

1. From the File menu, selectUtilities then Import and then Excel Files.
2. On the Add/Edit Multiple List Entries, selectNo.
3. SelectAdvanced Import.
4. Set up a mapping.
   1. SelectBrowse and choose the Excel file.
   2. Choose the correct sheet in the Excel workbook.
   3. Select theThis data file has header rows checkbox to identify the available headers from your file.
   4. On the Choose a mapping dropdown, select Add New.
5. On the Mappings window:
   1. On the mapping name field, type a name to easily identify the mapping (Customer, Vendors, etc…)
   2. From the Import type dropdown, choose the data you are importing.
   3. Match the QuickBooks and Import Data columns, then select Save.
      Note: 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. SelectPreview to verify the mapping.
   5. SelectImport 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. SelectApply 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 the necessary action to resolve the errors.
   4. Re-import list as needed.

This option allows you to add and edit multiple customers, vendors, and items. This is for list information (name, description, etc.) and cannot be used to import transactions (invoices, bills, etc…).

1. From the Lists menu, select Add/Edit Multiple List Entries.
2. Select the List dropdown and choose the list you want to work with.
3. Filter the list to see a particular record.
4. (Optional) Select the Customize Columns button to choose which columns you want to see in the Add/Edit Multiple List Entries window. This way, you can easily work on the columns you need. Select the Default button to return to the preset columns for the list.
   1. To add: from the left pane, select a column and selectAdd.
   2. To remove: from the right pane, select a column and selectRemove.
   3. To rearrange the column order: from the right pane, select the column and selectMove Up or Move Down.
   4. SelectOk.
      Note: Columns like customer or vendor name are required and cannot be removed. You can only change their order.
5. 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 select Copy Down and Duplicate Row)
6. SelectSave Changes.

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. Click 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 selectClose without making further changes. QuickBooks will not save the changes that contain errors.

Export Excel files

You can export Customer, Vendor, Payroll lists and transactions, as well as Items list into an Excel spreadsheet. If you need to change a report’s appearance or contents in ways that are not available in QuickBooks, you can also export reports and customize it in Excel without affecting your QuickBooks data.