QuickBooks Online/Microsoft Excel Data Analysis
Author: David H. Ringstrom
| CPE Credit: |
2 hours for CPAs |
In this comprehensive course, Excel and QuickBooks expert David Ringstrom, CPA, shows you how to overcome the limitations of QuickBooks internal reports.
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: May 2023
Designed For
Practitioners who want to streamline their reports by accessing QuickBooks data using Excel.
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
- Creating self-updating Excel reports by way of the Spreadsheet Sync feature available to QuickBooks Online Advanced subscribers
- Customizing the Profit and Loss Report in QuickBooks Online into a twelve month format
- Exploring the Bing Maps feature in Excel 2013 and later to map customers by location
- 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
- Mapping sales by city in Excel 2013 and later with Bing Maps
- 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
- Setting QuickBooks Online reports exported to Excel to open automatically, rather than being sidelined in the downloads bar of your browser
- Streamlining the process of building Waterfall charts from QuickBooks Online Profit & Loss reports by way of the SUMIF function
Learning Objectives
- Recognize how to apply QuickBooks features and techniques to streamline reports for faster analysis in Excel
- Recognize how to analyze QuickBooks data by way of pivot tables in Excel
- Recognize how to apply Excel's VLOOKUP function to merge data from two reports
Level
Intermediate
Instructional Method
Self-Study
NASBA Field of Study
Computer Science (2 hours)
Program Prerequisites
Experience with QuickBooks and Excel
Advance Preparation
None
