Upload Invoices to QuickBooks Online from a Spreadsheet

To upload invoices to QuickBooks Online, save them as a CSV file with one row per invoice line, then use Settings, Import data, Invoices. Every row needs an invoice number, customer, invoice date, due date and amount. Customer names must match your customer list, and an account with sales tax set up cannot use this import.

Which QuickBooks Online plans can upload invoices from a file?

The invoice import is part of QuickBooks Online Essentials, Plus and Advanced, according to Intuit's guide to importing multiple invoices at once. The plan is rarely the obstacle. Sales tax is: if your company has sales tax set up, Intuit says you can't import invoices this way.

That sales tax rule catches a lot of people, because turning on sales tax is one of the first things a new QuickBooks company asks you to do. If your account already charges tax on invoices, the import screen is not the route for you. The choices left are to enter the invoices by hand, to use an app from the QuickBooks app store that writes invoices through Intuit's connection, or to bring in only the invoices that carry no tax before you switch tax on.

Limits on one upload

Intuit sets two ceilings on a single file. A spreadsheet can hold up to 1,000 rows, and one import can carry at most 100 invoices. A year of invoices for a busy business will not fit in one go, so plan to split the file by month or by quarter. Splitting has a second benefit: when a file fails, you only have a month to search through.

What the import cannot carry

The same Intuit page rules out negative charges. A discount line, a refund line or a credit memo cannot ride inside an invoice upload. Leave those rows out, upload the invoices at their full amounts, and record discounts and credits afterwards. Credit memos have their own route, covered in the guide to importing credit memos into QuickBooks Online.

Invoice CSV columns QuickBooks Online requires

An invoice CSV for QuickBooks Online needs five values on every row: invoice number, customer, invoice date, due date and item amount. Product or service, description, quantity and rate are optional extras. QuickBooks marks required fields with an asterisk on the mapping screen, and any column you don't have can be set to "Not applicable."

The quickest way to get the headers right is to download the sample file from the import screen itself. Go to Settings, choose Import data, pick Invoices and select the sample CSV link. Paste your own data under those headers and you avoid a whole class of mapping problems.

A few notes on each required column:

• Invoice number is the key that ties rows together. Two rows with the same number become one invoice with two lines.
• Customer is the display name exactly as it appears in your customer list.
• Invoice date and due date must use one date format across the whole file. You tell QuickBooks which format during the import.
• Item amount is the line total, not the invoice total. QuickBooks adds the lines up for you.

Products and services on each line

If your file names a product or service that QuickBooks doesn't know, Intuit says it fills the line with a generic item called "sales." The amount still lands, but your sales-by-product reports lose the detail. If you care about those reports, create the items first. The guide to importing products and services into QuickBooks Online shows how to load a whole price list in one file.

One spreadsheet row per invoice line item

A spreadsheet for this import lists invoice lines, not invoices. An invoice with three lines takes three rows, and each of those rows repeats the invoice number, customer, invoice date and due date. Only the product, description, quantity and amount change from row to row. QuickBooks groups the rows by invoice number when it builds the invoices.

Here is an example file with two invoices. The values are made up for illustration.

Invoice 1041 comes out as one invoice for 805.00 with three lines. Invoice 1042 comes out as a single-line invoice for 400.00. The shared values on the three 1041 rows should be identical, so copy them down rather than retyping them. A retyped date or a customer name with an extra space is the kind of slip that is hard to spot on screen.

Building the rows from an invoicing app

Many people start from an export out of another invoicing tool. Those exports often put one invoice per row with the line items squeezed into a single text cell. That shape will not import. Split each line into its own row and fill the shared columns down. In Excel, a quick way is to sort by invoice number and use fill-down on the customer and date columns.

Invoices that only exist as PDFs

Sometimes there is no spreadsheet at all, only a folder of PDF invoices from an old system. Typing them into rows is one option. The guide to importing PDF invoices into QuickBooks Online covers the others and when each one is worth the effort.

Customer names that match the QuickBooks customer list exactly

The customer column must match a display name in QuickBooks character for character. "Harbor Dental" and "Harbor Dental Group" are two different customers to the import. When a name doesn't match, the row either fails or, if you ticked the option to add new customers, creates a second customer you then have to merge.

