Intuit made major changes in their 2013 QuickBooks product line; some changes were controversial like the new User Interface. Countless numbers of people complained about the black-gray-white schema and lack of color options. Intuit officials heard these complaints and made changes to deal with the UI issues in R5 and R6. Looking beyond these controversies, a lesser known feature, “Batch Enter Transactions”, made a first time appearance and is a very welcome addition.
This new feature is found in the Accountant versions of QuickBooks and allows the posting of 1000’s of transactions from Excel using copy and paste. You match your spreadsheet by customizing the columns of the entry screen, and use spreadsheet like features such as ‘copy down’ to post information into data fields you don’t import. You can use Batch Enter Transactions to add Checks, Deposits or Credit Card Charges or Credits to QuickBooks.
While I have used products like Transaction Pro Importer (from Baystate Consulting) in the past to import transactions, their product requires a ‘learning curve’ which may users and professionals are not willing to invest. Batch Enter Transactions, while limited in the types of transactions it can post, is very easy to use, as you will see.
To Batch Enter Transactions from an Excel Spreadsheet, follow these steps:
1. From the Accountant Center or the Accountant menu, select Batch Enter Transactions.
2. Pick the transaction type you want to enter (checks, deposits, credit card charges or credits).
3. Click the Customized Columns button to open dialog box that allows you to select and order the columns of data you wish to paste into QuickBooks. You need to insure that your columns are in the exact same order as the columns of your Excel spreadsheet.
4. Verify that your Excel data matches your QuickBooks list information. The accounts (or items) in your Excel data must exactly match your accounts (or items) in QuickBooks. If the account, name or item is not found in your QuickBooks data you will have the option to add them. (By the way, you can use either Account Numbers or Account Names when posting your data.)
5. Copy your Excel spreadsheet data to the windows clipboard.
6. In the Enter Batch Transactions dialog box, right click your cursor in the first field you want to post, then select Paste from the right click menu, your data from Excel will populate the grid. In this example my cursor is in the Date field.
Note: You can enter multiple lines (or “splits”) for the transaction by clicking on the Split button.
7. Review the data you just posted. Errors posted from your Excel data will be highlighted in red. QuickBooks defaults any empty field copied from Excel with data from the column to the left and also highlights the data in red.
Note: Each field has different characteristics, be sure to review these and make changes as needed; QuickBooks pops-up a message for highlighted data when you move your cursor over the field.
8. For any fields with missing data, begin typing or use the dropt-down for the proper information for the field. The field displayed below is Account so QuickBooks will display available names in the list.
Note: In the lower right you can see the total of the transactions to be saved and compare with your original source documents.
9. Click the Save Transactions button; QuickBooks displays the number and type of transactions saved.
10. Review your bank account register to verify the newly added transactions.
Many banks do not offer downloads of checking account data in QuickBooks formats, but they do offer Excel data. In the past we have had to rely on other 3rd party products to either translate that data into a useable format or import the data from Excel into QuickBooks, but with Batch Enter Transactions those types of entries can easily be performed without the need to purchase additional software.
While my emphasis in this article is in using Batch Enter Transactions to copy and paste data from Excel format, each of the transaction types can be ‘hand entered’ using the same dialog box. This means if you have a stack of hand-written checks, or a pile of credit-card receipts, you can sit down and knock them out in far less time than having to enter them via the traditional user interface entry screens.
So what does the future hold for this feature, well at a recent Webinar of the National Advisor Network, Intuit’s Product Team for QuickBooks Accountant indicated they were considering a variety of ‘other transaction types’ that would make use of this ‘batch entry’ feature. We can only hope that they expand this capability to include transactions like customer invoices, vendor bills, and payroll checks. Who knows, one day Batch Enter Transactions, may be the ‘routine method of data entry’ in QuickBooks.
In my way of thinking Batch Enter Transactions is clearly the Best New Feature in QuickBooks 2013.
William “Bill” Murphy