QuickBooks/Excel Report Speed Tips: Part 1 (Completed)

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

Who Should Attend
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
• Customizing the Profit and Loss Report in QuickBooks Online into a twelve month format.
• Deleting blank rows or columns by way of shortcut menus or keyboard shortcuts
• Employing the Text to Columns feature to transform text-based dates into numeric values that Excel recognizes as dates
• Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula
• Gaining control of long lists of data by filtering instead of sorting
• 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

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

Level
Intermediate

Instructional Method
Group: Internet-based

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

Program Prerequisites
Experience with QuickBooks and Excel recommended.

Advance Preparation
None