Excel to QuickBooks Conversion

QuickBooks helps you locate your critical financial data and transfer the data at no cost. If you handle your company or clients’ books of accounts, it’s essential for you to know the process and tools you can use to make a swift and free switch to QuickBooks Online.

You can import your vendor, customer, chart of accounts, and inventory data from an Excel spreadsheet to QuickBooks.

You can also do a full backup of your or your client’s files before importing and converting files to avoid losing critical information. While importing Excel files from your computer, QuickBooks automatically opens an Excel spreadsheet where you can enter your business details.

Points to Remember while you Import and Export Data from Excel to QuickBooks

• As you cannot undo the info you import, you must make a backup just in case.
• We suggest you consider talking to your accountant before you import data.
• You can check out the Intuit Marketplace to see apps that help you import data.

How to Upload a CSV file or an Excel Data File into QuickBooks?

To import a list in the CSV format, you have to install the QuickBooks Import Excel and CSV toolkit, which consist of:

• Import from Excel and CSV Manual: a PDF document with best practices, instructions, and notes.
• XLS Example Folder: a sample Excel file containing 4 worksheets, one for each type of list.
• Allowed fields: a reference guide describing what fields are available for CSV and Excel import.
• CSV Examples Folder: 4 sample CSV files, one for each type of list.

Steps to Install the QuickBooks Import Excel and CSV Toolkit:

1. Firstly, Open the file download window for the toolkit, then select Save.
2. Secondly, go to your Windows Desktop, then choose Save.
3. Then double-click on QuickBooks_Import_Excel_and_CSV.exe on your Windows desktop to open the WinZip Self-Extractor window.
4. Next select Browse, choose the folder where you want to install the toolkit, then OK.
5. Now select Unzip to extract the contents, and then choose Close to close the WinZip Self-Extractor window.
6. Finally, open the QuickBooks Import Excel and CSV folder from the location you selected in step 4.

Import and Export QuickBooks Data with MS Excel Files

You can import Vendor, Customer, Items, and Chart of Accounts from an Excel spreadsheet to QuickBooks. There are numerous options available while importing Excel files from QuickBooks.

Let’s dive into the options available:

Choice 1: Standard Import

QuickBooks presents a formatted Excel spreadsheet where you can enter your information.

To Import Vendors, Customers, and Products you sell

• First, Go to the File menu, click on Utilities, then Import, and then select Excel Files.
• If you get the Edit/Add Multiple List Entries window, then click No.
• Now follow the wizard for importing files.
  • Select the type of data for importing.
  • QuickBooks opens a formatted excel spreadsheet; once you have entered your information, save the File, and then closed it, you will be given the option to Add My Data Now.
  • You can review results and view Data in QuickBooks.
  • Now select Close when done.

Note: If you want to import additional vendor, customer, or item data from excel using this standard option, you can always go back to this window.

To import Chart of Accounts

Note: Before importing, we suggest that you create a backup of your company file.

• First of all, go to the Lists menu, then select Chart of Accounts.
• Then, select the Account dropdown at the bottom, then choose Import from Excel.
• Next, click on Browse to select the Excel file you want to import.
• Select the File, then select Open.
• Select the Excel Sheet where the data you want to import is.
• Then Map your accounts.
• Mapping your accounts direct QuickBooks how to import the data from your Excel sheet.
  The column headings in QuickBooks might be different from your Excel, so you’d have to match them.  
  • First, type in a mapping name.
  • Then select Account as Import type.
  • You need to match the information under QuickBooks with the column headings in your Excel sheet.
  • Select Save.
• Now select Import. If it’s your first time importing, select Yes to confirm the process.

Choice 2: Edit/Add Multiple Lists

This option enables you to add and edit multiple vendors, customers, and items.

• From the Lists menu, select Add/Edit Multiple List Entries.
• Select the List dropdown and choose the list you need to work with.
• Next, filter the list to see a particular record.
• (Optional) Select the Customize Columns button to choose which columns you want to see in the Add/Edit Multiple List Entries windows. This way, you can efficiently work on the columns you need. Select the Default button to return to the preset columns for the list.
• Select OK.

Note: Columns like customer or vendor name are required and cannot be removed. You can only change their order.

• Add or edit the entries by clicking the appropriate fields. Note that you can copy (Ctrl+C) data from Excel and paste (Ctrl+V) it into this window. You can also Copy down and Duplicate rows (highlight any field and select Copy Down and Duplicate Row)
• Select Save Changes.

After saving your changes, you will receive a warning telling you the number of saved records. QuickBooks will also show you if there are errors that need to be fixed.

To fix the errors:

