How To Import Bank Transactions Into QuickBooks: Excel & More
Tim is a Certified QuickBooks Time (formerly TSheets) Pro, QuickBooks ProAdvisor, and CPA with 25 years of experience. He brings his expertise to Fit Small Business’s accounting content.
Mark has researched and reviewed accounting software at Fit Small Business since 2019 and has developed an extensive knowledge of accounting software features and how unique business needs determine the best accounting software.
This article is part of a larger series on Accounting Software.
In this tutorial, we’re going to cover how to connect your bank accounts to QuickBooks Online. We’ll explain how to link your bank account to QuickBooks automatically as well as how to import banking transactions manually using a QuickBooks Online file or comma-separated values (CSV) file. You’ll need to import your transactions via a CSV file if you cannot locate your bank in QuickBooks Online or if you need to download older banking transactions.
This tutorial is part of our free QuickBooks tutorial series—designed to help you learn how to use QuickBooks Online, our top pick for best small business accounting software. We recommend following along with the tutorial in your own QuickBooks account. If you haven’t subscribed to QuickBooks yet, you have the choice of a 30-day free trial or 50% off for three months.
You can watch the video below or follow the step-by-step instructions that include illustrations.
Step 1: Add Your Checking Account to the Chart of Accounts
First, set up your checking account in the chart of accounts. To set up a new account, go to the Accounting menu in the left menu bar, select Chart of Accounts, and then click the New button on the top right side of the screen.
Preparing to create a new checking account
Fill in the new checking account screen with the required information. The account type must be bank, and the detail type must be checking. You must name the account, and we recommend the name of the bank plus “Checking Account.” If you have more than one checking account with the same bank, you might also include the last four digits of the account number. When you have entered all the information, click the green Save and Close button, and you’ll be returned to the prior screen with the new account selected.
Create a new checking account in QuickBooks Online
Step 2: Locate Your Bank
Select Banking from the left menu bar, and then choose the Banking dropdown.
Note: In the video, it says you should click on the Transactions in the left menu bar. In the latest QuickBooks Online version, select the Banking menu, and then choose the Banking dropdown.
Go to the Banking Center in QuickBooks Online
If you need to add a new bank account, click the Link account button to add your bank account, as shown below.
Connect a bank account to QuickBooks Online
After clicking Link account, a screen displaying a list of popular banks appears. Search for your bank by entering either the bank name or the bank’s website URL. Alternatively, you may click on the logo of your bank if it’s displayed on the screen. In the screenshot below, we select Chase for our fictitious company, Paul’s Plumbing.
Select your bank in QuickBooks Online
After selecting your bank, click the green Continue button to be taken to your bank’s sign-in page automatically.
Click Continue to sign in to your bank account
Step 3: Log Into Your Bank Account
QuickBooks will open a new browser window for you to sign in to your bank account, as shown below. Click Log in or Sign in to proceed.
Bank sign-in page opens in a new window
After signing in using the username and password that you set up for online access to your account, your bank might ask you to confirm that you want to share your banking information with Intuit and provide a summary of the information to be shared. If you agree, click the appropriate button on your bank’s confirmation page, and you’ll be taken back to QuickBooks.
Tip:
If you haven’t set up online access to your bank account yet, you must do so before completing Step 3. Coordinate with your bank on how to activate online access.
Step 4: Link Bank Accounts To Chart of Accounts
Upon returning to QuickBooks, you’re provided a list of accounts you have with that bank. Select all of the bank accounts you would like to link to QuickBooks Online. After you click on an account, a drop-down box will list the bank accounts included in your Chart of Accounts. If your checking account is already included in your Chart of Accounts, select it.
Map each of the accounts with your bank to the corresponding bank account you’ve set up in the chart of accounts. If you forgot to add your bank account, you can add one on the fly by selecting add new at the top of the dropdown menu.
Select bank account and link to Chart of Accounts
Step 5: Select the Right Date Range
After assigning your bank account to an account from your Chart of Accounts, you must decide how far back QuickBooks should go to pull transactions. Note that your bank may have limitations to how far back you can import transactions into QuickBooks Online.
The default start date to pull transactions is the beginning of the year. You should try to pull transactions back to the date you wish to start using QuickBooks Online. If your bank or QuickBooks doesn’t allow you to go back that far, you’ll have to transfer the older transactions using a QuickBooks Online or CSV file.
Select the beginning date to transfer bank transactions
Tip:
You need to decide the date you’ll begin using QuickBooks Online. For a new company, it should be when the company was formed. For an existing company, we recommend using the first day of the current year or quarter. This is an important decision because this is your only chance to import old transactions automatically. If you import transactions that are too old―from before you started using QuickBooks Online―you’ll need to delete each transaction manually.
Click the green Connect button when you’re done. It may take a few minutes for QuickBooks to connect your bank account.
Step 6: Review Downloaded Transactions in the Banking Center
QuickBooks Online will take you to the Banking Center after connecting your bank account. You can review the downloaded transactions and make sure everything is accurate in the Categorized or Reviewed tab (not shown).
We need to complete a few more set-up tutorials before we go through the process of reviewing and classifying the imported transactions. However, you can peek ahead at How to Manage QuickBooks Online Bank Feeds if you’d like to learn more now.
Now that your bank is connected, new transactions will be imported automatically into QuickBooks Online daily. If you visit the Banking Center anytime by clicking the Banking dropdown under the Banking menu from the left menu bar as you did at the beginning of this tutorial.
When To Import Transactions Manually Into QuickBooks Online Using a CSV File
There are two scenarios when you must manually import transactions from a CSV or similar file. First, some banks will not connect with QuickBooks Online, allowing for an automatic import. Second, you might need to import older transactions that were not imported in the previous section.
It’s likely that some of the transactions you are importing will already be entered in QuickBooks. For instance, bank transactions are created when you print checks from QuickBooks. Not worry, during your review of imported transactions, you can match them to existing transactions to avoid any duplication.
How To Import Transactions Into QuickBooks Online Manually With a CSV or Similar File
Step 1: Download a Transfer File From Your Bank
QuickBooks Online can import bank transactions from the following types of files:
- CSV: These files are used commonly to export data for a multitude of purposes. An advantage of using CSV files is they can be opened and edited with Excel.
- QuickBooks Online: QuickBooks Online files are QuickBooks WebConnect files specifically designed for exporting information from bank accounts to QuickBooks.
- QFX: QFX files are financial exchange files created for exporting information to Quicken but will also work with QuickBooks Online.
- OFX: An OFX file is a text file specifically designed for exporting financial data.
- TXT: Text files can be imported but might require some editing first. You might want to open the TXT file with Excel and convert it to a CSV file for a smoother transfer.
To obtain a transfer file, log into your bank account, and find the option that allows you to download your account transactions. You’ll likely have to select a file format to download your transactions. Choose the QuickBooks Online or QFX format if available since these file types are designed specifically for QuickBooks and will require less work. The next best option is an OFX file, but if that isn’t available, a CSV or TXT file will suffice.
Save the transfer file to a location on your computer that you can remember to import the file automatically.
Step 2: Select the Transfer File to Upload in QuickBooks
From the Banking Center, open the drop-down menu next to Update in the upper right corner, and then select File upload, as shown below.
Select Upload from file from the Banking Center in QuickBooks Online
Drag and drop your file of transactions or click on the Select file button to locate the file you want to upload.
Select the transfer file to import into QuickBooks Online
When you have selected your transfer file, click the green Continue button in the bottom right corner of the screen.
Step 3: Select a QuickBooks Account
Next, you must select an account from your Chart of Accounts to record the imported bank transactions.
Select an account to record imported transactions in QuickBooks Online
If needed, you can click Add new to set up a new checking account as demonstrated earlier. Paul’s Plumbing created a new QuickBooks account named Local FCU. After you have selected a QuickBooks account to record the imported transactions, click the green Next button in the lower right-hand corner.
If your transfer file is a QuickBooks Online, QBX, or an OFX file, you’re done and can skip to Step 6.
If your transfer file is a CSV or TXT file, you have a few more steps to complete.
Step 4: Map Columns to QuickBooks Fields (CSV and TXT Files Only)
Before completing this step, you should open your CSV or TXT file in Excel to determine exactly what information your bank exported and how it’s formatted. Paul’s Plumbing exported the following CSV file from their local credit union account:
CSV file for import into QuickBooks Online
Notice that transaction amounts are shown in column E with deposits shown as positive numbers and withdrawals shown as negative numbers. Some banks might include deposits in one column and withdrawals in another column.
After completing step 3, if you’re importing a CSV or TXT file, you should be on the Map Columns screen (not shown), where you’ll be required to provide the following:
- First row in file is a header row: Place a checkmark in this box if the first row in your transfer spreadsheet provides the title for each column.
- Date: Click the drop-down arrow and select the column in your transfer spreadsheet that contains the date of the transaction. In the next field, select how your spreadsheet formats the date.
- Description: Select the column that contains a description of the transaction. Paul’s Plumbing chose the Memo column as the description.
- Amount: First, choose whether your spreadsheet has all amounts in one column or if deposits and withdrawals are in separate columns. As we saw above, Paul’s Plumbing’s spreadsheet has all the amounts in one column, which is located in column E. If you indicate amounts are shown in two columns, you’ll need to specify one column for money spent and another for money received.
When you’re satisfied with your mapping, click the green Next button in the bottom right corner of the screen.
Step 5: Select Transactions for Import (CSV and TXT Files Only)
You can select which transactions from your file to import. I highly recommend importing all transactions into the file. Even personal transactions in your business account need to be imported and accounted for. Transactions already recorded in QuickBooks can also be imported and won’t be duplicated as you can match them to existing transactions when you review your bank feed later.
Make sure all transactions are selected and then click the green Next button in the bottom right corner of the screen to complete the import of your transactions.
Tip:
If there are no transactions listed on this screen, make the following changes to your file:
- You should remove all formatting from the amount column, even dollar signs.
- Blank rows must be removed so that everything begins at row 1, column 1 of the spreadsheet.
After you make changes to your spreadsheet, you must save it and then back up to step 2 and reselect the file to import.
Step 6: Return to the Banking Center
Click on Banking under Banking from the left menu bar to return to the Banking Center. Your bank account now is shown in the upper left corner with the number of transactions awaiting review.
We’ll learn how to review the transactions and accept, exclude, or match them to an existing transaction in How to Manage QuickBooks Online Bank Feeds.
The Importance of Connecting Your Bank Account to QuickBooks
Downloading your banking transactions into QuickBooks Online automatically instead of inputting them manually is an enormous time-saver. Most big banks allow you to connect to QuickBooks quickly and easily. If your bank does not connect with QuickBooks, you can still transfer your banking transactions with Excel and a CSV or QuickBooks Online file.
Tip:
Do not combine personal and business activity in the same checking account. You’ll waste time and effort in dealing with personal transactions in your company’s books. Instead, open a checking account specifically for your business. If your business is a sole proprietorship or you are self-employed, the checking account can be a second personal account. If your business is a partnership, limited liability company (LLC), or corporation, read our Best Free Business Checking Accounts.
Wrap Up
You’ve now learned to connect your bank to QuickBooks Online and to import bank transactions manually when an automatic bank connection is not available. We’ll return to managing bank feeds in a later tutorial but, first, we must finish setting up your QuickBooks Online company. The next tutorial in our QuickBooks Online Training Course is How To Import Credit Card Transactions Into QuickBooks Online. That tutorial will cover both how to connect a credit card account to update transactions automatically and how to import transactions from a CSV file.
