12.3 Integrating Microsoft Excel and Accounting Programs

Pros and Cons of Using Excel for Accounting

Accounting principles are not programmed into Excel, so it does not perform automatic calculations or create documents in the same way accounting suites do. Accounting suites are designed specifically for accounting and bookkeeping, whereas Excel is a spreadsheet program that has broader uses than accounting, such as calculating mathematical equations using functions and formulas, building tables, and performing data analysis. You can use Excel as a tool for deeper analysis and comparison of decision alternatives by importing the information from the accounting-specific software programs. It can be used for analyzing any data in numerous disciplines, such as science, humanities, social sciences, and sports. It can create charts and graphs, which makes it easy to share information and collaborate using OneDrive or SharePoint, and thus it is very useful for managerial accounting.

Graphs, statistical tools, and charts offer insight and are used in reports and presentations. These Excel tools are not commonly found in financial accounting suites. Within Excel, these tools can build relatable visualizations using PivotTables to increase comprehension of data. This level of data analysis is not offered in financial accounting suites. You may find some of these features in larger accounting systems, but they are not typical. Also, as technology advances and user needs are incorporated into software updates, it is possible some additional data analysis capabilities could be added in accounting suites. But for now, data analysis of the accounting data is often done in Excel. You would need to extract the data and import it to Excel to make it visually impactful. You can also combine several accounts from the accounting software program in a single export file to Excel and analyze them together using a table or chart.

QuickBooks is one common accounting suite that is easy to use. Its interface has icons that represent the most common needs of business managers. The program is divided into the areas of vendors, customers, employees, companies, and banks. The vendor section is for handling billing and accounts payable and is also a central database for all vendors in the company. Having this list reduces the need for each department to have their own database of vendors. In the vendor section, you can manage the vendor information such as the contact email and the area of the company to which the vendor is assigned. The customer section is for generating invoices and handling accounts receivable and includes all the payment methods that customers may use. Just as the vendor section is used to manage vendors and create the database for the company, the same is true for the customer section. The customer section includes the master list of all customers in the company. The employee section is for handling the payroll. The company section is for organizing the Chart of Accounts and performing other similar functions. The banking section is for writing checks, tracking checks, recording deposits, and reconciling transactions.

The main advantage of an accounting suite compared with a spreadsheet is that it makes all needed tables and interconnections automatically. In Excel, you would have to build all tables from scratch and use cell references to make them communicate, which would be time-consuming. In an accounting suite, you can track transactions and costs without building tables like in Excel. QuickBooks even adds its own specialized features, such as scanning invoices from vendors automatically, without any typing.

Integrating Excel and QuickBooks

You have learned different situations in which a company may want to analyze accounting data in Excel, but you have also learned that Excel should not be used alone for a company’s bookkeeping. Accounting suites allow for easy data export for analysis in Excel. For accounting in small- to medium-sized businesses, over two-thirds of the market has been held by Intuit’s QuickBooks for many decades. QuickBooks is easy to use for most people comfortable with technology and data, and to use it, you don’t have to be an accountant. You create a chart of accounts when you first install the software or sign up online and feed it with daily invoices and expenses. Once it’s set up, it automatically forms your financial statements in real time. The application can generate tax-related reports such as the annual business tax filings or sales tax for submitting to government agencies.

Fortunately, Intuit and Microsoft have made their applications compatible. Excel also integrates well with QuickBooks and other accounting software. Both accounting applications readily accept importation of Excel files, and they both can export data to Excel. Excel opens their exported file with ease, and you can gather insights with these datasets by utilizing data analytics processes, forming data visualizations, and designing business intelligence queries. Most accounting programs have a tool or command button for importing Excel data into the program or for exporting the data to Excel for additional analysis. When the data is imported into the program, it is converted to that program’s data file type. Importing data from Excel into these specialized programs can be helpful to check for errors and to uncover inconsistencies in financial data.

When starting to implement QuickBooks in your business, you may need to import a lot of data from Excel files. Maybe you have your listing of products in an Excel file. In QuickBooks, there is a quick, automated way to import this data. In the online version, you use the Import Data feature. This process lets you import many different types of data, as you can see in Figure 12.14. For importing customer data, you will need to make sure that the Excel spreadsheet matches the instructions provided in QuickBooks so that it can map the data (Figure 12.15). It may involve some formatting and cleaning up of your Excel spreadsheet. If you want to export from QuickBooks to Excel, you can easily filter out what kind of data you want to export (such as certain transaction types or a date range) and export it to Excel format (Figure 12.16).

Figure 12.14 QuickBooks walks you through the process of selecting the type of data you want to import. (Reprinted with permission © Intuit Inc. All rights reserved.)

Figure 12.15 QuickBooks also walks you through choosing the file and ensuring the data is in the correct format. (Reprinted with permission © Intuit Inc. All rights reserved.)

Figure 12.16 QuickBooks will ask what kind of data you want to export (such as certain transaction types or a date range) and export it to Excel format. (Reprinted with permission © Intuit Inc. All rights reserved.)