Streamlining QuickBooks and Excel Reports Part 1
September 11th, 2019, 1:00 PM to 2:40 PM ET
In this practical webinar, Excel and QuickBooks expert, David Ringstrom, CPA, shares helpful tips to help you overcome the limitations of internal reports in QuickBooks – and, subsequently, speed up your work in Excel. You will learn several techniques, including how to combine two reports into one, create an Excel pivot table from QuickBooks data, avoid the need to analyze data in Excel, and more.
You will see each technique demonstrated at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2019. You will identify differences in Excel 2016, 2013, or 2010 and will receive detailed handouts. You will also receive an Excel workbook that includes the examples presented during the webinar.
You will learn:
- Why, in many cases, you should export reports intended for spreadsheet analysis to a .CSV file instead of an Excel workbook
- How to use the undocumented DATEDIF worksheet function
- How to use a wildcard character with SUMIF to summarize data based on a partial match
- How to create one-click access to memorized QuickBooks reports
- How to trace customer longevity by creating a report that shows first and last invoice dates by customer
- How to use Excel’s PivotTable feature to quickly identify anomalies within QuickBooks data
- How to bypass filter drop-down lists by filtering based on cell contents with a single keystroke or mouse click
- QuickBooks techniques that may help you avoid the need to analyze data in Excel
- How to make minor changes within QuickBooks that can result in less manual effort in Excel
Upon course completion, you will be able to:
- Identify the setting that enables certain browsers to launch Excel files exported from QuickBooks Online and other sources
- Recall the keyboard shortcut that enables you to delete one or more rows or columns that you’ve selected
- Summarize QuickBooks data by way of Excel pivot tables
To register for just Part 1 of this webinar, please click here.
Streamlining QuickBooks and Excel Reports Part 2
September 12th, 2019, 1:00 PM to 2:40 PM ET
In Part 2 of QuickBooks/Excel Reports, Excel and QuickBooks expert, David Ringstrom, CPA, explains additional ways you can analyze data from QuickBooks Desktop and QuickBooks Online. Among many other topics, you will learn how to flatten multiple-column reports back into a list format for further analysis, how Excel 2016 (and later) users 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.
You will see each technique demonstrated at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2019. You will identify differences in Excel 2016, 2013, or 2010, and will receive detailed handouts. You will also receive an Excel workbook that includes the examples presented during the webinar.
You will learn how to:
- Use Text to Columns in all versions of Excel to separate a tiered QuickBooks chart of accounts into columns
- Utilize the Timeline feature in Excel 2013 (and later) to filter pivot tables based on date ranges
- Flatten the Profit & Loss by Class report to unlock the data for analysis by way of pivot tables, filtering, and other tools in Excel
- Map sales by city in Excel 2013 (and later) with Bing Maps
- Illustrate financial statements with the Waterfall chart in Excel 2016 (and later)
- Use Slicers in Excel 2013 (and later) to filter data within reports exported to Excel from QuickBooks
- Streamline the process of building Waterfall charts from QuickBooks Online Profit & Loss reports by way of the SUMIF function
- Streamline filtering of lists in Excel 2013 (and later) by using the Slicer feature with tables
- Filter transactions from QuickBooks reports by date in any version of Excel
- Export customer contact lists from QuickBooks Online and QuickBooks Desktop
Upon course completion, you will be able to:
- Recall the keystroke that enables you to select two or more non-adjacent items from a Slicer
- Identify which versions of Excel offer the Timeline feature
- Determine which versions of Excel offer the Bing Maps feature, and how to use it
To register for just Part 2 of this webinar, please click here.
Suggested Audience: Practitioners who wish to learn more about creating and combining Excel reports from QuickBooks data.
Program Prerequisites: None. Experience with QuickBooks and Excel Recommended
Advance Preparation: None
NASBA Field of Study: Computer Software & Applications (2 hours each)
Program Level: Intermediate
Credit Type: Group-Internet Based for the Live Program. [If you attend the program on-demand (after the live date), you will receive Self-Study credit. Please note that to receive Self-Study credit you will need to complete a final exam after viewing the webinar.]