Excel Template for QuickBooks Import: Bank CSV Layouts

Quick Answer: A QuickBooks bank import template is a sheet with a single header row and either three columns (Date, Description, Amount, with money out as negative numbers) or four columns (Date, Description, Credit, Debit, with positive numbers only). Use one date format for the whole sheet, leave empty amounts blank rather than zero, keep it under 1,000 rows, and save it as CSV before uploading to QuickBooks Online.

There is no special Intuit file to download for bank transactions; the "template" is simply a spreadsheet laid out the way QuickBooks Online's upload screen expects. Get the columns, headers, dates and amounts right and the file maps on the first try. Get any of them wrong and you see shifted dates, every amount as a deposit, or a rejected upload.

What are the two layouts QuickBooks accepts?

Intuit's upload instructions allow exactly two shapes. Pick the one closest to how your bank already exports.

Layout A: three columns, signed amounts

Row 1 is the header row. Every row below it is one transaction.

Positive numbers are money into the account, negative numbers money out. For a credit card account the meaning of "in" and "out" is from the card's point of view: a purchase is money out (negative), a payment to the card is money in (positive).

Layout B: four columns, split amounts

Credit is money in, Debit is money out, from your side of the account. Both columns hold positive numbers. Each row has a value in exactly one of the two.

When saving to CSV, Excel writes numbers the way the cells display them, so set the amount columns to a plain number format with no thousands separator before saving, or a figure like 1,912.33 can reach the file with a comma inside it.

What should the headers be called?

Use plain, short names. QuickBooks lets you match columns to its fields during upload, so the exact wording is less important than avoiding a few traps Intuit lists:

• Do not write "Credit amount" or "Debit amount"; remove the word "amount" from those two headers.
• Keep the header in row 1. Bank exports often put the account name, statement period or a disclaimer above the headers; delete those rows.
• One header row only. Do not merge cells or add a second line of sub-headings.
• No extra columns. Running balance, check number, category and memo columns from the bank should be deleted (or moved to another sheet) before saving.

Which date format works best?

Intuit's guidance is to enter every date in the same format, and its page recommends dd/mm/yyyy. US users commonly keep their bank's mm/dd/yyyy instead. Either works, provided:

• every row uses the same format;
• you choose the matching format when QuickBooks asks during mapping;
• the cells contain only the date, with no weekday (if the bank writes "Mon 01/12/2026", split the weekday into its own column and delete it).

Excel often stores dates as numbers and displays them in your regional format, which can differ from what ends up in the CSV. To force a known format, add a helper column with =TEXT(A2,"mm/dd/yyyy"), copy it, paste as values over the original column, and delete the helper. Then open the saved CSV in a text editor to confirm.

The broader problem of day-first versus month-first statements is covered in understanding bank statement date formats.

How do you convert between signed and split amounts?

Banks export in either style, and you may need the other.

From split (withdrawal and deposit columns) to signed, with withdrawals in column C and deposits in D:

=IF(D2<>"",D2,-C2)

From signed to split, with the signed amount in C:

• Credit: =IF(C2>0,C2,"")
• Debit: =IF(C2<0,-C2,"")

After either conversion, paste values over the formulas so the CSV contains numbers, not formulas. Also watch for:

• Negatives shown in parentheses, like (61.40). Convert them to -61.40.
• Currency symbols and "CR"/"DR" suffixes. Strip them with Find and Replace.
• Zero amounts. Intuit asks that cells containing only 0 be left blank.

What else does the template need to pass?

The description rule surprises people. Bank descriptions are full of store numbers, reference codes and card digits. Stripping them also makes QuickBooks bank rules match more reliably, since "Shell Oil" matches every visit while "Shell Oil 5521" matches one station. A quick way in Excel is to keep the original description in a spare column and build a cleaned version with Find and Replace or a lookup table of payee names; Excel's XLOOKUP function is handy for mapping raw bank text to a clean payee list. Delete the spare column before saving.

A pre-upload checklist for the finished sheet

Run through this before you save the CSV:

1. Row 1 holds the headers and nothing sits above them.
2. Only three or four columns remain, in the order Date, Description, then Amount or Credit and Debit.
3. Every date uses the same format, with no weekday text.
4. Amounts are plain numbers: no currency symbols, parentheses, "CR" or "DR".
5. No cell contains a lone zero.
6. No totals, subtotals or opening balance rows at the bottom.
7. The row count is 1,000 or fewer.
8. The date range does not overlap transactions already in the QuickBooks account.

Save, then reopen the CSV in a plain text editor. What you see there is exactly what QuickBooks will read.

Is there a template for other QuickBooks imports?

Yes, but each data type has its own. For lists (customers, vendors, products) and transactions such as invoices and journal entries, QuickBooks Online's Import Data screen accepts Excel files directly and offers sample files for some types. Intuit's import questions page says those spreadsheets must have fewer than 1,000 rows and that the first row is used for mapping. Those layouts are different from the bank template above; see importing Excel into QuickBooks Online for what each screen takes.

QuickBooks Desktop does not accept a bank template in CSV form at all; it needs a .QBO Web Connect file, an IIF file or, in the Accountant and Enterprise editions, rows pasted into Batch Enter Transactions.

Skipping the template

If your starting point is a PDF statement, building this sheet by hand means retyping every line. Two shortcuts:

• Download a CSV from online banking for the same dates, if the bank offers it. It usually needs only header and column cleanup.
• Convert the PDF. A statement converter produces the rows for you. QuickBankConvert's CSV has one signed amount per transaction, so it fits Layout A after you drop the columns QuickBooks does not use; its QBO output (Plus plan and above) needs no template at all, because a .QBO file maps itself.

Once the sheet is ready, the upload itself is walked through in importing bank transactions into QuickBooks Online.

Related guides

• The QuickBooks Online upload screen, step by step
• Everything QuickBooks Online imports from Excel
• Day-first and month-first statement dates
• Statement PDF to CSV