In the vast business of spreadsheet software, Microsoft Excel is hailed as “king of software”. Every year, billions of business users prefers to work with Microsoft Excel for numerical data management which helps them in having insightful outlook about their business operations. In the field of finance and accounting, Microsoft Excel is the most trusted software worldwide, used for financial reporting, formulating sales forecast scenarios, visualizing patterns and organizing records. Excel works like an online spreadsheet maker, a comprehensive desktop application with Visual Basic programming, and can also be accessed through mobile apps for Android and Apple phones and tablets. The flagship version of Excel is offered with Microsoft Office desktop version offers 480 plus functions. The majority of these functions are being used in Visual Basic routines and commands.
Steps to integrate Microsoft Excel and QuickBooks
To connect QuickBooks from Microsoft Excel by using ODBC Driver for QuickBooks
The users can use Microsoft Excel in accessing data from a QuickBooks data by using ODBC connector. By using ODBC Driver, users can easily import the data directly into an Excel spreadsheet and can present it in the form of a table. The users should ensure that they are using matching Excel and ODBC Driver. While working with Microsoft Excel, there are 5 different ways of retrieving data from various sourcing by proper usage of ODBC drivers.
The five different ways are: -
- To connect Microsoft Excel to QuickBooks with Get & Transform (Power Query)
- To connect Microsoft Excel to QuickBooks with Data Connection Wizard (Legacy Wizard)
- To connect Microsoft Excel to QuickBooks with the Query Wizard
- To connect Microsoft Excel to QuickBooks with Microsoft Query
- To connect Microsoft Excel to QuickBooks with PowerPivot
To connect Microsoft Excel to QuickBooks with Get & Transform (Power Query)
- The users are recommended to installed an ODBC driver for QuickBooks in using Get & Transform for connecting to QuickBooks from Microsoft Excel.
- In Excel, click the Data and then expand the Get Data drop-down list.
- Now, do click From Other Sources > From ODBC.
- Go to From ODBC dialog, and then choose data source name (DSN).
- In case, if you have not configured your ODBC driver, then you can expand the Advanced Options dialog box and then enter the connection string for your data source (without credentials). In addition, you can also enter SQL statement which will be executed after establishing a connection to the data source.
- Now, click OK
- In case, you are using a database username or password then select Database and then enter your credentials in the dialog box.
- Next, do click Connect
- In case, your database is not password-protected or you have specified your credentials in the ODBC data source settings then do select Default or Custom and then press Connect.
- Subsequently, a window will appear, do select the table you want to retrieve data from and then click Load.
- Finally, the data from the table will get displayed in an Excel spreadsheet, which can used further or future business operations.
To connect Microsoft Excel to QuickBooks with Data Connection Wizard (Legacy Wizard)
This option of integration can be successfully used in connecting ODBC external source which has already been defined.
- In Microsoft Excel, users should go to Data
- Now, do click From Other Sources and then click From Data Connection Wizard.
- A dialog will get opened, do select ODBC DSN
- Do click Next to continue
- Next, do select a data source that is to be connected and then click Next
- For connecting to the table containing the required data, do select its name and then click Next to enter and save information about your new file or else click Finish
- Go to Import Data dialog, do select the way your data to be viewed in Excel and then place where to put it in the worksheet
- Now, click OK
- Finally, the desired data is properly displayed in the existing Excel worksheet.
To connect Microsoft Excel to QuickBooks with the Query Wizard
This option can be used to create a simple query for retrieving data from QuickBooks to Microsoft Excel by using ODBC driver.
- Firstly, users should open Excel, and then go to Main Menu and click the Data tab
- From dropdown menu, do click the From Other Sources, and then click From Microsoft Query
- A dialog will appear, do choose the data source which is to be connected.
- After accomplishing connection successfully, users can select the required data to be displayed in Excel
- Do click Next
- The succeeding two steps will enable filtering as well as sorting the data.
- Now, click Next to skip these procedures.
- In case, if users have plan to further use the query then they can save it by clicking the Save button, placed on the right.
- Next, do select Return Data To Microsoft Excel and then click Finish.
- Go to Import data dialog, and then select the way your data should be viewed in Excel and then place it at appropriate place in the worksheet.
- Now, click OK
- Finally, the required data gets successfully imported to Excel.
To connect Microsoft Excel to QuickBooks with Microsoft Query
This option is used to create a more complex query for retrieving QuickBooks data to Microsoft Excel through ODBC driver.
- Firstly, go to Microsoft Excel, and then click the Data tab
- A ribbon gets appeared, do click From Other Sources, and then click From Microsoft Query.
- In the succeeding dialog, users should choose the data source which they want to connect to.
- Now, uncheck Use the Query Wizard to Create/Edit Queries and then click OK.
- Next, users can select the tables they want to add to their query.
- After finishing this step, do click the Add
- Finally, users should navigate to the graphical editor, where they can easily do array of tasks like filtering rows and columns of data, sorting out data, joining multiple tables, creating a parameter query etc.
To connect Microsoft Excel to QuickBooks with PowerPivot
The users can use this option to perform data analysis and create complex data models. For loading the required data, do follow these steps: -
- In Quickbooks Excel Integration , do click the Power Pivot tab, and then click Manage to go to the PowerPivot window.
- A window gets opened, do click From Other Sources.
- After the opening of Table Import Wizard, do select Others (OLEDB/ODBC)and then click Next.
- Go to the Specify a Connection String window, and then click the Build
- In the Data Link Properties dialog, users should properly specify the data source they want to connect.
- Now, click Next
- Next, users should choose the way to import the data. It can be done by either selecting a table from the list or writing a query to specify the data to be imported.
- After the successful completion of import operation, do click the Close
- Finally, the retrieved data is successfully inserted in the worksheet.