QuickBooks users most frequently ask us how they can import data from Excel into QuickBooks. You can create in excel your journal entry as long as it’s in their proprietary format so you can import that directly into QuickBooks but in my experience, it is a bit challenging to get the formatting exactly right and then to go through the process to import.
+1-800-942-0215 to speak with a QuickBooks Software Specialist.
Support - We provide solutions for the following QuickBooks (Pro, Premier, Accountant, Enterprise, Payroll, Cloud)
- Error Support
- Data Import
- Data Conversion
- Payment Solutions
- Upgradation
- Payroll Support
- Data Export
- Software Integration
- QuickBooks Printer Support
- Complete QuickBooks Solutions
- Tax-Related Issues
- Data Damage Recovery
- Installation, Activation Help
- QuickBooks Windows Support
Chat Now
Schedule a MeetingAs I’ve had a number of larger journal entries, I have found it very time-consuming to create these files and then import them into QuickBooks, thus we needed something that would be simpler on a daily basis to post journal entries to QuickBooks from Excel with the utmost ease.
Basically, what we have done in this excel spreadsheet is just added some programming to the back end so that you can create a journal entry in Excel and then push it into QuickBooks almost immediately by just clicking a button. Let me just point out a couple of things before we start. First of all, you need to be running in order to use the import tool. Read more: How to Import Estimates into QuickBooks Desktop?
What are the requirements to import data:
It must be a Windows version of QuickBooks desktop since this will not work on a Mac operating system. It can also be fairly old, as the specs show it works back as far as 2002, which means it does not have to be a brand new version of QuickBooks.
Essentially, excel needs to be version 2013 or later and 32 bit for it to talk to QuickBooks, since the tool that connects to QuickBooks is a 32 bit program. So, make sure you’re running the 32 bit version of excel, and it needs to be a 2013 or later version.
Our spreadsheet has just a few workbooks, so we’ll create the journal entry in this table and then post it from here as well, and then we have the tables workbook, which contains information from QuickBooks. If we don’t have the account names, we can still create the journal entry from excel into QuickBooks if you’re trying to post from Excel to QuickBooks.
Read More: How to Import CSV Files in QuickBooks Desktop?
Process of making journal entries:
The actual process of posting or creating your journal entries is made much simpler if we first reach the QuickBooks account and get a list of all your account numbers. When you open up the spreadsheet, the first thing you should do is connect to QuickBooks and start collecting data so you can start building your journal entries. But before I get started, let me explain a couple things.
The steps:
The first time a software tries to connect to QuickBooks it will ask for permission to communicate with that software, so at first when connecting you’ll notice a box that asks for this permission, so it tends to only communicate with an open QuickBooks company by default.
- The first thing you must do is open your company on your computer, and then you will see a left-hand menu at the top, and then in order to appease these tables, we simply need to click the update tables button.
- I am sure you want to know the answer whenever QuickBooks company files are open. After I click on that in QuickBooks, this pop-up appears asking you to grant the ability to talk to your QuickBooks file.
- It’s just for convenience. You can also select Yes, but then you’ll have to do it every time you select something like post journal entries or update tables. When QuickBooks pops up, it asks for permission again so if you want to go through the process every time, you can select prompt each time. It’s just my preference.
- If you choose to say yes always allow access even if QuickBooks is not running, you might encounter some issues because this software is set up so that it will seek out whatever company opens; you might encounter some unexpected results, especially if you have more than one company open.
- Depending on the company file that you’re looking for, it may not open, so I recommend continuing here whenever QuickBooks company file is open just confirm your selections.
- The tables will populate the process is now complete and now I have a list of all the accounts, all the vendors, and all the customers in this QuickBooks company.
I’d like to point out about these tables, that they can be updated as often as you’d like, which means that whenever your account changes in QuickBooks simply click on update tables and it will adjust the tables right away. The program will always re-update tables based on the most recent data for the existing company, so it will always look at your QuickBooks company that’s open.
The tables can be updated as often as you’d like, and you’d certainly prefer to update them whenever you want. Your QuickBooks company allows you to add or delete accounts.
Once these have been populated when I go back to the Journal Entry Workbook, I have this drop-down setting up to look at that table. Once these are added, all the accounts listed in my account will be available here for me to select the most recent start time.
Read More: How to Import Customers from Another Program in QuickBooks?
The important points to consider:
Here are a few things to note.
- Whenever you enter information into this table, it will search for the values in the set of boxes.
- If you haven’t used tables very much in Excel, just bear in mind that you see the blue frames around the set of boxes as you enter information.
- I just want to let you know that if you type another value into the blue box to add a new line, you’ll notice that it expands immediately to include the new line.
- Simply fill out the information in the line immediately below the box and it will automatically expand to include it.
- It won’t expand that line if I add information down here with some spaces in it.
- The delete rows instruction will shift up the rows inside the table if you need to delete just one.
- I think you need those values to post a journal entry, since the Asterix on them says that they are required fields.
- I have set this up so I have a positive amount of debits and negative amounts of her credits, so that’s what you need about a date and you need about that account.
The other thing, of course, is that it needs to be in balance.
Basically, this bill adds up the total of everything in the amount column in the table in order to create a balanced journal entry, because a balanced journal entry must have all of its amounts zero, and that’s what this bill does.
Read More: How to Import Bank Transactions into QuickBooks Desktop?
Few points to be noted:
- In the case of anything less than 0 or anything other than 0, it will be red, so let’s just do a basic journal entry. Just put in a date. It would be better if you opened a few classes so that you could see how they came through. We also need a net zero here, so we will need to journalize the transaction. All the required fields are completed, so it should net 0.
- Therefore, I want you to open up this account so you can see how this transaction comes through. You should see a popup in the right-hand side if this is dated to one. You noticed there is a journal entry right there. The journal entry is opened.
- The amounts match exactly with what I posted, so all you need to do is make sure your dates are correct and all your accounts are netted out.
- If you try to post this now, and you don’t have everything complete, for example if you did not get a balance on the journal entry, you will immediately get a response saying that the transaction is out of balance.
- You will get an error back if you are missing a number in here. Because there is no number in there, it is not a valid account. So that when you get the messages back, you can refer back to the message here to fix your journal entry, I am saving it on the line here.
When you use Excel for creating your journal entries, the real power comes from using numbers from other systems and pasting them into your journal entry or posting a series of amounts together into your journal entry. The amounts came from another system, so you could copy them and paste them directly into your journal.
Read More: How to import customer list from excel to QuickBooks desktop?
Formulas are also available here so now you’ve got all your amounts here and the net 0. Accounts receivable can be copied and pasted. This chart shows what your accounts will look like, but you can paste them in there as well. There shouldn’t be any problems. The journal entry gets posted to QuickBooks with a simple click of a button.
I have created a journal entry which changes a little bit every month so what I’ll actually do is save the spreadsheet as the current month’s approval next month I’ll repeat the process and save out the spreadsheet as a new month and then just add those adjustments to QuickBooks.
We have one system where previously the billings were manually entered by the insurance companies, and that is how it was used. Multiple hours were needed to include all of these items directly into QuickBooks. However, once the solution was implemented, they could simply copy these amounts directly from their email and then paste them directly into the journal entry here and in excel.
Read More: How to Import Credit Card Transactions into QuickBooks?
Using formulas, you can add a couple of more lines and then could post directly to QuickBooks, so instead of taking hours to process every paper, it now takes just a few minutes to pace the posted directly to QuickBooks. The method described here can be found on our website, but this is just a basic overview of the possibilities that can be explored through custom programming channels. If you feel that your organization might face some unique challenges, a customized program might be able to assist. Visit FastFix247.com for more information.
Few Good Reads:
- How to import an IIF file into QuickBooks?
- How to Extract Data From QuickBooks Enterprise to Excel?
- How to export QuickBooks transactions to another company file?
- How to export data from QuickBooks Enterprise?
QuickBooks Won’t Export To Excel: How to Resolve this issue?
