Meet Dana, a made-up owner of a small landscaping company. Dana's books live in a spreadsheet with 14 tabs, named things like "2022 FINAL," "2022 FINAL v2" and "DO NOT DELETE." It worked for five years. It works less well now that there are eight employees, two trucks and a lender who wants financial statements.
So Dana decides to move to QuickBooks and, very reasonably, wants to bring everything. Five years of rows. Every mulch delivery since 2021.
That instinct causes most of the pain in this kind of move. This guide explains what to bring when you move from spreadsheets to QuickBooks, what to leave in the spreadsheet where it belongs, and how to avoid the duplicate mess that shows up when old rows meet a new bank feed.
Why importing every old transaction backfires
It feels thorough. In practice, importing years of spreadsheet rows tends to create three problems.
First, the old data rarely fits QuickBooks cleanly. Spreadsheet rows don't know about customers, vendors, accounts receivable or the difference between a transfer and an expense, so each row needs cleanup before it means anything.
Second, the imported history has to reconcile. If you bring in 2022, someone has to reconcile 2022 bank statements against it, or the balances are a guess.
Third, duplicates. You import March rows from the spreadsheet, then connect the bank feed, which offers to download recent history. Now March is in there twice. People spend weeks un-picking this, and it's the most common question we hear about an Excel to QuickBooks migration.
What to bring over (and what to leave behind)
Bring over:
- Opening balances for every balance sheet account as of the day before your start date: bank accounts, credit cards, loans, fixed assets and owner equity.
- Open invoices (what customers still owe you), entered individually so you can apply payments to them.
- Open bills (what you still owe vendors), entered individually.
- Customer and vendor lists, which QuickBooks can import from a spreadsheet file.
- Products and services, if you sell them.
- Payroll year-to-date figures, if you're switching payroll mid-year. Your payroll provider will guide this part.
Leave behind:
- Individual transactions from before the start date.
- Paid invoices and paid bills from old years.
- Tabs with names like "scratch."
Keep the spreadsheets, though. Save them somewhere permanent and read-only. They're your archive for those years, and your CPA may want them. The prior years' tax returns already reflect those numbers, so they don't need to live in QuickBooks too.
Picking a start date for your QuickBooks migration
The start date is the most important decision in a QuickBooks migration, and the cleanest options are:
- The first day of your fiscal year. Everything for the current year lives in one place, which makes year-end simpler for you and your CPA.
- The first day of the current quarter, if you're far into the year and don't want to re-enter months of activity.
Avoid starting mid-month. You'll have half a bank statement in each system and nothing that reconciles cleanly.
If you start partway through the year, the months before the start date stay in the spreadsheet, and your CPA will need both at year end. Tell them early.
A worked example: Dana's April 1 switch
Dana picks April 1 as the start date. On March 31:
| Item | Balance |
|---|---|
| Checking (per March bank statement) | $38,420 |
| Business credit card (per statement) | $4,105 owed |
| Truck loan (per lender statement) | $41,600 owed |
| Two trucks (at original cost) | $96,000 |
| Open customer invoices (11 invoices) | $22,750 |
| Open vendor bills (4 bills) | $6,310 |
Checking and the card get their March 31 statement balances as opening balances. The 11 invoices and 4 bills are entered one by one with their original dates and amounts, so April payments match against them. The truck loan and trucks go in as journal entries, with the CPA's input on accumulated depreciation (the depreciation already taken on those trucks).
The difference between what Dana owns and owes lands in equity. Done carelessly, it sits in Opening Balance Equity forever. Our article on opening balances in QuickBooks explains how to clear that account into the right equity accounts.
When Dana connects the bank feed, the download is set to start on April 1. No March rows, no duplicates. The April statement reconciles on the first try because March 31 matched the statement to the penny.
Set up before you import anything
Order matters. Before entering opening balances:
- Build the chart of accounts. Resist recreating every spreadsheet column as an account. Our guide to a chart of accounts for small business shows a sensible size.
- Set company settings: fiscal year start, accounting method, sales tax if you collect it.
- Add customers, vendors and products.
- Enter opening balances and open invoices and bills.
- Connect bank feeds last, starting from the start date.
Our walkthrough of QuickBooks Online setup for small business covers each step in this order, including the settings people skip and regret.
Questions owners ask
Can I import my Excel transactions into QuickBooks?
QuickBooks can import bank transactions and lists from spreadsheet files. For a move from spreadsheets, though, it's usually cleaner to bring opening balances and open items instead of years of old rows, which then need reconciling and often end up duplicated by the bank feed.
How do I avoid duplicate transactions when I connect my bank?
Choose a bank feed download date on or after your QuickBooks start date. Anything before that date should exist only as part of the opening balance, not as individual transactions.
What date should I start using QuickBooks?
The first day of your fiscal year is cleanest. If that's months away, the first day of the current quarter works. Avoid a mid-month start, since neither system will hold a complete statement period.
Want the move done right the first time?
Our bookkeeping and payroll team handles spreadsheet-to-QuickBooks moves as part of setting up your books: start date, opening balances, open items and a first reconciliation that ties. Request a consultation to talk through your spreadsheets (yes, including "2022 FINAL v2"). If you'd like a planning range first, try the Scope & Investment Estimator.