How to Upload Excel List of Customers to Quickbooks

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 dissimilar file formats.

Import Excel files

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

Option 1: Standard import

QuickBooks opens a formatted Excel spreadsheet where you lot can enter your information. When you save and close the file, you tin review results and view your information in QuickBooks. To import Customers, Vendors, and Products you sell

1. Go to theFile menu, selectUtilities thenImport and so Excel Files.
2. If you get the Add together/Edit Multiple List Entries window, click No.
3. Follow the magician in importing files.
   1. Select the blazon of data.
   2. QuickBooks opens a formatted excel spreadsheet. One time yous take entered your information, saved the file and closed it, you lot will be given the choice to Add My Data Now.
   3. Review results and view Information in QuickBooks.
   4. Select Shut when done.

Note: If y'all need to import boosted customer, vendor or detail data from excel using this pick, y'all tin can e'er go back to this window.

To import Nautical chart of Accounts

Note: Before importing, we recommend that y'all create a fill-in of your company file .

1. Go to the Lists menu, and so selectNautical chart of Accounts.
2. Select the Account drop-down at the bottom, and then choose Import from Excel.
3. Select Browse to select the Excel file you lot want to import.
4. Select the file, then select Open.
5. Select the Excel Sheet where the data you want to import is on.
6. Map your accounts.

   Mapping your accounts tell QuickBooks how to import the information from your Excel sail. The column headings in QuickBooks may be different from your Excel, then you lot'd need to friction match them.

   1. Type in a mapping name.
   2. Select Business relationship every bit Import type.
   3. Match the information nether QuickBooks with the column headings in your Excel sheet.
   4. Select Save.
7. Select Import. If information technology's your first fourth dimension to import, select Aye to confirm the process.

Option 2: Advanced import

For items

Follow the steps below if you are importing items and you exercise not accept an Excel or CSV file yet.

Reminders: Existing inventory items: you CANNOT update their Quantity On Hand (QOH), the value on hand (VOH), or average cost. New inventory items: you can set up a Quantity On Hand (QOH) and a Value on Hand (VOH) and establish an average price. QuickBooks enters an Conform Quantity/Value on Manus debiting the particular's asset account and crediting Opening Residuum Equity. If you want to import particular quantities or values and you have Advanced Inventory and Multiple Inventory Sites enabled, you must disable Multiple Inventory Sites before importing. Before importing any information, Back up the QuickBooks company file without overwriting whatever previous backups. You cannot import Subtotal, Payment, and Sales tax items via the Advanced Import function, but y'all tin can import them in an IIF file. For details, refer to Export or import IIF files .

Step ane: Turn on inventory preferences

1. Sign in to your company file as Admin.
2. Make sure you are on the Unmarried-user manner.
   Annotation: On the File menu, an option to Switch to Multi-user Style 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 get to the Company Preferences tab.
5. Select the Inventory and Purchase Orders are Active checkbox, and so select OK.

Pace 2: Set upwards accounts in your data file

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

Step 3: Create your information with the following for each particular:

• Item Proper name: Name you want on your item list.
  1. If you are importing Sub-Items, the Parent item must either already exist in the Detail 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 exist formatted with the Parent separated from the Sub-Item by a colon.

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

• Detail Type: Include the type of item, i.due east. inventory role, service, etc. Detail proper name must exist spelled out just as yous meet information technology in QuickBooks.
• Description of the Item: Written description of the detail that volition appear in the Particular List, Sales Orders, Sales Receipts, and Invoices.
• Income Business relationship: Proper name of the Income Business relationship in which sales of the item will be recorded.
• Inventory Asset Business relationship: Proper noun of the Asset Account in which the inventory value will be recorded.
• Proper noun of the COGS Account: Name of the COGS Account in which purchases of the item will be recorded.
• On Manus Quantity: The Quantity On Hand of the Item (Inventory Items only).
• Toll of Detail: The Cost of the Particular.
• 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 terminal purchased (New Inventory Items just).
• Is Passed Thru: Be sure that you put a Y in this column so you can runway both the cost and price of the item.

Important: You lot must use the aforementioned name, spelling, and capitalization equally it appears in the Nautical chart of Accounts.

Optional Information:

• Manufacturers Office Number or SKU: You lot can also import the function/SKU number of an particular.
• Reorder Signal: Once the Item On Hand count reaches this amount, QuickBooks will prompt you to reorder more Items.

Step four: Create the spreadsheet

Create the spreadsheet using these tables as guides:

Step five: Prepare to import

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

1. Starting time QuickBooks.
2. Open up your company data file.
3. Back up the QuickBooks company file without overwriting whatever previous backup.

Pace vi: Import the spreadsheet

