QuickBooks/Excel Report Speed Tips: Part 2
Author: David H. Ringstrom
||2 hours for CPAs
In Part 2 of this series, Excel expert David Ringstrom, CPA will show you how to flatten multiple column reports, such as the Profit & Loss By Class report, back into a list format that can be used for analyzing data further with pivot tables, slicers, and so on. Excel 2016 users will learn how to visually present a summary profit and loss report in chart form by way of the Waterfall Chart feature, while users of earlier versions of Excel will still learn tricks for cleaning up their QuickBooks reports.
The session also will cover how those using Excel 2013 and later can use the Bing Maps feature to transform summary data into interactive maps. If you’re using Excel 2010 or earlier, you won’t be able to use the Waterfall Chart or Bing Maps features, but this jam-packed presentation demonstrates plenty of techniques that can be implemented in those versions of Excel. Techniques are equally applicable to reports exported from both QuickBooks Online and QuickBooks Desktop.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 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 course.
Publication Date: November 2018
Practitioners who wish to expand their knowledge of analyzing data from QuickBooks Desktop and QuickBooks Online more effectively.
- Transforming a report, such as the Profit & Loss By Class report, into a list you can in turn analyze with pivot tables and other tools
- Removing the Text number format from reports you export to Excel from QuickBooks
- Utilizing filtering to remove extraneous total rows and headings from your data
- Removing blanks and unwanted rows from reports
- Concatenating multiple columns of account numbers into a single column
- Capitalizing on the hidden Multiple Consolidation Ranges feature to summarize data into a pivot table format
- Undoing the Table feature within data that you've drilled down into from a pivot table
- Utilizing Text to Columns to separate accounts and subaccounts into multiple columns
- Converting formulas to values within an exported QuickBooks report
- Utilizing Bing Maps in Excel 2013 and later within supported types of workbooks
- Concatenating multiple address fields into a single combined field and removing duplicates
- Interacting with mapped data to see underlying data points
- Utilizing the Bing Maps feature in Excel 2013 and later to convert data in order to map sales by city and state
- Correcting nuances in mapped data when Bing Maps misinterprets your data
- Identify how to use Excel's Text to Columns feature
- Define the ideal data for use with creating pivot table reports
- Recognize and apply Trace Dependents and Trace Precedents while auditing formulas
NASBA Field of Study
Computer Software & Applications (2 hours)
QuickBooks and Excel experience.