Most of the data imported into QuickBooks with Transaction Pro Importer comes from third-party software such as billing systems, point of sale (POS) systems, time clocks or payroll providers. However, the data provided by these systems may not be ready to import into QuickBooks without some manual manipulation. We’ll take a look at the popular Excel formulas for importing data into QuickBooks.
Excel Formulas for Importing Data
VLookup is a cross-reference formula in Excel that is used to lookup a value in a column and then return the value from the corresponding row in another table. It can be used with text and numbers, and is commonly used for list items such as Customer, Vendor, Employee or Item names. For example, a Customer may be set up as one name in your POS system but have a somewhat different name in QuickBooks. The names must match to QuickBooks exactly when importing to avoid new names being setup. Therefore, it is important to update your import file with the correct matching QuickBooks name, and the VLookup formula is a quick way to do this.
The Concatenate formula is used when the first and last names are in separate columns, but they need to be in the same column to match to QuickBooks during import. This formula allows you to combine text data from one or more columns into a single column. You can also add punctuation, spacing or additional words to the formula.
Just as Concatenate allows you to combine data from multiple columns into a single column, the Text to Columns formula allows you to separate text in a single column into multiple columns. This formula is often used for addresses that are all in one column when the street address, city, state, and zip need to be separated into their own columns for importing.
Basic math formulas in Excel can also prove quite useful in preparing a file to import. Sometimes the amount may be the wrong sign, such as when importing Vendor Credits, and the amounts are negative numbers in the import file. QuickBooks automatically implies the negative when importing Vendor Credits; thus, the amounts need to be positive numbers to import. By simply multiplying a column by -1, it will reverse the sign, allowing the numbers to be imported correctly.
Ever had to deal with a zip code containing a leading zero that was dropped by Excel? If so, then using the Custom option with cell formatting will become your favorite feature of Excel. You can format the zip code column to always include five digits by entering a custom formatting of five zeros. If a cell only has four digits, it will add the zero to the front of the number so that the cell correctly contains five digits instead.
These are just a few of the many useful formulas that can be used to prepare your data prior to importing into QuickBooks with Transaction Pro Importer. Our YouTube Channel has a video of these five formulas and options to help you see just how helpful they are.
If you have any questions about using Excel formulas to prepare your import files or any other Transaction Pro questions, please email support@transactionpro.com. You may also post your questions in our Transaction Pro Forum.