1. Go to theFile menu, selectUtilities then Import and and then Excel Files.
2. On the Add/Edit Multiple List Entries, selectNo.
3. SelectAdvanced Import.
4. Setup a mapping.
   1. SelectScan and choose the Excel file.
   2. Choose the right canvass in the Excel workbook.
   3. Select theThis data file has header rows checkbox to identify the bachelor 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 (Client, Vendors, etc…).
   2. From the Import type dropdown, choose the information you are importing.
   3. Friction match the QuickBooks and Import Data columns, then select Salvage.
      Note: QuickBooks cavalcade displays the available customer fields in QuickBooks and Import Data cavalcade displays the available row headers on your Excel file. If you don't have data from your file that will lucifer 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 Tape Found fault, you lot volition be presented with these options:
   • Keep the existing data and discard the import information.
   • Replace the existing data with import data, ignoring bare fields.
   • Replace the existing data with import data, including bare fields.

   To fix the errors:

   1. Choose the advisable option.
   2. SelectUtilize or Apply to all.
   3. You lot will receive a notification indicating the number of successful imports and the number of errors. If you take errors, select Salvage for the error log and review it to decide the necessary action to resolve the errors.
   4. Re-import list as needed.

For customers, vendors, and other lists information

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

1. From the File menu, selectUtilities and then Import so Excel Files.
2. On the Add together/Edit Multiple List Entries, selectNo.
3. SelectAdvanced Import.
4. Gear up up a mapping.
   1. SelectBrowse and cull the Excel file.
   2. Choose the right sheet in the Excel workbook.
   3. Select theThis data file has header rows checkbox to place the available headers from your file.
   4. On the Choose a mapping dropdown, select Add together 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 information you are importing.
   3. Friction match the QuickBooks and Import Data columns, and so select Save.
      Notation: QuickBooks cavalcade displays the bachelor customer fields in QuickBooks and Import Data column displays the available row headers on your Excel file. If yous don't take data from your file that will match the fields in QuickBooks, go out it blank.
   4. SelectPreview to verify the mapping.
   5. SelectImport to complete the import.
6. If you receive the Duplicate Record Found error, you lot will be presented with these options:
   • Proceed existing data and discard import data.
   • Supersede existing information with import data, ignoring blank fields.
   • Supervene upon existing data with import data, including bare fields.

   To fix the errors:

   1. Choose the advisable option.
   2. SelectApply or Employ to all.
   3. You lot will receive a notification indicating the number of successful imports and the number of errors. If you have errors, select Relieve for the error log and review information technology to determine the necessary activity to resolve the errors.
   4. Re-import list as needed.

Option 3: Add/Edit Multiple Lists

This option allows you to add and edit multiple customers, vendors, and items. This is for listing information (proper 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 lot want to work with.
3. Filter the listing to run into a particular record.
4. (Optional) Select the Customize Columns button to choose which columns y'all want to see in the Add/Edit Multiple List Entries window. This mode, yous can easily work on the columns you lot 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 cavalcade 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 client or vendor name are required and cannot be removed. You tin simply change their order.
5. Add or edit the entries by clicking the appropriate fields. Notation that you lot can copy (Ctrl+C) data from Excel and paste (Ctrl+5) information technology into this window. Y'all can besides Re-create down and Duplicate rows (highlight any field and select Re-create Down and Duplicate Row)
6. SelectRelieve Changes.

After saving your changes, yous will receive a warning telling you the number of records saved. QuickBooks volition besides 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 fault is.
2. Update the record to ready the errors.
3. Select Save Changes and repeat the process until all errors are fixed. Note that if you don't desire to fix an error, you can save the changes you've fabricated so far and selectShut without making farther changes. QuickBooks will non salve the changes that contain errors.

Consign Excel files

You can export Customer, Vendor, Payroll lists and transactions, as well as Items list into an Excel spreadsheet. If you demand to alter a report'due south appearance or contents in ways that are non bachelor in QuickBooks, you tin can too export reports and customize information technology in Excel without affecting your QuickBooks information.

Client/Vendor/Payroll list and transactions

1. Open the Client/Vendor/Payroll middle.
2. SelectExcel driblet-downwards and choose:
   • Export Customer/Vendor/Employee listing if you want to export customer/vendor/employee data such as name, balances and contact information.
   • Consign Transactions if you want to consign transactions (either past proper name or transaction type).
3. In the Export window, choose whether to create a new worksheet or update an existing worksheet.
4. Select the Export push button. QuickBooks opens MS Excel and you lot can edit the information every bit needed.

Items

1. Go to theLists menu, select Item List.
2. On the Excel dropdown, choose Consign all Items.
3. In the Consign window, choose whether to create a new worksheet or update an existing worksheet.
4. Select the Consign button. QuickBooks opens MS Excel. You lot can edit the data as needed.

Reports

Information that QuickBooks exports to Excel include:

• Data in the study, as it is displayed on the screen
• Formula for subtotals, totals, and other calculations
• Row labels that describe the contents of each row
• Headers and footers from the QuickBooks written report

1. Open the written report you want to consign.
2. Select the Excel drop-down at the peak of the report, and so select Create New Worksheet or Update Existing Worksheet.
3. In the Transport Study to Excel window, choose what yous'd like to do with the report.
4. Select the Avant-garde push button to see other available options for the exported study and then selectOK. To ensure the exported report will show Header data

   If yous are using a newer version of MS Excel and the exported report does not show header information, go to the Printing options department and make sure the On Printed report and screen option is selected.

   

5. Select the Export push button. QuickBooks opens MS Excel. You can edit your data as needed.

moserpogmeher1984.blogspot.com

Source: https://quickbooks.intuit.com/learn-support/en-us/help-article/list-management/import-export-ms-excel-files/L9BDPsTTX_US_en_US

Share this post