Connect Excel to QuickBooks: 4 Easy Ways

Bookkeeping is an important aspect for business owners in order to keep a tab on their business’s financial health. With the right recording and organizing of your firm’s Financial Data, you can estimate the profits and make informed strategic decisions for your company’s future. When you first start recording the Accounting stats, you will eventually use a Spreadsheet application such as Microsoft Excel. Excel has been around for many years and you can easily find the sample Accounting Formulas for your basic Bookkeeping needs. Though, as your business grows, you will require dedicated Accounting Software like QuickBooks to efficiently handle the rapidly increasing financial information.

QuickBooks is an intuitive Accounting Software that allows you to manage income, track inventory, expenses, forecast and project future earnings, accept payments, etc. You can also connect Excel to QuickBooks and import Customer, Supplier, Items, Chart of Accounts, etc, from an Excel Spreadsheet. This allows you to eliminate manual data entry and directly send data from Excel to QuickBooks. After importing your files from Excel to QuickBooks, you can continue using the brilliant QuickBooks functionalities and simplify your accounting process.

In this article, you will learn how to seamlessly connect Excel to QuickBooks for your business financials. 

What is QuickBooks?

QuickBooks provides an integrated one-stop platform for all small and medium-sized businesses to meet all of your growing Accounting needs. This tool has been introduced to the world by Intuit, a leading software company with a deep focus and understanding of Financial Software. No prior Accounting knowledge is required to use this application as QuickBooks does all the mundane tasks for you.

It offers an On-Premises QuickBooks Desktop version and a Cloud-Based service called QuickBooks Online that can be accessed over a secure network. Using this application you can effortlessly Track Bills & Expenses, Run Payroll, Scan Receipts, Generate Custom Reports, Manage Income, Sales & Tax Payments, and much more. Its easy integration with other applications helps you synchronize data across all departments in your organization.

What is Microsoft Excel?

Microsoft Excel is a Spreadsheet Application developed by Microsoft to organise and record data in a tabular format. Launched in 1985, Microsoft Excel is a widely used Spreadsheet software in businesses worldwide allowing you to organize and manipulate data through formulas in columns and rows. MS Excel is loaded with functionalities to perform several operations such as calculations, pivot tables, graph tools, etc.

Compatible with Microsoft Windows, macOS, Android, and iOS, MS Excel is included with the MS Office Suite. It is typically used by firms to record, organise and analyse financial data. MS Excel also offers VBA programming that can assist you in automating repetitive tasks.

Prerequisites

• An active QuickBooks account.
• Working knowledge of Quickbooks.

How to Import Data from Excel to QuickBooks?

Quickbooks is available as both, Desktop Version and Online Version. Quickbooks Desktop is an On-Premise solution that comes with 3 variants offering features suitable for businesses of all sizes with an annual subscription. Quickbooks Online is a monthly subscription-based version that is completely based on Cloud. Now, let’s understand how to import data from Excel to Quickbooks Desktop and Quickbooks Online in detail.

1. Import Data from Excel to QuickBooks Desktop

There are 3 simple approaches to connect Excel to Quickbooks Desktop and import your Excel Data:

• Excel to QuickBooks Desktop: Standard Import
• Excel to QuickBooks Desktop: Advanced Import
• Excel to QuickBooksDesktop: Add/Edit Multiple Lists

A) Excel to QuickBooks Desktop: Standard Import

Before importing the MS Excel file, you can check out which fields will be imported, which are optional, and restrictions if any. You import Customers, Vendors, and Products you sell in Quickbooks using the Standard importing feature. Follow the simple steps given below to perform the Standard Import:

• Step 1: On the top horizontal menu, navigate to File > Utilities > Import Data > Excel Files. If an Add/Edit Multiple Lists Window pops up, then click on the No button.

• Step 2: Now follow the instructions of the importing wizard:
  • A) Select the desired data type. 
  • B) Quickbooks now opens a formatted Excel Spreadsheet where you can paste your information from Excel and save it. After you close the file, Add my Data Now option will now pop up.
  • C) You can now review the results and click on the Close option when done. 

You can also import the Chart of Accounts using the Standard Import approach by following the easy steps below:

• Step 1: Before importing, it is always a good practice to create a backup of your company file.
• Step 2: Navigate to the Lists menu and click on the Chart of Accounts option.
• Step 3: From the Account drop-down menu, select the Import from Excel Option.
• Step 4: Click on the Browse button and select the Excel file you want to import.
• Step 5: Click on the Open option and select the desired sheet from the Excel file.
• Step 6: In order to import the data correctly, you need to Map the column headings/attributes of your Excel file to that of the QuickBooks. To do that follow these steps:
  • Step 1: Enter a Mapping Name.
  • Step 2: Select the Account option as Import type.
  • Step 3: Now, you can manually match the Quickbooks column headings with Excel and click on the Save button when done.
• Step 7: Click on the Import button and then select Yes to confirm the import process. 

B) Excel to QuickBooks Desktop: Advanced Import

