Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

12.3 Integrating Microsoft Excel and Accounting Programs

Workplace Software and Skills12.3 Integrating Microsoft Excel and Accounting Programs

Table of contents
  1. Preface
  2. 1 Technology in Everyday Life and Business
    1. Chapter Scenario
    2. 1.1 Computing from Inception to Today
    3. 1.2 Computer Hardware and Networks
    4. 1.3 The Internet, Cloud Computing, and the Internet of Things
    5. 1.4 Safety, Security, Privacy, and the Ethical Use of Technology
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  3. 2 Essentials of Software Applications for Business
    1. Chapter Scenario
    2. 2.1 Software Basics
    3. 2.2 Files and Folders
    4. 2.3 Communication and Calendar Applications
    5. 2.4 Essentials of Microsoft 365
    6. 2.5 Essentials of Google Workspace
    7. 2.6 Collaboration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  4. 3 Creating and Working in Documents
    1. Chapter Scenario
    2. 3.1 Navigating Microsoft Word
    3. 3.2 Formatting Document Layout in Microsoft Word
    4. 3.3 Formatting Document Content in Microsoft Word
    5. 3.4 Collaborative Editing and Reviewing in Microsoft Word
    6. 3.5 Document Design
    7. 3.6 Navigating Google Docs
    8. 3.7 Formatting Layout and Content in Google Docs
    9. 3.8 Collaborative Editing and Reviewing in Google Docs
    10. 3.9 Versions and Version History
    11. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  5. 4 Document Preparation
    1. Chapter Scenario
    2. 4.1 Microsoft Word: Advanced Formatting Features
    3. 4.2 Working with Graphics and Text Tools in Microsoft Word
    4. 4.3 Managing Long Documents in Microsoft Word
    5. 4.4 Google Docs: Enhanced Formatting Features
    6. 4.5 Working with Graphics and Text Tools in Google Docs
    7. 4.6 Managing Long Documents in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  6. 5 Advanced Document Preparation
    1. Chapter Scenario
    2. 5.1 Creating Different Document Types in Microsoft Word
    3. 5.2 Mail Merge in Microsoft Word
    4. 5.3 Creating Forms in Microsoft Word
    5. 5.4 Creating Different Document Types in Google Docs
    6. 5.5 Creating Forms in Google Docs
    7. 5.6 Advanced Collaboration in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  7. 6 Preparing Presentations
    1. Chapter Scenario
    2. 6.1 Presentation and Design Essentials
    3. 6.2 Designing a Presentation in Microsoft PowerPoint
    4. 6.3 Formatting Microsoft PowerPoint Slides: Layout and Design Principles
    5. 6.4 Adding Visuals and Features to Microsoft PowerPoint Slides
    6. 6.5 Designing a Presentation in Google Slides
    7. 6.6 Creating Google Slides: Layout and Text
    8. 6.7 Adding Visuals and Features to Google Slides
    9. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  8. 7 Advanced Presentation Skills
    1. Chapter Scenario
    2. 7.1 Effective Presentation Skills
    3. 7.2 Finalizing a Slide Collection
    4. 7.3 Preparing a Microsoft PowerPoint Collection for Presentation
    5. 7.4 Preparing a Google Slides Collection for Presentation
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  9. 8 Content Management Systems and Social Media in Business
    1. Chapter Scenario
    2. 8.1 What Are Content Management Systems?
    3. 8.2 Common Content Management Systems
    4. 8.3 Creating Content with a Content Management System
    5. 8.4 Search Engine Optimization
    6. 8.5 Social Media in Business
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  10. 9 Working with Spreadsheets
    1. Chapter Scenario
    2. 9.1 Microsoft Excel Basics
    3. 9.2 Text and Numbers in Microsoft Excel
    4. 9.3 Calculations and Basic Formulas in Microsoft Excel
    5. 9.4 Formatting and Templates in Microsoft Excel
    6. 9.5 Google Sheets Basics
    7. 9.6 Text and Numbers in Google Sheets
    8. 9.7 Calculations and Basic Formulas in Google Sheets
    9. 9.8 Formatting and Templates in Google Sheets
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  11. 10 Advanced Excel Formulas, Functions, and Techniques
    1. Chapter Scenario
    2. 10.1 Data Tables and Ranges
    3. 10.2 More About Formulas
    4. 10.3 Using Arithmetic, Statistical, and Logical Functions
    5. 10.4 PivotTables
    6. 10.5 Auditing Formulas and Fixing Errors
    7. 10.6 Advanced Formatting Techniques
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  12. 11 Advanced Excel Spreadsheets: Statistical and Data Analysis
    1. Chapter Scenario
    2. 11.1 Understanding Data, Data Validation, and Data Tables
    3. 11.2 Statistical Functions
    4. 11.3 What-If Analysis
    5. 11.4 PivotTables/Charts
    6. 11.5 Data Analysis Charts
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  13. 12 Using Excel in Accounting and Financial Reporting
    1. Chapter Scenario
    2. 12.1 Basic Accounting
    3. 12.2 Financial Functions in Microsoft Excel
    4. 12.3 Integrating Microsoft Excel and Accounting Programs
    5. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  14. 13 Understanding and Using Databases
    1. Chapter Scenario
    2. 13.1 What Is a Database?
    3. 13.2 Microsoft Access: Main Features and Navigation
    4. 13.3 Querying a Database
    5. 13.4 Maintaining Records in a Database
    6. 13.5 Creating Reports in Microsoft Access
    7. 13.6 Creating Forms in Microsoft Access
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  15. 14 Advanced Database Use
    1. Chapter Scenario
    2. 14.1 Advanced Queries in Microsoft Access
    3. 14.2 Multiple Table Forms
    4. 14.3 Customizing Forms
    5. 14.4 Customizing Reports
    6. 14.5 Using Macros
    7. 14.6 Data Analysis and Integration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  16. 15 Integrating Applications
    1. Chapter Scenario
    2. 15.1 Microsoft 365: Collaboration and Integration
    3. 15.2 Microsoft Word: Integration with Microsoft Excel and Microsoft Access
    4. 15.3 Microsoft Word and Microsoft PowerPoint Integration
    5. 15.4 Microsoft Excel and Microsoft PowerPoint Integration
    6. 15.5 Microsoft Excel and Microsoft Access Integration
    7. 15.6 Integrating Data from Other Programs into Google Workspace
    8. 15.7 New Developments: The Role of Artificial Intelligence
    9. 15.8 Mastering Workplace Software Skills: A Project
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
  17. Index

