Import Inventory into QuickBooks Online with Inventory Sheet
Learn how to import your inventory into QuickBooks Online using a sample inventory sheet that you can download from your company.
Learn what each column means and how to set up your inventory properly the first time. Also learn what some mistakes some users make when they first set up the sheet, along with some helpful tips for Amazon sellers!
Where to download the sample inventory sheet from QuickBooks Online
Before you can import inventory into QuickBooks Online, you’ll need to make sure that you have inventory tracking enabled. That means that subscribers must have at least the QuickBooks Online Plus or Advanced subscriptions.
If you have a lower subscription, you’ll have to upgrade to be able to take advantage of inventory tracking.
Don’t want to use a sheet? Read our article on setting up inventory in QuickBooks Online for Amazon sellers for more advice.
To turn on the option, go to the Gear icon > Company Settings. Then click on Sales, and under Products and Services, turn on “Track quantity and price/rate” and “Track inventory quantity on hand.”
Once you’ve enabled these options, you can now download a sheet from QuickBooks Online to fill out so that you can import inventory.
To download the sample inventory sheet, click on Sales. Under New, click on the dropdown arrow, and then click on Import. On the next screen, you can download the form to edit and upload.
Once you download the form, open it up using Microsoft Excel, Google Docs, or any other spreadsheet application you use (LibreOffice or OpenOffice, for example).
Understand QuickBooks Online PnS (Products and Services) Sample Inventory Sheet
We’ll go through each column and discuss what to put in each one. Where relevant, we’ll offer some advice to keep your books more organized. Below is the sheet—close-ups are provided below.
The fields in the sample sheet correspond to the fields on the inventory or service items when you first create them. The form is below for reference.
Here’s a closeup of the first half of the sheet:
Product/Service Name – This field is required and unique—meaning only one Product or Service can ever have this name.
If you’re integrating your inventory with any other app, then changing the name may cause errors with that app.
If you’re an Amazon seller, we highly recommend that you set the QBO Name field to the same as whatever your Amazon SKU is. That’s because the Amazon SKU, like the QuickBooks Name, does not change.
Below is a reference. To keep things the same, we’d recommend that sellers use “SS_Spoon” in QBO for the Name field. That way, unique identifiers from Amazon and QuickBooks Online match.
Otherwise, whatever you decide to use for your Inventory Name, you’ll want to maintain a naming standard or consistency. Too often, sellers will just type anything into this field, and then as the business grows and others add to their product list, their entire inventory database can become very messy.
Then it becomes a mess to rename and redo everything; in some cases, renaming won’t be feasible. So think carefully about how you want to name your inventory.
Sales Description – This field is optional. This is where sellers would normally put the Product Name, or title, from Amazon. This field will automatically populate on the invoices whenever you select the product or service for documents. While it’s not necessary, it may be helpful if you don’t have descriptive item Names/SKUs.
If you’re optimizing your product title, then you may want to consider populating this field later if you want to maintain more precise and consistent records.
SKU – The SKU field is optional in QuickBooks Online. This part usually confuses many sellers since SKUs remain static or unique for most retailers. That means you can have multiple products in QBO that all share the same SKU.
If you sell on Amazon, we highly recommend you use your product’s ASIN here. That way, you can easily search by Amazon SKU or ASIN in QBO.
Type – This field is required. You can put either Inventory, Service, or Noninventory in this field. Most sellers would put Inventory here. If you’re uploading services and non-inventory, you may want separate sheets for those to keep them organized, or have a different section for services and noninventory so that they’re all grouped together.
Sales Price / Rate – This field is optional. You may want to leave this field blank so that users entering in transactions are forced to enter the correct price here.
Any value you put here will automatically populate when creating transactions. Since prices change often when it comes to ecommerce, you may want to leave it blank.
If you’re using SellerZen to automate your Amazon to QuickBooks Online integration, then we’ll automatically enter the price the item sold for on the transaction.
Taxable – By default, QuickBooks Online will mark Products as Taxable. If they’re not taxable, then you should enter “No” in this field.
Income Account – By default, QuickBooks Online will use the Sales of Product Income account for all sales proceeds from this item.
If you have a different account, then it’s important that you select that here.
For instance, if you have two Amazon seller accounts, and you’re tracking both accounts on the same QuickBooks Online company, then you may want to consider having two separate sales of product income account—one for each seller account.
This allows for better reporting so that you can see sales from each account rather than having both lumped into one sales account.
Some sellers may use separate sales of product income accounts to track different brands, product lines, etc.
You can have SellerZen use different accounts if you’re using our platform to automatically synchronize your Amazon seller accounts with QuickBooks Online.
Here’s a closeup of the second half of the PnS sheet:
Purchase Description – This field is optional. Some sellers may want to put the manufacturer’s description here or just notes regarding the item.
Purchase Cost – This field is optional. QuickBooks Online doesn’t really use this field to calculate your COGS or purchase cost. In the absence of any number, QuickBooks will refer to this field.
Your actual purchase cost will be determined through your POs, Bills, or other documents you use to purchase the item.
Learn more about why Cost of Goods Sold may be zero or missing from your reports.
Expense Account – This field is required. The default account is the Cost of Goods Sold account. If you’re using a different Income Account, then consider using a different Expense Account as well.
Quantity on Hand – This field is required. Many sellers are tempted to put their actual quantity on hand here. But if you’re setting up your company for the first time, you’ll want to put zero.
Then use vendor documents like POs, Bills, or Expenses to record inventory purchases. This way, QuickBooks Online will accurately track costs. Make sure your dates are correct, as QuickBooks Online uses FIFO.
Reorder Point – This field is optional. This will let you know when you should reorder a particular product.
Inventory Asset Account – This field is required. You may want to change this if you’re changing the Income and Expense accounts.
Quantity as-of Date – This field is required. This date is important because QuickBooks Online uses this date as a limit; you won’t be able to backdate a transaction to a date before the as-of date of the product.
If you’re setting up your company for the first time, then you’ll want to use a date that precedes your first sale. Some sellers make the mistake of using a current date, and that will just result in errors when employees try to enter earlier transactions.
Even if you make a mistake, you can re-upload the form to update your list of inventory. Just select the fields you want to change. We’ll go over this in the next section.
Importing your inventory sheet into QuickBooks Online
Once you’re ready with the sheet, it’s time to upload it to your QuickBooks Online. Warning: make sure that everything on the sheet is correctly formatted. The dates should be in MM/DD/YYYY format.
The first row will be ignored, but everything after that will be imported. Delete the yellow warning on the first column that starts with “Do not import this file as is…”
You’ll want to avoid re-importing to update items, as that can sometimes cause confusion and errors.
Here’s a completed row for the inventory sheet. Note that the first row, containing the labels, will not be imported.
Let’s go ahead and upload this sheet into QuickBooks Online and get the product created.
Go back to Sales > Products and Services > Import (under the New dropdown menu). Click on Browse and select the sheet you’ve just created.
Click on Next on the bottom right of the screen.
Verify that all of the fields that have data in them are selected so that QuickBooks Online will import those settings.
If you changed the labels, you’ll have to select them again on the next screen to map your labels to the correct QuickBooks Online labels. For instance, if you renamed the Product/Service Name label to “Inventory Name,” then you’ll need to select “Inventory Name” so that QuickBooks Online knows the two labels are the same.
Click next once you’ve verified all the labels are correctly mapped. If you’ve used the same sheet you downloaded without editing the column labels, then you should be fine.
On this screen, you can verify and make any final changes to your products or services before you start the import. If you notice a lot of mistakes, it’s better to cancel the import, fix the mistakes on the sheet, and then go through the process again.
Otherwise, if you have just a few mistakes, fix them on this screen and start the import by clicking on Import on the bottom right of the screen.
And here we have the new product in QuickBooks Online that we’ve imported.
If you missed a column, then you can re-import and overwrite some of the values you previously entered, but this process can be tricky.
Sometimes, QuickBooks Online will overwrite the values with the updated ones on the sheet, while other times the process will complete with no updates even if you’ve checked the “Overwrite all values” option.
You’ll want to do your best to make sure that all the values are correct to save yourself the headache here. If the “Overwrite” option doesn’t work, you’ll have to manually edit all of your products to the correct value.
That’s it! The sample product and services sheet from QuickBooks Online may seem intimidating at first, but it’s a fairly quick process since many of the fields will stay the same. For instance, you’re likely to use the same information for the Taxable, Accounts, and Type.
As for the Name, Sales Description, and SKU fields, you’re likely to copy and paste the information from wherever you currently keep your inventory information. Most marketplaces, like Amazon or eBay, will allow you to download a spreadsheet of your inventory, making this process a bit less frustrating.
Like this article about QuickBooks Online? Read other helpful articles below.