QuickBooks offers an Advanced Importing method to connect Excel to QuickBooks. You can use this functionality by following the simple steps given below:

• Step 1: Go to File > Utilities > Import Data > Excel Files. If an Add/Edit Multiple Lists Window pops up, then click on the No button.
• Step 2: Click on the Advanced Import Option.

• Step 3: Now you can set up the mapping via these steps given below:
  • Step 1: Click on the Browse button and select the Excel file you want to import.
  • Step 2: Click on the Open option and select the desired sheet from the Excel file.
  • Step 3: Ensure to tick the This Data File has Header Rows checkbox.
  • Step 4: From the Choose the Mapping drop-down menu, select the Add New option. 

• Step 5: Enter a Mapping Name to identify the attribute mapping (Customers, Vendors, etc).
• Step 6: Select the data type you are importing from the Import Type drop-down menu.
• Step 7: Carefully check and match the QuickBooks and Import Data Columns, and finally, click on the Save button.

• Step 8: Verify the Mapping by clicking on the Preview button and then select the Import button to complete the importing process.

C) Excel to QuickBooks Desktop: Add/Edit Multiple Lists

This method allows you to import and edit multiple customers, vendors, and items (Only the list information i.e name, description, etc., and cannot be used to import transactions, invoices, bills, etc). Follow the steps to correctly use this approach:

• Step 1: Navigate to Lists > Add/Edit Multiple List Entries.
• Step 2: From the List drop-down menu, select the list you want to work with.
• Step 3: You can now filter the list to identify a specific record.
• Step 4: To simplify this process, click on the Customize Columns button to select which columns should appear in the Add/Edit Multiple List Entries Window. You can go back to the preset columns by clicking on the Default button.
  • For adding a column from the left pane, select a column and click on the Add option.
  • For deleting a column from the right pane, select a column and click on the Remove option.
  • You can rearrange the column order by selecting the column and then clicking on the Move Up or Move Down option. Finally, click on the OK button to complete the customization of the columns.
• Step 5: You can now copy the data from Excel Files and paste it into Quickbooks by clicking the appropriate fields.
• Step 6: Click on the Save Changes button for saving the above modifications.

For more information on importing Excel files and handling errors, you can visit the QuickBooks Support page.

2. Import Data from Excel to Quickbooks Online

Quickbooks Online offers a single and simple approach for importing data from Excel to QuickBooks. You can follow these easy steps given below to start importing Excel Files:

• Step 1: On the top right corner, click on the Settings ⚙️icon and select the Import Data option.
• Step 2: Several data type options will be displayed such as Bank Data, Customers, Suppliers, Products and Services, Chart of Accounts, Invoices, and Bills. In this example, for importing Bills from Excel Files, the Bills option is selected. Before Importing the file, ensure that the Excel file is correctly formatted according to QuickBooks Online and contains the following columns:
  • Bill no.
  • Supplier
  • Bill Date
  • Due Date
  • Account
  • Line Amount
  • Line Tax Code

• Step 3: Click on the Browse button and select your desired .CSV Excel file.
• Step 4: Click on the Open option and then click on the Next button.

• Step 5: Check and map your column headings. You can select the Date Format (eg. D/M/YYYY) and the required VAT option (Exclusive or Inclusive of Tax). Click on the Next button to proceed further.

• Step 6: Finally, map your VAT codes and click on the Next button.

• Step 7: Click on the Import Data option to start the process and click on the Done option once the import summary is displayed after the successful completion of the importing process.
• Step 8: Click on the OK button to finish.     

Conclusion

In this article, you have learned to effectively connect Excel to Quickbooks. QuickBooks Desktop version offers 3 methods to Import Data from Excel whereas QuickBooks Online provides a direct approach to import all types of data. QuickBooks is a dedicated software for answering all your Business Accounting needs. You can transfer your existing data from Excel to QuickBooks and enjoy the various features that allow you to Manage Sales, Track Inventory, Accept Payments, etc. With a clear view of your business’s Profit and Loss Statements from the visually stunning Dashboards and Reports, you can comfortably make Data-driven decisions for your firm.

As your business evolves and grows, a tremendous amount of data is generated at an exponential rate associated with your Customers, Suppliers, Products, and Services. For a deeper insight into your business’s financial health and performance, you would need to Integrate the data from all the applications in your organisation used for Marketing, Customer Relationship Management, Human Resources, etc. To efficiently handle this massive volume of data you would need to invest a portion of your Engineering bandwidth to Integrate, Clean, Transform and Load your data into your Data Warehouse or a destination of your choice. You can overcome all these challenges by using a Cloud-Based ETL tool like Hevo Data.

Hevo Data, a No-code Data Pipeline can help you transfer and integrate your data from different Data Sources such as QuickBooks into a Data Warehouse or a Destination of your choice for free. It is a fully automated and secure platform that doesn’t require you to write any code!

Want to take Hevo for a ride? Sign Up for a free 14 day Trial! and simplify your Data Integration process. You can check out the Pricing Details to select a plan according to your business needs. 

FAQ