Updated 01/29/2018 by Hector Garcia, CPA

First of all, is important to disclaim that the following exercise i will be discussing to import Invoices into QuickBooks from an Excel spreadsheet will only work in two versions of QuickBooks:

  • QuickBooks Accountant 2014 to 2018
  • QuickBooks Enterprise 14-18

That being said, if you have QuickBooks Pro,QuickBooks Premier, QuickBooks Mac or QuickBooks Online, this is 100% irrelevant as this feature is not available in those versions..
_________________________________________________________________

Alternatively, you can always export bank transactions from excel into QuickBooks using a 3rd party tool called CSV2QBO or 2qbo Convert Pro by MoneyThumb, here is a quick video that explains how the converter software works:

__________________________________________________________________

 

Now, back to using QuickBooks Accountant, I must also point out that in year 2013 of Accountant and Enterprise you can import Checks, Deposits, and Credit Card Charges/Credits; the additional transactions such as Invoices, Customer Memos, Bills, and Vendor Memos are only available in 2014-2018 versions

Now, onto the exercise, step 1 is to make sure your spreadsheet contains all the required data that will be imported (copy/pasted technically)

Screen Shot 2014-02-15 at 7.31.35 PM

 

In this example, you see the following columns:

  • Number (or Invoice Number)
  • Customer Name
  • Date
  • Amount

Technically, this will be sufficient to start the process, but I recommend to have the ITEM name as well, the Item is the on the points to the account that will be affected with the imported invoices.  Other Columns to consider: Description, Terms, Due Date, Template…  No Custom Fields can be added to the Batch Import feature.

 

Step 2, go to Company menu, and Batch Enter Transactions (In QuickBooks Accountant, this feature will be in the Accountant menu).

BATCH ENTER TRANSACTION

 

 

Now you will see the Batch Enter Transaction window and will have to customize the columns to match the same as the Excel spreadsheet.

Screen Shot 2014-02-15 at 7.58.07 PM

 

 

Notice that the “Total” and “Item” Column are there, because they are required, but its ok if its not in your spreadsheet, because that can be loaded afterwards.

Then all you have to do is Copy the data from Excel:

Screen Shot 2014-02-15 at 8.00.19 PM

 

 

And Paste it into QuickBooks:

Screen Shot 2014-02-15 at 8.03.06 PM

 

Whatever you will see in RED is for names or items not in the QuickBooks Database.  So you can simply Quick Add them to the Customer list by clinking on each of them, If they had already been in Quickbooks, then they would show in black and you would not have to do anything else.

Screen Shot 2014-02-15 at 8.07.03 PM

 

And then finally, just add the item to each one, or just add one on the first line and then Right Click, Copy Down to have all the invoices bear the same item (if applicable to your situation)

Screen Shot 2014-02-15 at 8.09.35 PMScreen Shot 2014-02-15 at 8.10.12 PM

 

Finally click on Save Transactions, and all the Invoices will be created automatically.

 

Then, you can see all the invoices that were created

Screen Shot 2014-02-15 at 8.13.35 PM

 

I want to mention something very important, this import feature will only allow a single item per invoice being imported, so if you need to import invoices that contain more than one item, you will need to use a 3rd party data import tool such as the few I discuss in my Import Excel Data Into QuickBooks article.

If you have QuickBooks Pro or QuickBooks Premier, this will NOT work… You need to UPGRADE to QuickBooks Accountant Edition 2016.  Call me at  1-800-819-6307, I will try to see what is the best price I can get you.

Also, if you like this topic, check out this video I published:

 

Share →

15 Responses to Importing Transactions from Excel into QuickBooks Desktop Accountant

  1. Avatar Nekipedia says:

    FYI: The batch enter transactions feature DOES NOT restrict import to 1line per invoice and; therefore, it is not necessary to use a 3rd party application to import multiple line invoices. Simply place your curser on the invoice line that contains multiple items and then click “Split” in the lower left hand corner of the batch window. A new window will open that allows you to enter all the items. This feature is also available for cheques, deposits, credit cards etc. I have used it extensively and it is especially helpful for transactions, such as ATM withdrawals, that contain an additional ATM Fee which you obviously want ensure is posted to your expense account for service charges [if applicable of course!]. Or in the case of a home business, that claims only a percentage of the expense, this permits you to allocate a portion as business and the rest as living expenses – ALL IN A BATCH WINDOW – without having to later search for those transactions that you need to allocate to multiple accounts.

    • Avatar Hector Garcia says:

      I dont disagree. but the problem is you cannot import more than one transaction with splits, that was my comment alluding to.

  2. Avatar ChrisR says:

    When entering a batch of bills, do you know of any way to get it to catch/flag duplicate invoice ref numbers? We use a shared doc to input and track certain invoices, and I am just copy/pasting to QB’s now from there (have to move to Excel first, since copy/pasting straight from Google Sheets doesn’t seem to work). There are multiple hundreds of these a week, so I’d be really nice if it would flag duplicates in here just as it does from the traditional enter bills place.

  3. Avatar Akinbuwa Adekunle says:

    I use the quickbook premier 2014. and i couldn’t load data into it. i have to go through the stress of inputing the agent and invoices one after the other. Now, how do i upgrade, or do it to enable me upload via excel to quickbook.
    Waiting to read from u
    Thanks

  4. Avatar David Casebere says:

    I am looking for instructions on how to upload a budget from Excel by class, The instructions above are great but seem to be useful for uploading only a whole company budget rather than budgets by class. Do you know if such instructions exist somewhere and if so, would be please email them to me? Thanks for your help.

  5. Avatar Chamara says:

    Hi ! Can we enter GST amount in batch transaction entering. Because I couldn’t see such column in entering the Bills. It only appears in entering Invoices.

  6. Your tutorial is fantastic and very easy to understand. I am a bookkeeper and work for a CPA firm. When our “shoebox” clients bring their data in it is a nightmare. Your instruction makes it a little easier to swallow.

    Thanks,

  7. Avatar GWEN says:

    Do you have a spreadsheet that you could share that has been built for this purpose?

  8. Avatar Carrie says:

    I am using Enterprise Accountant 14.0 when I try to paste my data everything stays in the date column

  9. Avatar Abdul says:

    Hi Thank you for such a great tutorial. I was trying to import cheques but amount column is giving error during copy paste. If I enter amount manually then it is accepting. Is it format problem or what? can you please guide?

Leave a Reply

Your email address will not be published. Required fields are marked *



For More Information

Call us to 954-633-2718 or email: hector@quickbooks-training.net