4 Hour Live Virtual Training on QuickBooks/Excel Analysis and Pivot Tables: Beginner to Advanced
-
Training TypeLive Training
-
CategoryQuickBooks Online
-
Duration04 Hours
-
Rating4.6/5
Course Introduction
About the Course
In Part 2 of this series, Excel and QuickBooks expert David Ringstrom, CPA, demonstrates 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. In this jam-packed presentation, David teaches you how to visually present a summary profit and loss report in chart form by way of the Waterfall Chart feature, how to use the Bing Maps feature to transform summary data into interactive maps, and how to implement tricks for cleaning up QuickBooks reports. Techniques are equally applicable to reports exported from both QuickBooks Online and QuickBooks Desktop.
Course Objective
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.
Apply Excel’s VLOOKUP function to merge data from two reports into one.
Identify how to use Excel’s Text to Columns feature.
Define the ideal data for use by creating pivot table reports.
Applying Trace Dependents and Trace Precedents while auditing formulas.
Who is the Target Audience?
Practitioners who wish to learn more about creating and combining Excel reports from QuickBooks data.
Practitioners who wish to expand their knowledge of analyzing data from QuickBooks Desktop and Online more effectively.
Basic Knowledge:
No prerequisite knowledge needed. QuickBooks Pro or higher installed on your computer.
Available Batches
Pricing
-
• Deleting blank rows or columns by way of shortcut menus or keyboard shortcuts.
• Automating spreadsheet updates by utilizing the From Text feature to connect to comma-separated value files.
• Transforming the Sales By Customer Detail Report in QuickBooks Desktop into a top customers report ranked by sales.
• Simplifying filtering tasks by adding a Clear Filter shortcut to the Quick Access Toolbar.
• 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.
• Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.
• Summarizing accounting detail exported from QuickBooks into meaningful report by way of Excel's PivotTable feature.
• Contrasting reports exported as Excel workbooks from QuickBooks Desktop to reports exported as comma-separated values (CSV) files.
• Track customer longevity by creating a report that shows first and last invoice dates by customer.
• Understanding the nuances around opening comma-separated values (CSV) files manually.
• Setting QuickBooks Online reports exported to Excel to open automatically, rather than being sidelined in the downloads bar of your browser.
• Streamlining access to memorized reports in QuickBooks Desktop by creating custom icons on the shortcut or icon bars.
-
• Mapping sales by the city in Excel 2013 and later with Bing Maps.
• Utilizing the Timeline feature in Excel 2013 and later to filter pivot tables based on date ranges.
• Exploring the Bing Maps feature in Excel 2013 and later to map customers by location.
• Filling in gaps within a general ledger report exported from QuickBooks to prepare the report for analysis in Excel.
• Using Text to Columns in all versions of Excel to separate a tiered QuickBooks chart of accounts into columns.
• Illustrating financial statements with the Waterfall chart in Excel 2016 and later.
• Mapping sales by the city in Excel 2013 and later with Bing Maps.
• Using Slicers in Excel 2013 and later to filter data within reports exported to Excel from QuickBooks.
• Filtering transactions by date or date range.
• (blank)
• Streamlining the filtering of lists in Excel 2013 and later by using the Slicer feature with tables.
• Streamlining the process of building Waterfall charts from QuickBooks Online Profit & Loss reports by way of the SUMIF function.