How to Import Item List into QuickBooks Desktop?

QuickBooks Desktop lets you transfer the data from and in accounting software. It makes your procedure easy for creating, upgrading, or converting a new company file. Users can import certain types of data such as general journal entries, bank transactions, accountant’s changes, and transactions in bulk. If required, vendors and customers can also be imported. If you are willing to know how to import item list into QuickBooks Desktop, read this guide till the end.

What All Lists Can Be Imported in QuickBooks Desktop?

Before learning how to import item list into QuickBooks Desktop, one should know what all types of lists QuickBooks allows you to import. The following are the list you can import easily in QuickBooks:

Employees Chart of Accounts Vendor types Shipping methods Items

To dos Sales tax codes Notes Customers Classes

Other names Payment terms Sales reps Vendors Customer messages

Customer and job types Payment methods Budgets Price levels customer: Jobs

All the aforementioned lists can be imported in QuickBooks using different file formats. If required, you can import the transactions as well.

How to Import Item List into QuickBooks Desktop using MS Excel?

By importing the item list, you can bring all the data related to items at once in your accounting records. But before that, you should be aware of a few important things about importing item lists in QuickBooks. Let’s see what they are.

Important Points to Consider When You Import Item List

• Make sure you create a backup of the data. So if anything messes up, you can restore the data because you cannot reverse the information you import in QuickBooks.
• If you are unable to import the item list, you should try some third-party application that makes your task even easier. Check the Intuit Marketplace to get the app.
• Before you import the item list, make sure you consult with your accountant about this.

Import Item List into QuickBooks Desktop with Excel

To import the item list, you can opt for different options such as standard import, advanced import, and edit the multiple lists manually. Here, we will show you how to import item list into QuickBooks Desktop using an MS Excel file in all the ways:

Method 1: Standard Import

To use standard import, QuickBooks will show a formatted Excel spreadsheet. In this sheet, you can enter the details. After saving the file, you can view the data and results in QuickBooks. If you want to import items, use the following steps:

• Navigate to the “File” menu.
• Then, click on the “Utilities” option.
• Now, press the “Import” button.
• After this, choose the “Excel Files” option.

• When you see the “Add/Edit Multiple List Entries” screen, click the “No” button to continue.
• At this stage, you will have to follow a wizard to import files:
  • Choose the type of data you want to import.
  • When QuickBooks Desktop opens the formatted excel spreadsheet, you can continue to enter the details. Then, save and close the file. As you do so, your screen shows an option “Add My Data Now” on which you need to click.
  • Once done, you can click on the “Data” and review the results in your account.
  • Press the “Close” button.

By performing these steps, you can add the item list in QuickBooks Desktop, If required you can import more item data from the excel sheet.

Method 2: Advanced Import

The Advanced Import method can be used to import items when you don’t have the CSV or Excel files. However, you need to keep a few important points into consideration which are as follows:

• For New Inventory Items:
  • Users can set up the VOH (Value on Hand), QOH (Quantity On Hand), and set up the average cost.
  • QuickBooks will add the Value of Hand/Adjust Quantity by crediting the Opening Balance Equity and debiting the asset of the item account.
• For Existing Inventory Items:
  • Users cannot update the VOH (Value on Hand), QOH (Quantity On Hand), and the average cost.
• If you are planning to import item values or quantities with the “Multiple Inventory Sites” and “Advanced Inventory” options enabled then you need to disable the “Multiple Inventory Sites” option before you import.
• Do not import “Payment”, “Subtotal”, and “Sales Tax Items” through the “Advanced Import” option. However, you can import them using the “IIF file”.
• Before you import any data, make sure you create a backup of your company files without and ensure you don’t overwrite the previous backups.

To use the Advanced Import method, you are required to enable the Inventory Preferences in your QuickBooks account. After this, you will have to set up the account in the data file and create the data for each item. Once done, prepare the spreadsheet, take a backup, and import the spreadsheet. Let’s understand each step with a detailed description.

Step 1: Enable the Inventory Preferences

• Make sure you are logged in to the company file as an administrator.
• Then, switch to the “Single-User Mode” by following these steps:
  • Go to the “File” menu.
  • Here, you will see the “Switch to Multi-user Mode”. Click on it to turn it to the “Switch to Single-User Mode” option.
• After this, click on the “Edit” menu in QuickBooks.
• Now, go to the “Preferences” option.
• Here, you can click on the “Items & Inventory” option located on the left.
• Then, switch to the “Company Preferences” tab.
• At this point, you need to place a checkmark for “Inventory and Purchase Orders are Active”.
• Select the “OK” button.

Step 2: Set Up the Accounts in Data File

• Go to the “Lists” menu.
• Then, click on the “Chart of Accounts” option.
• Once done, click right anywhere on the “Chart of Accounts” area and choose the “New” button.
• Click on the “Account Type” option. To complete this step, you will have to create these account types:
  • Cost Of Goods Sold (COGS) Account.*
  • Income Account to track sales.
  • Inventory Asset Account.*

Step 3: Create the Data for Each Item

• Item Name: Add the item names that you wish to add to the item list. For this, you can perform the following steps:
  • If you import the “Sub-Items” then the “Parent item” should either be on the top of your sub-item (in the list order on your spreadsheet) or it must exist in your item list already.
  • In addition, if you import the “Sub-Items” then it will format the “Item Name” with “Parent” which will be separated by a colon from your “Sub-Item”.
