AppGarden Import Cleaner for QuickBooks logo AppGarden

Import Cleaner for QuickBooks™

QuickBooks™ bank file: Credit, Debit and Amount columns that clash, in Excel™

By Paul Kemp · Published

Bank uploads to QuickBooks™ take one of two shapes: three columns (Date, Description, Amount) or four (Date, Description, Credit, Debit). Files that mix the two, or that leave one side out, may be a reason an upload does not go through. Confirm the current layout in Intuit's own help for your plan.

The mistakes to look for

Fix by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy.

  1. Decide on three columns or four, based on how your bank export is laid out.
  2. For the four-column layout, rename the headings to exactly Credit and Debit.
  3. For the three-column layout, combine them into one signed Amount: =N(C2)-N(D2) assuming Credit is in C and Debit in D and that you want credits positive. Choose the sign to suit the file, and paste as values.
  4. Clear zeros: select the columns and use Home → Find & Select → Replace, find 0, tick Match entire cell contents, and replace with nothing. Do not skip that tick, or you will damage values like 10.
  5. Confirm the amounts are numbers, not text.
  6. Save as CSV and look at it in a text editor before upload.

Check the layout in the pane

Import Cleaner for QuickBooks™ checks zeros in Amount, Credit or Debit (QI-01), headings that include the word "Amount" (QI-02), and Amount used with Credit or Debit, one-sided columns and missing columns (QI-06). The scan and every explanation are free; applying the fixes is the paid part. It edits the workbook only and never connects to QuickBooks™.

See Import Cleaner for QuickBooks™ for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. QuickBooks is a trademark of Intuit Inc. Import Cleaner for QuickBooks is an independent product, not affiliated with or endorsed by Microsoft or Intuit Inc.