QuickBooks Online (QBO) is a great user friendly environment with anytime, anywhere access and great built-in automation to perform your accounting. As with most software, setting up the file for use is the part that takes the longest. So, there are some shortcuts you can use, such as building your lists in Excel and then importing them into QBO.
When importing into QBO, there are some limitations and also opportunities to consider. Here are my top five:
- Know what is able to be imported into QuickBooks Online. Built into the Importing Tool inside of QBO are 5 areas you can import data into:
- Customers: Contact info, names, addresses, new customers, etc.
- Vendors: Contact info, names, addresses, new vendors, etc.
- Chart of Accounts: Names, types, detail types, etc.
- Products & Services: Names, descriptions, prices, account mappings, etc.
- Bank Transactions: Connect to your bank to download bank account and credit card account activity to match to your existing transactions or quickly add them to your file. See Linking Bank Accounts and Credit Cards for ways to setup Bank Transactions downloading into QBO.
To access the Import list and read more about each import type, click Tools –> Import Data
- Build your Excel Spreadsheet to optimize the Import experience. Your data should be built such that each column is a data field in QBO and each row is a new entry. Some things to note:
- Character Limits: Some of the data fields, such as the Name field, have limits on how many characters can be used for that field. The Name field is 100 characters. Instead of cutting off the end when you import, QBO will skip any entry with a Name that is too long and ask you to resolve it.
- Formula-based Fields: If you use a formula to create a data field, then upon importing, the actual formula is what imports, not the result of the formula. Therefore, in excel, go ahead and use your formula, but copy the column and Paste VALUES into a new column to be used for importing. If you then have updates in your formula column, you’ll need to Copy and Paste Values again.
- Column Headings: It does not matter what order your columns are in. When you are importing, QBO lists the data fields available and you tell it which column in excel should be imported into that field. I recommend naming your columns in a way that you know exactly which fields to use in import.
It is fine to start the import process, realize your columns need work, cancel out, fix your excel file, and start the import process again. Nothing is final until you get through the entire process.
- Understand additional characteristics that may require or save additional time beyond importing. There are limited fields available for each import, so if you are wanting to take your lists to the next level, here are some things you should be aware of:
- Hierarchy limitations: You cannot import a list that has a hierarchy built into it. This is process that you will have to manually do after you’ve imported all of the list elements. For example, if you want a customer to be a sub-customer of another, you’ll import it as its own customer, then go into the customer list, select the customer, edit, and click the box “Is Sub-customer” and choose the parent manually. Click Save .
- Inventory opportunity: Use excel to turn on your inventory function inside of QBO. Inventory allows you to track how many of an item you current have on hand. You can start tracking inventory by exporting your current Products & Services list to excel, adding a column for Qty on Hand, Inventory Asset Account, and Qty as-of Date (if you leave this column out it will default to today’s date). Then, upon import, anything with a qty will have the box “I track this” checked. If you have zero qtys on items that you expect to track in the future, you can manually click the “I track this” box. The alternative to using the import tool is to click on each product, click I track this box, and enter the qty, date, and inventory asset field one by one. Also, in order to track inventory in the first place, you need to have the preference set.
The opportunity for time savings using Importing is going to bigger and bigger the longer your lists are. If you only have 5 or 10 customers or products, then it will be faster to just manually make your updates. However, if you have 100 or more, think of the time savings by allowing the technology to do the work for you.
- Learn to address the errors that you might encounter when importing. You are using a wizard throughout the import process and QuickBooks Online helps you along the way. If there is anything that is not able to be imported, it tells you exactly why and gives you an opportunity to fix it in the wizard if you choose.
- Review Data: Take a minute to glance over the table of data that is about to be imported. If all fields are eligible to import (no character limitations, etc) then you cannot undo what you’re doing. Click Import.
- Import Summary: A summary of the ones imported and any errors are listed, grouped by reason for the error.
- Fix Errors: QBO tells you reasons why the field has an error. You can quickly fix the error right here and click “Re-import modified records”. It will keep you in the Import Summary field until you either click away from it or have addressed all of the errors. In the screen shot below, I fixed 2 of the errors and see how the Summary has changed.
- Ignore if you don’t want to fix: Instead of fixing the error, you might decide that this customer is either a duplicate or has no need to be part of your file. Or you might decide to just add manually. You can select any you want to ignore. This is useful if you have a large list of errors so that you can narrow down to just the ones you want to fix.
Addressing the errors is easy and efficient. Re-size any columns if you can’t see the data you need to fix.
- Finally, look at the Apps inside QBO to see if you have other opportunities for importing. It could be really beneficial for your business to use an app for some of your business processes. In the App center, each of the Apps import data into QuickBooks Online. Here are some examples:
- TSheets: See Details Use an app to record employee time and import into the timecards in QBO, which can be used to process payroll.
- SmartVault: See Details Use an app to link all of your documents electronically to the transactions or customers they belong to. Quickly organize and retrieve anything you need to review or distribute while providing direct links in your accounting to minimize errors and questions.
- Transaction Pro Importer: See Details If you have data from another system that you want to import into QBO, including transactional data, see if Transaction Pro Importer can provide the tools you need to automate those processes. It expands the power of importing into QuickBooks Online.
Unwrap the power of QuickBooks Online through importing data to save you time and money!