QuickBooks Payroll Import from Excel: The Journal Route

A QuickBooks payroll import from Excel works as a journal entry, not as paychecks. Build one sheet per pay date from the provider's payroll report: gross wages and employer taxes as debits, withholdings and deductions as liability credits, net pay as a credit. Save it as CSV and import it through QuickBooks Online's journal entry import.

Paychecks or a journal entry: what a QuickBooks payroll import from Excel creates

A QuickBooks payroll import from Excel creates journal entries. Intuit's guidance for a client paid through an outside provider records each payroll as a journal entry, and QuickBooks Online's import screen takes journal entries. So each pay date arrives as one balanced entry against wage, tax, liability and cash accounts.

Intuit's US article Manually enter payroll paychecks is written for businesses that run payroll through another service and calls those checks third-party paychecks. Its method is a journal entry dated the paycheck date, with debits to wage and employer tax expense, credits to payroll liability accounts, and a credit for net pay from the checking account. It says employees can be combined into one entry or entered one per employee.

The same article states the limit that matters most. Entering paychecks this way tracks payment totals but does not produce employee W-2 data, so year-end forms come from the payroll provider. The QuickBooks entry is the books' record of the payroll. The provider remains the record of each employee's pay.

That is the case for importing a summary journal per pay date rather than a line per employee. The books need the accounts to be right on the date the money moved. Employee detail already lives in the provider's register, and copying it into QuickBooks adds rows without adding information the ledger uses.

A per-employee entry still has a place. A client that allocates wages by department or job may want one line per employee per class. In that case the journal has more rows, but the columns, the accounts and the balancing rule stay the same.

Building the payroll journal sheet for a QuickBooks import

The payroll journal sheet for a QuickBooks import is one block of rows per pay date, in the columns the journal import reads. Every row names one account and carries a debit or a credit. The block balances: total debits equal total credits, so debits minus credits is zero.

Intuit's US article Import journal entries lists the required columns: Journal No., Journal Date, Account Name, Journal/Description, Debits and Credits. Give every row of one pay date the same journal number. Use the pay date as the journal date and a number that encodes it, so a duplicate pay date is easy to spot.

Account names must match the client's chart of accounts. Intuit's manual paychecks article uses example accounts named Payroll Expenses: Wages and Payroll Expenses: Taxes, and liability accounts for Federal Taxes (941/944), Federal Unemployment (940), state SUI/ETT and state PIT/SDI. Treat those names, and the mapping in the sample below, as the firm's starting point, adjusted to the client's own chart. The import's formatting rules for subaccounts and account numbers are covered in ledgers and journal entries in a QuickBooks Online export.

How each line of a provider report maps to a debit or credit is explained in payroll report extraction into a journal entry layout. The sheet below uses that logic, with one change for the bank feed: net pay goes to a clearing account instead of straight to checking.

A sample journal sheet for one pay date

Example Bakery Co. is a fictional client paid through an outside provider. All amounts, account names and the journal number are examples for one semi-monthly pay date.

Debits minus credits: 13,014.00 minus 13,014.00 is 0.00. Add a formula cell beside the block that shows this difference, and do not save the CSV until it reads zero. The totals row is for the check only. It is not a journal line, so remove it before saving the CSV.

Net pay checks the withholding lines. In this example, gross wages of 12,000.00 less federal withholding of 1,320.00, employee social security of 744.00, employee Medicare of 174.00, state withholding of 480.00 and 401(k) deferrals of 600.00 leaves 8,682.00. If the provider's register shows a different net pay, a deduction is missing from the sheet.

Excel can change values on the way to CSV. Dates may reformat, and long numbers may lose digits. The post on XLSX or CSV for accounting exports covers what Excel does on save and open.

Importing the payroll journal into QuickBooks Online and Desktop

Importing the payroll journal into QuickBooks takes a different route in each version. QuickBooks Online imports a CSV of journal rows through Settings, Import Data, Journal Entries. QuickBooks Desktop has its own import tools, and Gusto's Desktop integration hands over payroll as an IIF file. A version without a journal import still takes the same entry typed by hand.

In QuickBooks Online, Intuit's import article gives the steps: open Settings, select Import Data, then Journal Entries. Browse to the CSV, match each header to a QuickBooks field, and if the screen reports an error, edit the file and try again before starting the import. The article lists the Online plans it applies to, from Free and Simple Start through Advanced. If "Warn if duplicate journal number is used" is turned on, some entries that reuse a number cannot be uploaded, and Intuit says to turn that preference off before importing. With the warning off, nothing stops the same pay date going in twice, so check the journal numbers against last period's before each import.

If the import screen is not available for a client, the manual paychecks article gives the hand-entry path: select + Create, then Journal Entry, set the paycheck date, and enter the same debit and credit lines. The sheet still does its job as the worksheet and the balancing check.

