You are using an unsupported browser. Please update your browser to the latest version on or before July 31, 2020.
close
Home > Tips and Tricks > Useful Excel Formulas and Tips to Use with Imports and Exports
Useful Excel Formulas and Tips to Use with Imports and Exports
print icon

Transaction Pro Importer and Transaction Pro Exporter both support the use of Excel files. 

 

Below are some tips and common features that can be used with Excel import or export files:

 

  1. Drop Down Lists: This can be helpful when having users do data entry and want to be sure that a valid customer, item etc is used. Read more on how to create a drop down list HERE.
  2. ROUND Function: This can be helpful when importing Journal Entries, as QuickBooks will round each line, which can cause out of balance situations. Read more about this formula HERE.
  3. LEFT and RIGHT Functions: These can be used to easily select data from the beginning or end of a cell. For instance, if the file contains both the Account Number and the Account Name, a formula can be used to extract only the Account Number. Read more on how to use the LEFT function and the RIGHT function.
  4. CONCATENATE Function: Importer has a concatenate function to allow data columns to be combined, which additional information about can be found HERE. However, if fields that are on two different sheets in the import file need to be combined, this function can be used in Excel to combine the fields into one cell. Read more on how to do this HERE.
  5. Fill Data Automatically Feature: Importer requires a REFNUMBER on every transaction line. If the import file does not have a column that does this, a column can be added for REFNUMBER and the Fill Data Feature in Excel used. You can read more about it HERE.
  6. IF Statement Function: An example as to when to use the IF function is to add a QB sales tax item to the import file. Often times, the e-commerce websites do not provide any tax information other than the amount. The IF statement can be used to look at either the amount, rate, or state in identifying what the sales tax item should be. More information about this function can be found HERE.
  7. Pivot Tables: These tables can be used in Excel to summarize data or to rotate the axis in Excel, if the data is in the incorrect format. There are many articles for this that can be found on Microsoft's Website.
  8. Paste Special Transpose: This feature is helpful if the orientation of the axes needs to be switched (ie. make rows into columns). Read more about it HERE.
  9. Changing the ENTER Key Behavior: By default in Excel, if the ENTER key is selected, the cursor will move down one row. However, the cursor can be set to behave in a different way such as moving to the right when the ENTER key is selected. Find out how to do this HERE.
  10. FIND and REPLACE: This can be helpful if there are some data entry errors in Excel. For example, the dates in the import file need to be changed from 12/31/2019 to 01/01/2020, this function can be used to correct the information in Excel. Read more on how to use this feature HERE.
Feedback
1 out of 8 found this helpful

scroll to top icon