QuickBooks/Excel Report Speed Tips: Part 1

Author: David H. Ringstrom

In this session, 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 tables, creating one-click access to QuickBooks reports, and much more. Part 1 of the presentation focusses on QuickBooks Desktop, although many of the techniques are equally applicable to QuickBooks Online as well.

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 (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

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 2019, Excel 2016, and so on.

Publication Date: November 2021

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

Topics Covered

• Automating spreadsheet updates by utilizing Power Query feature to connect to comma-separated value files and Excel workbooks
• 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
• Contrasting reports exported as Excel workbooks from QuickBooks Desktop to reports exported as comma-separated values (CSV) files
• Creating one-click access to frequently used reports in QuickBooks Desktop
• 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
• Managing information overload by creating a Top 10 pivot table
• Contrasting merging two QuickBooks reports with Power Query versus VLOOKUP
• Illustrating a Profit & Loss report graphically in Excel by using the Waterfall Chart feature in Excel 2016 and later

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
• Recognize and apply Excel's VLOOKUP function to merge data from two reports into one
• Identify which button on a report window allows you to condense a report by hiding jobs in QuickBooks Desktop
• Recognize which option(s)QuickBooks Online offers for exporting reports to Excel
• Identify which section of the Excel Options dialog box allows you manage the Protected View setting
• Recognize which menu in Excel the Pivot Table command appears on
• Describe which feature in Excel lets you spearate text into two or more columns
• Identify which option enables you to display the earliest date from a group of records within a pivot table

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