Learning Objectives

By the end of this section, you will be able to:

  • Discuss the pros and cons of using Excel for accounting purposes
  • Describe how Excel and QuickBooks can be integrated

WorldCorp uses Microsoft Excel for managerial accounting and data analysis, but they use an accounting software suite to handle the financial accounting. Excel can be used in conjunction with your accounting system, but it cannot replace the accounting system. Accounting software also allows for real-time acceptance and recording of transactions. That means that all purchases through a retail store using a cash register and printed receipts are also recorded in the accounting software at the same time. While all these transactions are happening all over the country and internationally, the financial statements are automatically updated. Accounting suites allow for more automation than Excel, but Excel can still be useful in data analysis. This section will compare Excel with accounting suites like QuickBooks and explain how to import or export data into and out of accounting software suites.

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).

Intuit QuickBooks displays Import Data page (Bring your existing data into QuickBooks) with selection options: Bank Data, Customers, Vendors, Chart of Accounts, Products and Services, and Invoices.
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.)
The Upload (1) portion of an Import customers window displays options available: Select a CSV or Excel file to upload (Search window below) and Select Google Sheet (Search window below).
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.)
Export data window Reports tab displays All dates for Pre-selected date ranges for Which reports and lists do you want to export? Slider buttons available for General ledger and Profit and loss.
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.)

Spotlight on Ethics

Financial Auditing

The purpose of the financial audit is to ensure that the company is following the rules and governmental guidelines for managing and reporting financial information. It is also designed to assess the risk of the company for potential legal action for inaccuracies or wrongdoing. Audits are greatly facilitated with the use of software. It reduces human error and can even detect issues that might not be discoverable by the individual auditor. Deliberate manipulation of financial information can cost the company millions of dollars. In 2001, Enron was found to be inflating their company earnings by hiding debt. This results in Enron’s shareholders losing billions of dollars and the share price collapsed. Enron employees were convicted of wrongdoing and the company eventually went bankrupt.

Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.