• Item Type: Then, add the item types such as service and inventory services. Please note, the spelling of the item name should be the same as you view in QuickBooks.
• Description of the Item: It is also important to write about the item that will be displayed in the Item List, Sales Receipts, Invoices, and Sales Receipts.
• Income Account: Make sure you add the name of your income account. This account will record all the sales of the items.
• Inventory Asset Account: Add the name of your asset account. This will help you in recording the inventory values.
• Name of the COGS Account: Here, you can enter the COGS account name that records the purchases of the items.
• On Hand Quantity. This is for the “Inventory Items” only. Here, you can add the quantity on hand of your items.
• Cost of Item: Type the correct cost of the items.
• Sales Price: Here, you can enter the sale price of your items.
• Total Value: This is for the “New Inventory Items” only. Make sure you enter the total value of your items.
• As Of Date: When your items were last purchased, you need to add that date in this field. This option is for “New Inventory Items” only.
• Is Passed Thru: In this section, you need to add “Y” so that it can help you to track both the price and cost of the item.

Note: Make sure you enter the same spelling, name, and capitalization as it displays in the QuickBooks Chart of Accounts.

Step 4: Prepare the Spreadsheet

• This is the most important part before the final import. Here, you need to prepare the spreadsheet that you will import into QuickBooks Desktop. All you have to use the following columns with the appropriate details in your sheet:
  • Columns in Excel
  • Columns in QuickBooks Import Screen
  • Required
  • Description
  • Cell Input in Excel

Step 5: Start Importing the Excel Spreadsheet

• QuickBooks will automatically sync with your spreadsheet during the import process. Make sure you are aware of the location of your “Excel Spreadsheet” file.
• Then, take a backup of your QuickBooks company file. Also, ensure the following steps while taking the backup:
  • Open “QuickBooks”.
  • Launch the company file.
  • Then, take a backup of your company files in QuickBooks without overwriting the old backups.

Step 6: Import the Excel Spreadsheet

• To get started, click on the “File” menu.
• Then, click on the “Utilities” menu.
• Choose the “Import” button.
• Press the “Excel Files” option.
• When you see the “Add/Edit Multiple List Entries” window, click on the “NO” button.
• Here, select the “Advanced Import” option.
• At this point, you have to prepare the mapping of your spreadsheet. For that, you can do the following:
  • Click on the “Browse” button.
  • Select the “Excel” file button and pick the sheet you want to import.
  • Then, click on the “This data file has header rows” option. This will identify the headers from the file.
  • Once done, click on the “Choose a mapping” drop-down arrow and click the “Add New” button.
• When you are on the “Mappings” screen, perform the following steps:
  • Enter the name on the mapping name section so that you can identify the mapping.
  • Then, select the data you want to import. For this, you can use the “Import Type” drop-down menu.
  • Now, you can match the columns of “QuickBooks” and “Import Data”.
  • Press the “Save” button.
  • Now, click on the “Preview” button to verify the mapping.
  • After this, choose the “Import” button to finish the import.

Note: In the QuickBooks column, QuickBooks will show the available customer fields and the Import Data column will show the available row headers on the Excel file. In case you don’t have the data from the file to match the fields then you can leave this column blank.

• While performing these steps, if you detect any “Duplicate Record Found” error then you will see the following options:
  • Keep your existing data and end the import data.
  • Replace your existing data with the import data and ignore or include the blank fields.
• To fix this duplicate data error, you can do the following:
  • Select the option based on your choice to fix the error.
  • Then, press the “Apply” button. You can also use the “Apply to all” option.
  • After this, you will get a notification while showing the number of imports that are successful and the number of errors. If you are having errors, click the “Save” button for an error log. Then, review it to decide the action to fix the error.

By executing these steps, you can import the item list in QuickBooks Desktop. If you encounter any error during the import, you can fix the error and try importing the item list again.

Method 3: Manually Add the Multiple Lists

In this method, you will have the option to add or edit multiple items list manually. Please note, this is only for list information; you cannot use it to import transactions. Now, let’s go through the steps to edit multiple lists in QuickBooks Desktop:

• Navigate to the “Lists” menu.
• Then, choose the “Add/Edit Multiple List Entries” option.
• Select the “List” drop-down menu and select the list you wish to work with.
• Now, you can filter your list to view specific records.
• Here, you can click on the “Customize Columns” option so you can decide which column you would like to show in the “Add/Edit Multiple List Entries” screen. By doing so, you can easily work on specific columns you want.
• By selecting the “Default” option you will go back to the preset columns for your item list. Here, you can do the following:
  • If you want to add the column then choose the column from the left and hit the “Add” button.
  • To remove the column, you can pick a column from the left and press the “Remove” button.
  • If you want to arrange your columns in order, all you have to choose is the column from the left and click on the “Move Down” or “Move Up”.
  • Press the “OK” button.
• At this point, you can edit or add the entries by going to the appropriate fields. Please note, you can copy the data from Excel and then paste it into this window. If required you can “Copy” and “Duplicate” rows as well. All you have to do is highlight the selected field and press the “Copy Down” or “Duplicate Row” options.
• Once done, click the “Save Changes” option.

After you save the changes, you will get a warning message indicating the number of records that have been saved. It may also show the number of errors that you have to fix. If you encounter any error, the following steps can be followed:

• First of all, hover your mouse cursor in any field and find what kind of error it is.
• Then, update the record to solve the errors.
• Once done, click the “Save Changes” button.

If you don’t prefer to fix the error right now, you can just save the applied changes and close the window. QuickBooks does not save any changes consisting of errors.

Besides MS Excel, you can use other ways to import or export the data in your QuickBooks account. For instance, CSV files, IIF files, and Web Connect files can be used to import certain details.

Summing Up!

QuickBook is quite flexible software to import and export items and other types of lists. If you are willing to import the item list, QuickBooks makes it possible for you. All you have to do is choose the type of method you would follow and then perform the steps we have discussed above. We hope you have learned how to import item list into QuickBooks Desktop.

Do you want to customize the reports in QuickBooks? Learn here how to create QuickBooks Desktop custom reports.