How to import Batch inventory items in quickbooks desktop

In this tutorial we will see whether how we can import and export inventory items using copy/paste method. 

 

So, to import Inventory items into QuickBooks, either you can select “Add/Edit Multiple List entries” option under the list menu or you can go to the “Services & Items” window and select Import from excel or paste from excel under the excel dropdown. All the options will take you to the same window. 

Tutorial Instructions:

Here are the services items we have imported earlier and this time we are going to add tangible inventory products. So click on “List” dropdown field and select “inventory Parts”, and here are the items we have added in our previous tutorial.

Well, here are the products that I am going to import into quickbooks but first what we need is to align or match the columns so that we can copy and paste the inventory items conveniently so, to add or remove the columns click on the “customize columns” button it will pop-up a small window where we can remove the columns, we don’t need by selecting and clicking on the remove button, and exactly the same way we can add the ones we need by selecting and clicking on the add button. And we also can position a column according to our need by selecting and clicking on the “Move Up and Move Down” buttons and click on “oK”.

Now, just copy and paste the inventory items from excel to QuickBooks window and here Quickbooks found some issue with the ones turned into “Red” color. now lets click on the red ones to see whether what issue quickbooks found. Well, its saying that the product is not in the List of products and services and its also asking whether would you like to add it now. We definitely want to, so click on “Yes” and fill the required fields and then click on “ok”. Well you can’t add an item or product without filling the required fields like, Name, COGS account, Inventory Account, and Income Account and the other fields can be left empty but if you have the full info then do fill them on the go and then click ok.

And here you can see that as soon as the item or product has been added to the products and services list, QuickBooks recognizes it and turned the other same products into black color by applying the same settings of the just added product.

Now exactly the same way, add the other products to the Products and Services list, here you can see that quickbooks has created those products and somehow the red ones on the “Item name” column are considered duplications that’s why when we click on them, it doesn’t show any issue. Anyhow we will take care of these duplications later.

And, QuickBooks does not accept numbers with dollar sign so just click on it and change it.

Moreover, just go through your data and make changes accordingly, here, Cost of goods sold account hasn’t been applied, just click on the field and select Cost of goods sold account and here, this vendor’s profile hasn’t been created so far, just click on it and either set it up by providing the full profile info or just click on “Quick Add” to create the vendor profile. Here this field is also empty just click on it and select your income account and here in the “re-order and Max” columns, we can remove the re-order and max “points” according to our needs and here in the “As of date” field that date is not being recognized properly just click on it and add the date,.. and then just select “copy down” by right clicking on it, it will copy down that date to all. 

Now just see your data to see if you need to make more changes or if there are some missing fields and if the data seems fine then just click on “save changes”, here its letting you know that 25 inventory item records have been saved and 7 records have some issue, so click on the each one to see whether what is the issue, here when you click on a field or hover the cursor over the field, a small pop up message is informing that this name is already in use which means, these inventory items have already been created, we can just delete them.

After deleting the duplicates, ,,click on the “save changes” button and then in the “View Dropdown” field select “Active Inventory items, now look at your data again to see if you need to make some more changes in the data and  if It seems good that just click on “Save Changes” and then close the window and here you can see, that the items list has been imported and nicely populated, separating the main item and subitems. Now, open a couple of products to see how the fields have been populated and if there is a need to make any changes.

Well in case, if you need to make some changes to all your inventory products, like increasing the “Margin” or “markup” on every product, you sure can, just click on the List Menu and select “Add/Edit multiple List entries” and then select “Inventory Part” in the “List Dropdown” field.

Here click on the “Customize column button and add “Mark-up or margin” column and here you can see the current margin rates of every product which you can go through and edit the margin of each and every product or if you want to change it to a standard margin rate then add it to one and copy down it to all. It will just apply that margin rate to all the products right away then click on save changes button and close the window. Now check a couple of products by double clicking on them and here you can see that the new margin rate has been applied to them.

Now lets say that you already have a nicely created items list in your quickbooks desktop which you want to export so, just click on the excel dropdown and select “Export All Items”, It will open a small pop-up window where you will have 4 different options to select from to export the items list. The first one is by “Creating a new worksheet” which means, the list will be exported by creating a new workbook.

The second one is by “Updating the existing Worksheet” here just select your worksheet and update it with the current list of items.

The third one is by “Replacing the Existing Worksheet” and exactly the same way you will select your concerned worksheet and then replace it with the new items list.

And the last option is to export the list by creating a CSV file.

Well, I am selecting the first option which is exporting the items list by creating a new worksheet and then click on Export, it will take a few seconds to export the items list and here you can see that the items list has been exported and since it’s a macro enabled workbook, we can just save it by allowing it to save it as macro free workbook and here is the inventory list that we have just exported.