1. Click or hover your mouse in any field to see what the error is.
2. Update the record to fix the errors.
3. Select Save Changes and repeat the process until all errors are fixed.
   
   Note that if you don’t want to fix an error, you can save the changes you’ve made so far and select Close without making further changes. QuickBooks will not save the changes that contain errors.

Export Excel Files

You can export Vendor, Customer, Payroll lists and transactions, as well as the Items list, into your Excel spreadsheet.

If you want to change a report’s contents or appearance in ways that are not available in your QuickBooks, you can also export reports and customize them in Excel without changing your QuickBooks data.

Vendor/Customer/Payroll List and Transactions

1. First, open the Customer/Vendor/Payroll center.
2. Then, select Excel dropdown and select:
   • Export Vendor/Customer/Employee list if you have to export customer/employee/vendor data such as balances, name, and contact details.
   • Export Transactions if you need to export transactions (either by name or transaction type).
3. In the Export window, select whether to create a new worksheet or update an existing worksheet.
4. Select the Export button. QuickBooks opens MS Excel, and you can edit the information as needed.

Items

1. Go to the Lists menu, select Item List.
2. On the Excel drop down, choose Export all Items.
3. In the Export window, select whether you want to create a new worksheet or update an existing worksheet.
4. Select the Export button. QuickBooks opens MS Excel. You can edit the information as needed.

Reports

Information that your QuickBooks exports to Excel consist of:

• Your data in the report, as it is displayed on the screen.
• The formula for totals, subtotals, and other calculations.
• The row labels that describe the contents of each row
• Headers and footers from the QuickBooks report.

1. Open the report you want to export.
2. Select the Excel dropdown at the top of the report, then select Create New Worksheet or Update Existing Worksheet.
3. In the Send Report to Excel window, choose what you’d like to do with the report.
4. Select the Advanced button to see other available options for the exported report, then select OK. To ensure the exported report will show Header information.
5. If you are using a newer version of MS Excel and the exported report does not show header information, go to the Printing options section and make sure the On Printed report and screen option is selected.
6. Select the Export button. QuickBooks opens MS Excel. You can edit your data as needed.

Steps to Convert Transactions from Excel to QuickBooks

Step 1: Enter the transactions in Excel or Excel format from your online banking or credit card website or download transactions in CSV.

The first line of the CSV file has to be the header with column names such as amount, date, payee, memo, check number. Every transaction should take one line in the CSV/Excel file.

If created in Excel, you need to save your Excel/CSV file before converting.

Step 2: Determine the importable Format Based on your QuickBooks Version

QuickBooks imports QBO and IIF formats. The QBO format is for credit card and bank transactions only. The IIF format enables entries for all accounts but requires more details.

QuickBooks import QBO files till it becomes three years old. After which, your QuickBooks needs to be upgraded.

Step 3: Convert the Transactions in Excel/CSV format to QBO or IIF format.

You need to download Bank2QBO to convert to QBO or Bank2IIF to convert to IIF format.

Both Windows and macOS versions are available; you can install them on your computer.

IMPORTANT: Bank2QBO is now substituted with the Transactions app, which converts to more formats and converts from more formats.

• Start the converter and open the Excel file or CSV file.
• Check how transactions are parsed and click Review Mapping if needed. Custom mapping eases when the parser cannot detect columns automatically.
• Before converting to an IIF file, you need to set the account type and account name.
• Click the “Convert” button to create a QBO or IIF file.
• The converter uses your CSV/Excel filename and its location as the new File base name. You can adjust the filename and its location before converting it.
• If you mark the “Open after save” checkbox, the converter will do a system call to open the created File.
• By default, IIF files and QBO must be opened by Quickbooks installed on your system.

If the checkbox was not set, then you need to do the following to import created File:

• For QBO files, switch to Quickbooks and then select File->Utilities->Import->Web Connect
• For IIF files, switch to Quickbooks and then select File ->Utilities->Import->IIF file.

Steps to Import Bank Transactions from Excel to QuickBooks

You will have to import your bank transactions using the.QBO format because importing bank transactions to QuickBooks Desktop (QBDT) using the. CSV file is not possible.

Let us show you how to do it:

• First of all, sign in to your Bank and download the transaction as a .QBO file.
• Then, in your QBDT. Go to the “File” menu.
• Next, select Utilities, then Import, and then click on Web Connect Files.
• Click the .QBO file you have saved, then select Open.
• Select your bank account.
• Click on Continue.
• You should see a dialogue box notifying you that the data has been successfully read into QuickBooks and then select OK.
• Now, go to the Bank Feeds Center.

---