QuickBooks/Excel Report Speed Tips Part 2
Author: David H. Ringstrom
| CPE Credit: |
2 hours for CPAs |
In this session, Excel and QuickBooks expert David Ringstrom, CPA, shows you how to overcome the limitations of internal reports in QuickBooks Online (QBO). He’ll show how to streamline exporting reports out of QuickBooks Online by making simple changes to your browser settings. You’ll see how to create self-updating spreadsheets with Power Query, and how to transform QBO data into pivot tables. Part 2 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 expand their knowledge of analyzing data from QuickBooks Desktop and QuickBooks Online more effectively.
Topics Covered
- Choosing the most analysis-ready report format from QuickBooks Online
- Exploring the Bing Maps feature in Excel 2013 and later to map customers by location
- Exporting customer contact lists from QuickBooks Online
- Filtering transactions by date or date range
- Illustrating financial statements with the Waterfall chart in Excel 2016 and later
- Streamlining the process of building Waterfall charts from QuickBooks Online Profit & Loss reports by way of the SUMIF function
- Understanding how to manage the Protected View prompt in Excel 2010 and later
- Using Slicers in Excel 2013 and later to filter data within reports exported to Excel from QuickBooks
Learning Objectives
- Identify how to use Excel's Power Query feature
- Define the ideal data for use with creating pivot table reports
- Identify the setting that enables certain browsers to launch Excel files exported from QuickBooks Online and other sources
- Recognize which section of the Excel Options dialog box allows you to manage the Protected View setting
- Identify which key enables you to delete a column within Power Query
- Recongize true statements about Slicers
- Identify SUMIF's arguments
Level
Intermediate
Instructional Method
Self-Study
NASBA Field of Study
Computer Software & Applications (2 hours)
Program Prerequisites
QuickBooks and Excel experience.
Advance Preparation
None