In QuickBooks Desktop, Intuit's Import and export data in QuickBooks Desktop article lists general journal entries among the transaction types it handles and warns that an import cannot be undone, so back up the company file first. A journal in IIF form has its own header rows and line markers; the layout is shown in IIF header rows and the TRNS, SPL and ENDTRNS lines.

Firms moving a client from Desktop to Online should settle the payroll accounts before the move, so pay-date journals land on the same names afterward. The steps for that move are in how to convert QuickBooks Desktop to Online. For other ledger data going into Online the same way, see ledgers and journal entries in a QuickBooks Online export.

Can a Gusto or ADP payroll export go straight into QuickBooks?

A Gusto or ADP payroll export goes straight into QuickBooks only through a provider integration, where one exists. A report downloaded from the provider, such as a payroll register or summary, still has to be reshaped into journal rows before the QuickBooks import accepts it. The provider's report has the numbers; it rarely has the import's columns.

Gusto documents two integrations. Its help article Integrate with QuickBooks Online says that, once connected, Gusto builds and syncs a detailed journal entry into QuickBooks Online each time payroll runs, using the client's own chart of accounts. Its article Integrate with QuickBooks Desktop says payroll data is exported as an IIF file and uploaded into QuickBooks Desktop versions that support IIF imports. When a client uses either one, the firm's job is checking the synced entry, not building it.

For ADP and other providers, start with that provider's own documentation on accounting exports. Intuit's manual paychecks article names ADP and Paychex as examples of third-party payroll, and its journal method applies to any provider's report.

When no integration is connected, the reshaping looks like this:

• Pick the summary, not the register. A payroll summary or journal-style report already totals each wage, tax and deduction for the pay date.
• One row per account. Combine report lines that post to the same QuickBooks account, such as employee and employer social security into the federal liability account.
• Check the net. Net pay on the sheet must equal net pay on the report.

A client may also switch providers mid-year, which leaves two report formats for one year. The quarterly totals still need to agree with the filed returns; payroll report extraction covers that comparison.

The double-counted net pay mistake in QuickBooks payroll imports

Double-counted net pay happens when the payroll journal credits checking for net pay and the bank feed then records the same withdrawal again. Checking falls twice for one payroll, and wages or another account picks up the second entry. A payroll clearing account stops it by giving the journal and the bank feed a shared meeting point.

Intuit's article Match your bank and credit card transactions explains the two choices for a downloaded transaction. Match is for a record already entered in QuickBooks, and matching prevents duplicate entries. Categorize creates a new record. A payroll journal that already credits checking has recorded the withdrawal once. If the bank line is then categorized, a second record is created for the same money.

The clearing account removes the guesswork. The journal credits Payroll Clearing, an account that holds net pay until the money leaves the bank. When the bank feed shows the net pay withdrawal, it is categorized to Payroll Clearing. The journal credit and the bank debit offset, and the account returns to zero. Checking falls once, on the date the bank actually paid.

Clearing accounts also catch timing. If a provider debits the client's bank before the pay date, or in more than one withdrawal, such as net pay first and taxes later, the bank lines and the journal no longer share a date or an amount. With a clearing account per withdrawal type, each bank line lands against the journal amount it belongs to. A clearing balance that is not zero after the bank lines arrive points to a missing or doubled withdrawal.

Whether to use a clearing account, and which account type it takes in the chart, is the firm's call for its client. Intuit's own example credits checking directly, so a firm that follows it has to make sure the bank line for net pay does not create a second record.

Does the imported QuickBooks payroll tie to the payroll register?

Imported QuickBooks payroll ties to the payroll register when four totals agree for the pay date: gross wages, employer taxes, total withholdings and deductions, and net pay. Run them after each import. A difference in any of the four means a line is missing, doubled or posted to the wrong account.

The comparison for the sample pay date:

The withholdings line needs care. The liability credits in the sample total 4,332.00, which includes the 1,014.00 employer share. Subtract it before comparing with the register's employee withholdings, or the line will look 1,014.00 out when nothing is wrong.

Then extend the tie-out over time. Each liability account should fall to zero when the matching deposit is paid, and the quarter's wage total should agree with what the provider filed. Year-end forms come from the provider, as Intuit's article says, so the year-end check is the QuickBooks wage total against the provider's annual totals.

The provider report is also a client document the firm has to collect each pay period. Schema CPA reads payroll provider reports and pay stubs and exports the figures to Excel or CSV today. It has no QuickBooks or Xero connection; QuickBooks Online and Xero connections are on the roadmap. The document types on the homepage list payroll alongside the other categories it reads. A client moving to Xero instead has its own import path, covered in how to import Excel into Xero.