QuickBooks Desktop/Microsoft Excel Data Analysis

Author: David H. Ringstrom

In this course, Excel and QuickBooks expert David Ringstrom, CPA, shows you how to overcome the limitations of internal reports in QuickBooks Desktop. He shares several techniques, including combining two reports into one, summarizing QuickBooks data via Excel pivot table. You'll see how to use Power Query in Excel to streamline and automate transforming QuickBooks reports into analysis ready formats.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) during the course as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the course.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Publication Date: October 2023

Designed For
Practitioners who wish to learn more about creating and combining Excel reports from QuickBooks data.

Topics Covered

• Changing the sign of numbers in Excel by using the Multiply option within Paste Special
• Choosing the most analysis-ready report format from QuickBooks Desktop and QuickBooks Online
• Contrasting reports exported as Excel workbooks from QuickBooks Desktop to reports exported as comma-separated values (CSV) files
• Creating a 12 month P&L report from QuickBooks Desktop and then exporting to the comma-separated value (CSV) format to streamline analysis
• Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula
• Filtering transactions by date or date range
• Gaining control of long lists of data by filtering instead of sorting
• Leveraging Excel's Quick Access Toolbar to create a shortcut that enables you to filter lists with a keystroke instead of multiple mouse actions
• Managing information overload by creating a Top 10 pivot table
• Pivot charts are an adjunct to Excel's pivot table feature, which allows you to summarize data by dragging and dropping data with your mouse. Pivot charts are much more interactive than traditional Excel charts
• Simplifying filtering tasks by adding a Clear Filter shortcut to the Quick Access Toolbar
• Summarizing accounting detail exported from QuickBooks into meaningful report by way of Excel's PivotTable feature

Learning Objectives

• Recognize QuickBooks features and techniques to streamline reports for faster analysis in Excel
• Identify how to summarize QuickBooks data by way of Excel pivot tables
• Identify which command enables you to send data from the Power Query editor to a pivottable
• Identify the set of commands used to display the Query Properties dialog box
• Identify the ribbon tab that contains the Collapse Field command

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience with QuickBooks and Excel recommended.

Advance Preparation
None