The import screen offers a checkbox to add customers that don't already exist. It is handy for a first upload into an empty company. In a company that already has a customer list, it is risky, because every spelling slip becomes a new customer record. A safer habit is to leave the box clear on the first try. Any row that fails on a customer name then points you to a spelling to fix.

A matching routine that works

Before you upload, export your customer list from QuickBooks and compare it with the names in your file. A simple lookup in the spreadsheet does the job: any name that returns no match needs fixing. Watch for these small differences:

• A trailing space after the name.
• "and" in one place and "&" in the other.
• "Inc." with a period in QuickBooks and "Inc" without one in the file.
• Sub-customers, which QuickBooks may show with the parent name in front.

Currency on new customers

If you use multicurrency, set each customer's currency before you upload. Intuit notes that a customer created by the import without a currency is assumed to use US dollars.

Stepping through the invoice import screens

The invoice import in QuickBooks Online runs in one short sequence: choose the file, decide about new customers, map the columns, pick the date format, then review and start. It takes a couple of minutes once the file is clean. The mapping step is where most of the thinking happens.

1. Select Settings, then Import data.
2. Choose Invoices, then the option to upload a file.
3. Decide whether to tick the box that adds new customers.
4. Browse to your CSV file and select Next.
5. Match each column header in your file to a QuickBooks invoice field. Set unused fields to Not applicable.
6. Pick the date format your file uses, such as MM/DD/YYYY, and select Next.
7. Review the summary and select Start import.
8. Read the result screen. If some rows failed, note the reasons before you close it.

That last step matters. The result screen lists the reasons for any failures, and it is the easiest place to learn what went wrong. Take a screenshot if the list is long.

Why do some invoice rows fail to upload?

Most rejected invoice rows fail for one of a handful of reasons: a customer name that doesn't match, a date in the wrong format, a missing required value, a negative amount, or a file that goes over 100 invoices or 1,000 rows. Fix the cause in the spreadsheet, remove the rows that already imported, and upload the rest.

Use this checklist on the failed rows:

• The customer name matches the customer list exactly.
• Every date in the file uses the same format, and that format was chosen on the import screen.
• No required cell is blank on any row, including rows two and three of a multi-line invoice.
• No amount is negative, and no amount has a currency symbol or text in the cell.
• The invoice number isn't already used by an invoice in QuickBooks.
• The file has 100 invoices or fewer and no more than 1,000 rows.
• The file is saved as CSV, not as an Excel workbook.

The partial import trap

The mistake that causes the most clean-up is uploading the whole file again after a partial failure. The invoices that worked the first time are already in QuickBooks, so a second full upload doubles them. Always cut the successful rows out of the file before the retry. If you do end up with duplicates, delete the extra invoices from the customer's transaction list before anyone sends them.

When the import is the wrong tool

If you only have a handful of invoices, typing them in is often faster than preparing and checking a file. The upload earns its keep at around twenty invoices or more, or when the same shape of file arrives every month. QuickBooks Desktop users have a different set of tools; the guide to importing invoices into QuickBooks Desktop from Excel covers them.

Uploaded invoices and the customer payments that reach your bank

An uploaded invoice is an open receivable until a payment is recorded against it. When a customer pays, the money shows up as a deposit in your bank account, and you match that deposit to the invoice in QuickBooks Online. The match marks the invoice paid and keeps the income from being counted twice.

The order of work matters here. If you upload invoices and also bring in the bank deposits for the same period, each deposit should be matched to its invoice, not categorized as income. Categorizing it as income would record the sale a second time. When the bank lines arrive, QuickBooks often suggests the match itself if the amount and customer line up.

Getting the bank side in

The deposits come from your bank feed or from a bank file you upload. If your bank offers a QuickBooks download, that is the simplest source; the guide to downloading a QBO file from TD Bank shows what those screens look like at one large bank. If all you have is a PDF statement, SimpleBankConvert is being built to turn it into a file QuickBooks Online can upload, so the payments for your imported invoices have something to match against.

A quick reconciliation habit

After each month's upload, run the open invoices report and compare its total with what your old system said was outstanding. If the two agree, the import is complete. If they don't, the difference usually points to a skipped file, a duplicated invoice or a discount line that was left out on purpose and still needs recording.