Excel Agility: QuickBooks/Excel Reporting Part 1
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Taxation and Accounting
- Credit: ATATX 1.5, CPE 2.0
-
Excel and QuickBooks expert David Ringstrom, CPA, demonstrates how to overcome the limitations of internal reports in QuickBooks and, subsequently, speed up your work in Excel. David outlines 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.
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 webcast.
Who Would Be Interested in This Course:
Practitioners who want to streamline their reports by accessing QuickBooks data using Excel.
Your Benefits of Attending:
- Learning QuickBooks techniques that may help you avoid the need to analyze data in Excel.
- Creating one-click access to memorized QuickBooks reports.
- Learning why in many cases you should export reports intended for spreadsheet analysis to a .CSV file instead of an Excel workbook.
- Bypassing filter drop-down lists by filtering based on cell contents with a single keystroke or mouse click.
- Using Excel’s PivotTable feature to quickly identify anomalies within QuickBooks data.
- Tracking customer longevity by creating a report that shows first and last invoice dates by customer.
- Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.
- Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula.
- Employing the Text to Columns feature to transform text-based dates into numeric values that Excel recognizes as dates.
Learning Objectives:
- Recall how to analyze QuickBooks data by way of pivot tables in Excel.
- Define the keyboard shortcut that enables you to delete one or more rows or columns that you’ve selected.
- Identify the setting that enables certain browsers to launch Excel files exported from QuickBooks Online and other sources.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience with QuickBooks and Excel
Advance Preparation:
None
Table of Contents
Introduction
Excel Versions 00:01:01
Open QuickBooks Sample Company 00:02:15
Determining Top Customers 00:04:30
Create One-Click Access to Reports 00:10:19
Creating 12-Month P&L in QuickBooks Online 00:14:31
Auto-Open QuickBooks Online Reports 00:20:43
Exporting P&L Data to Excel via CSV 00:23:29
Export to Excel vs. Export to .CSV 00:30:43
Self-Updating Spreadsheets Linked to CSV Files 00:36:00
Self-Updating Spreadsheets Linked to CSV Files (cont.) 00:42:29
SUMIF with Wildcard Criteria 00:45:48
Transaction Detail Report 00:52:29
Minor Data Clean-UP 00:56:45
Removing Sales Tax and Blank Rows 00:59:56
Summarize with Pivot Table 01:02:57
Summarize with Pivot Table (cont.) 01:04:49
Top Ten Filter 01:09:45
First/Last Invoice Date by Customer 01:12:38
First/Last Invoice Date by Customer (cont.) 01:17:23
Remove Data with Text to Columns Wizard 012:17:46
First/Last Invoice Date by Customer (cont.) 01:21:07
Calculating Client Tenure with DATEDIF 01:25:27
Merging Two QuickBooks Report 01:29:10
Merging Two QuickBooks Report (cont.) 01:32:28
Merging Two QuickBooks Report (cont.) 01:33:55
Merging Two QuickBooks Report (cont.) 01:36:08
Conclusion 01:39:34
Index
- .CSV 00:16:44, 00:24:34
- Binary Workbook 01:07:12
- DATEDIF 01:25:57
- Icon Bar 00:10:48
- P&L Report 00:14:37
- Pivot Table 01:03:06
- Quck Access Toolbar 00:38:15
- SUMIF 00:46:01
- Text Import Wizard 00:38:44
- Text to Columns Wizard 01:17:51
- Top 10 Filter 01:10:13
- VLOOKUP 01:36:28
- Wildcards 00:46:41
.CSV: Comma-Separated Value files are text files where each field of data is separated by a comma. This is an effective means to export data from QuickBooks that you, in turn, wish to analyze in Excel.
Binary Workbook: A file with the XLSB file extension is an Excel Binary Workbook file. They store information in binary format instead of XML like with most other Excel files (like XLSX). Since XLSB files are binary, they can be read from and written to much faster, making them extremely useful for very large spreadsheets.
DATEDIF: A worksheet function in Excel that works in any version of Excel but that mysteriously doesn't appear in Excel's online help documentation. DATEDIF has three arguments: Date1, Date2, and Interval. Keep in mind that DATEDIF does not count the starting period, so you may need to add 1 to its result.
Icon Bar: The icon bar is the area immediately below the menus. It offers quick navigation and access to the most commonly used features in QuickBooks. Start any task by clicking an icon on the icon bar. You can also add or remove icons to customize which icons display on the icon bar.
P&L Report: P&L is short for Profit and Loss. Such reports are also interchangeably called Income Statements.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.
Text Import Wizard: An Excel feature that allows you to separate data from text files into multiple columns.
Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.
Top 10 Filter: Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum. You can summarize your data by creating an Excel Pivot Table, and then use Value Filters to focus on the top 10, bottom 10 or a specific portion of the total values in your data.
VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.
Wildcards: Wildcards are special characters that can take any place of any character. There are three wildcard characters in Excel: * (asterisk), ? (question mark), and ~ (tilde) .
