Excel Accountant: QuickBooks Detailed Analysis

In this webcast, Excel and QuickBooks expert David Ringstrom, CPA, shares valuable tips to help you overcome the limitations of QuickBooks internal reports. David teaches several techniques, including how to combine two reports into one, create pivot tables from QuickBooks data, access memorized QuickBooks reports, and more. He also shows additional ways to analyze data from QuickBooks Desktop and QuickBooks Online in this follow-up to part 1. Among other topics, David covers how to flatten multiple-column reports back into a list format for further analysis, how users of Excel 2016 and later can visually present a summary profit and loss report in chart form by way of Waterfall charts, how users of Excel 2013 and later can implement the Bing Maps add-in to transform summary data into interactive maps, and how users of earlier versions of Excel can clean up their QuickBooks reports.

David demonstrates every technique at least twice in this course: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 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 webinar.

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.

Areas Covered:-

• Learn QuickBooks techniques to reduce the need for Excel analysis and create one-click access to memorized reports.
• Export reports for spreadsheet analysis to a CSV file instead of Excel and bypass filter drop-downs by filtering based on cell contents.
• Use Excel’s PivotTable feature to identify anomalies in QuickBooks data and track customer longevity with first and last invoice dates.
• Utilize Excel functions like DATEDIF and SUMIF with wildcards, and transform text-based dates using Text to Columns.
• Map sales by city with Bing Maps, manage the Protected View prompt, and use Slicers to filter QuickBooks data in Excel.
• Streamline Waterfall charts from QuickBooks P&L reports, export customer contact lists, flatten P&L by Class reports, and use Timelines to filter pivot tables.

Learning Objectives:-

• To identify QuickBooks features and techniques that can be used to streamline reports for faster analysis in Excel.
• To recall how to analyze QuickBooks data by way of pivot tables in Excel.
• To identify the setting that enables certain browsers to launch Excel files exported from QuickBooks Online and other sources.
• To identify how to use Excel’s Text to Columns feature.
• To define the ideal data for use in creating pivot table reports.
• To recall the keystroke that enables you to select two or more non-adjacent items from a Slicer.