Skip to main content
A
arkvalleyinfo
December 11, 2018
Question

Importing a list of checks from excel into Quickbooks

  • December 11, 2018
  • 7 replies
  • 0 views

Every week I have a list of names(payee) and amounts of 25+ independent contractors in an Excel spreadsheet which is two columns.  This provides the information that is used to handwrite all of these checks. Can I import this Excel spreadsheet into Quickbooks Pro 2013 and print all the checks at once?  Every week the number of people and the amounts change.

    7 replies

    A
    arkvalleyinfoAuthor
    December 11, 2018
    Yes it possible.  Importing into QB is not simple and less you have 100s of checks generally not worth the effort to set up the excel file to import into QB.

    I find it a lot easier to use the excel list as the input source document, enter each payment as a bill...  Then In QB using  pay bills, select the bills to be paid and write the checks.
      A
      Anonymous
      December 11, 2018
      You can use the IIF Transaction Creator to convert an Excel worksheet into an IIF file that you can import directly into your QuickBooks file. It works with most transaction types.

      If you pay the same contractors, or mostly the same ones, each month, then you can reuse the same worksheet each month to create a new batch of checks to import.

        K
        kane_2
        January 25, 2019

        Have you used the Import > transactions > checks function?  I am trying to use this function right now and it is a little pokey.  For instance, it was importing the transactions in a very clean manner - then it stopped importing the check numbers and made  all the imported transactions appear as checks to print.  Have you been able to get an answer to this original question without needing to use a IIF file?

        IamjuViel
        IamjuViel
        January 25, 2019

        Hello there, @kane_2.

         

        You’re already on the right track! Allow me to help walk you through importing your check transactions into QuickBooks Desktop using an excel or CSV file.

         

        Here’s how:

        1. Go to Accountant.
        2. Choose Batch Enter Transactions.
        3. From the Transaction type drop-down, choose Checks.
        4. Choose the appropriate bank or credit card account where it will be recorded.
        5. Open your spreadsheet and arrange it the way the columns in QuickBooks papers. Or you may click on the Customize button to re-arrange the columns in QuickBooks to match what you have in your file.
        6. Copy and paste the transactions from your spreadsheet or excel file to QuickBooks.
        7. Click Save Transaction.
        8. Hit Yes.
        9. Click OK.

        On the other hand, yes, you can print all checks you’ve created and recorded in QuickBooks all at once. Here’s how:

        1. Go to Write checks.
        2. Click the Print drop-down arrow, choose Batch.
        3. Choose the Bank account where the checks are recorded.
        4. Enter the number of the First Check to be printed.
        5. Put a check mark on all the checks you want to print.
        6. Click Ok.

        On the Print Checks window:

        1. Choose the Printer’s name.
        2. Select the Printer type.
        3. On the Check Style section, choose if you want to print the check as Voucher, Standard or Wallet.
        4. If your pre-printed checks already have your company name and address on it, uncheck the box Print company name and address.
        5. Check and make sure all other settings are correct.
        6. Click Print.

        That should do it! Make sure that your QuickBooks software is updated to the latest version so you will not have problems during the importing process.

         

        If you need further help with the steps above, I’d recommend reaching out to Technical Support team. A specialist will be able to look up your account securely and further assist you via remote access session.

         

        Here’s how to contact us:

        1. Go to: https://help.quickbooks.intuit.com/en_US/contact.
        2. Choose your QuickBooks Product.
        3. Select your QuickBooks version.
        4. On the Contact Us page, click a topic.
        5. Click on the Get Phone Number button to see the support number.

        Stay in touch with me to let me how it works on your end. I’m always here to help in importing and printing your check transactions by batch in QuickBooks Desktop.

        S
        smei
        February 28, 2019

        Where do you find go to Accountant? Do you have to on Admin user  or can i import the list on Multi- user mode? I cant see the Accountant option there for me to look

         

         

        Thanks,

        Michael

          K
          Kirill - CloudBusiness LLC
          March 13, 2019

          Hello,

          As a workaround, you can try to use Business Importer app to import Checks and other entities from .xlxs files into you QBD Company.

          It also has many useful features as Export and Schedule Import. 
          The app has 14 day free trial period, so you can test if it suits your needs.
          You'll find it here https://desktop.apps.com/apps/167128/business-importer

          I hope it will save you a lot of time!

          MaryLandT
          MaryLandT
          June 24, 2019

          Hello @sabreu,

           

          The Accountant menu is only available for QuickBooks Desktop Accountant user. If you're not an accountant user, you can import transactions by following the steps provided by my colleague, @Charies_M.

           

          I've also added this article for the file types and formats you can bring in to QuickBooks: Import/export overview. 

           

          To learn more about importing data with MS Excel files, check this out: Import or export MS Excel files.

           

          Let me know if there's anything else you need. Just leave a comment below and I'll get back to you.

          J
          jpupecki
          August 11, 2022

          LONG READ BUT STEPS are SUPER SIMPLE once you know how to do it!

           

          Yes it's possible with Batch Transactions. I am using QuickBooks Desktop Pro 2017 for this example. Please note import is misleading - when you open up Batch Transactions there is NO import button, you need to copy and paste from Excel to QB.

           

          A previous response said that Batch Transactions would be located under the Company Tab if I didn't have the Accountant's version but it wasn't there for me. I worked around this by adding it as a shortcut by clicking on the "Customize Shortcuts" at the bottom of the panel (see attached png).

          Once it was added I needed to prep my excel file by making one tab for checks and one tab for deposits. (I can't seem to add more than one file to upload in this reply so I'll try to reply to this comment once it's posted with the additional screenshots).

           

          At minimum for expenses I needed to have the following columns:

          1. Date
          2. Number (for the check number)
          3. Payee
          4. Account (this matches up to one of your chart of account accounts)
          5. Amount
          6. Memo (not required but helpful!)

           

          For the deposits I needed to have the following columns:

          1. Date
          2. Payee (not required but necessary if you've returned items and want the return under the correct payee)
          3. Account (this matches up to one of your chart of account accounts)
          4. Check No (not required but necessary if you're logging the check number a customer paid you with)
          5. Amount

           

          It doesn't matter which order your columns are in BUT, when you go to the batch transaction screen you must customize those columns to match the order in your excel spreadsheet. All you need to do is copy all the columns and rows of data from Excel and then paste them into the Batch Transactions Window once you open it (remember it's under your shortcuts panel if you following my example above).

           

          If you're pasting the expenses then make sure to choose Checks, if you're pasting the deposits, make sure to choose Deposits.

           

          Make sure once you've pasted the data that NOTHING IS RED. If it's red that means QB isn't matching it up to an existing thing (vendor, customer, chart of account, etc).

           

          Lastly, make sure at the top of the Batch Transactions window you've chosen the correct BANK ACCOUNT (checking, savings, etc) that you want these transactions to be imported to.

           

          At the bottom click "Save Transactions" button and you're good to go! 

           

           

          J
          jpupecki
          August 11, 2022

          This interface won't let me upload any images (sorry!) so here is an example of my excel checks sheet. Note in our Chart of Accounts account 50400 what we put in the Number field is Construction Material Costs, 60400 is Bank Service Charge, etc. Whatever is in your Account column must EXACTLY match the number you've put in the number field when you made that account or when you paste it, it will turn red and error out. Whatever is in your Payee field must EXACTLY match your Payee/Vendor name or it will error out.

          My credit/deposit sheet looks similar.

           

          DateAmountNumberMemoPayeeAccount
          9/16/20201208.69113CHECK 113  REF. NO. [removed] 80000
          9/17/20203603.07 8674 Debit Card Purchase Jaeger Lumber- Middles 908-6860070 NJJaeger Lumber50400
          9/17/2020105.91 8674 Debit Card Purchase Jaeger Lumber- Middles 908-6860070 NJJaeger Lumber50400
          9/17/2020258.37 8674 Debit Card Purchase Jaeger Lumber- Middles 908-6860070 NJJaeger Lumber50400
          9/17/202036 Overdraft Item Fee 60400
          M
          MariaMager
          August 16, 2022

          Would anyone know how to do this with QuickBooks Online?

          Fiat Lux - ASIA
          Fiat Lux - ASIA
          August 16, 2022

          @MariaMager 

          Prepare your Checks data with the following template

          RefNumberBankAccountVendorPrintOnCheckNameTxnDateLocationAddressLine1AddressLine2AddressLine3AddressLine4AddressCityAddressStateAddressPostalCodeAddressCountryPrivateNoteToBePrintedCurrencyExchangeRateExpenseAccountExpenseDescExpenseAmountExpenseBillableStatusExpenseBillableEntityExpenseClassLineItemLineDescLineQtyLineUnitPriceLineAmountLineBillableStatusLineBillableEntityLineClass
          101CheckingJoe's Coffee ######### 12 Main StreetSuite 102  AlbanyNY12345  N  General SuppliesGeneral Supplies85.21           
          102CheckingCostco ######### PO Box 1000   DallasTX28456  N  General SuppliesGeneral Supplies125           
          103CheckingProperty Mgmt Assoc ######### PO Box 1800   AlbanyNY12345 Mar 2011 RentN  RentRent1000           
          103CheckingProperty Mgmt Assoc ######### PO Box 1800   AlbanyNY12345 Mar 2011 RentN  Common Area MaintenanceCommon Area Maintenance100           
          104CheckingSouthern Bell ######### PO Box 4500   DallasTX28456  N  TelephoneTelephone250.53           

           

          then utilize an importer tool to proceed

          https://transactionpro.grsm.io/qbo

           

          M
          MariaMager
          August 16, 2022

          Thank you so much for the information.  Just to confirm, there is no way to import without using an outside importer tool?  Once I have the information in the proper format how do I upload it to Quickbooks?

          Terms & ConditionsAccessibility statement