Helpful Excel Formulas for QuickBooks Online Imports
When preparing your files for import to QuickBooks® Online (QBO), there are several formulas that are easy to use to clean up the data in your file prior to import. Here are several of these, along with examples for practical application.
Concatenate (Combining Multiple Fields into One Field)
Often, the data in Excel is presented in multiple columns, whereas in QBO, all the data resides in one field.
In the screen shot below, the Excel file has the name in two fields; we are combining them into one field to match the Display Name field.
In QBO, if you are using parent-child relationships, such as a Job under a Customer, this data needs to appear in one column with a colon between the customer and job. This is demonstrated in the screen shot below.
Left/Right Functions (Trimming data from the cell)
This function is helpful if you only need certain characters from an Excel file. For instance, in the screen shot below, we needed to parse out just the account number.
If certain data is not present in the import file, you can add columns to calculate this data based on data that exists in other columns.
For example, on a customer list import, if you want to add the sales tax item, you could do an IF function based on the state. Let’s say that your company is based in Florida and your sales tax item in QBO is FL Tax.
Convert Signs on Numbers
A common question I see on the Intuit® Community Forums is that when importing csv files the signs for bank or credit card activity is reversed in QBO.
If you want to learn more about Excel formulas, you can check out Microsoft’s Excel training online.