COURSE DESCRIPTION

Discover how to dig deeper into pivot tables in this comprehensive course. Excel expert David Ringstrom, CPA, demonstrates step-by-step how to manipulate your pivot table data faster and more efficiently. Youll learn how to extract data from Microsoft Access databases and other sources, minimize repetitive steps in Excel by creating keyboard shortcuts, and adapt simple macros that can be recorded. In addition, David discusses several helpful Excel features, including the Table feature, PivotTable feature, Slicer feature, Linked Picture feature, the PowerPivot feature, and others.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. Hell draw to your attention 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.

Topics include:

• Creating dynamic and interactive graphs with Excels PivotChart feature.
• Discovering the Recommended PivotTables feature.
• Launching macros that clean up pivot tables with a single mouse click.
• Learning the nuances associated with subtotaling data within a pivot table.
• Learning how the Table feature can vastly improve the integrity of pivot tables in Excel.
• Incorporating calculations within or alongside pivot tables.
• Learning how to expand and collapse pivot table elements, thereby avoiding information overload.
• Gaining control of unruly pivot table data by creating a macro that can automatically transform all count fields into sums and apply number formatting in one fell swoop.
• Mitigating the side effects of converting a table back to a normal range of cells.
• Compiling unwieldy data into the format required for pivot table analysis quickly and easily.
• Staving off frustration by filling blank cells within any columns that contain numbers with zeros before you create pivot tables.

Learning Objectives:

After reading the course material, you will be able to:

• State how to use the Timeline feature in Excel 2013 and later.
• Define how to create pivot tables from the information you extract from databases.
• Identify how to summarize pivot table data in new ways by grouping based on dates or custom arrangements that you define.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

Many users pivot to Excel when a desired financial statement format cannot be created within the accounting software. However, this in turn often leads to techniques such as creating worksheets for every month of the year, which eventually can be cumbersome to revise and preserve data integrity.

In this presentation Excel expert David Ringstrom, CPA, explains how to use Excel functions, including VLOOKUP, OFFSET, and SUM, to quickly create accounting reports that allow you to switch to any reporting period with only two mouse clicks from a single worksheet. You'll also get tips on exporting data from your accounting package, improving the integrity of your spreadsheets, and much more.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

In this informative presentation, Excel expert David Ringstrom, CPA, shows you how to create resilient and easy-to-maintain budget spreadsheets. Among other techniques, David explains how to separate inputs from calculations, build out a separate calculation spreadsheet, create both an operating and a cash flow budget, transform filtering tasks, and preserve key formulas.

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.

Topics include:

• Building operating budgets quickly based on detailed supporting schedules that provide an audit trail.
• Understanding why its worthwhile to build out supporting schedules to break down calculations used in budgets.
• Avoiding the complexity of nested IF statements with Excels CHOOSE function.
• Preserving key formulas using hide and protect features.
• Crafting formulas to compute gross margins, projected sales, commissions, and related amounts.
• Using range names to streamline formulas and bookmark key inputs within a workbook.
• Understanding the use of the MONTH function to return the month portion of a date or month name.
• Copying formulas efficiently down one or more columns at the same time.
• Improving the integrity of budget spreadsheets by isolating all inputs to a single worksheet.
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify.

Learning Objectives:

After reading the course material, you will be able to:

• Identify the first level of Excels Order of Operations when calculating formulas.
• State where range names assigned to worksheet cells appear within Excels user interface.
• State which tabs within the Format Cells dialog box contains the Hidden checkbox.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

In this on-demand webcast, youll learn from Excel expert David Ringstrom, CPA, how to implement multiple techniques to create spreadsheets that are interactive, accurate, and user friendly. Step-by-step, David shows you how to
use a variety of Excels form controls and features to control the data input of other users, simplify data entry, preserve key formulas, minimize spreadsheet maintenance, 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.

Topics include:

• Contrasting Form Controls in Excel versus ActiveX Controls.
• Discovering the array of form controls available within Excel.
• Crafting data validation lists that are contingent on selections from a preceding list.
• Streamlining data entry in Excel with the CheckBox form control.
• Creating an in-cell list by way of Excels Data Validation feature.
• Using the ComboBox form control to create a searchable drop-down list.
• Creating an input rule that requires names to be entered in a last-name, first-name format.
• Removing data validation from a worksheet when needed.
• Avoiding merging cells—use the Text Box feature for paragraphs of text instead.
• Allowing users to make multiple choices by way of the ListBox form control.

Learning Objectives:

After reading the course material, you will be able to:

• Recall the arguments for Excel's INDEX function.
• State which Goes To Special option allows you to select all form controls on a worksheet at once.
• Identify which versions of Excel permit using slicers with both tables and pivot tables.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

In this empowering presentation, Excel expert David Ringstrom, CPA, demonstrates multiple ways to automate your work in Excel by way of the Custom Views feature—one of the most underutilized features in Excel. David explains how to hide and unhide multiple worksheets at once, freeze or unfreeze worksheet panes on-demand, store multiple print ranges/settings for a given worksheet, gain control over long lists of data by filtering instead of sorting, 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.

Topics include:

• Using Custom Views in Excel 2013 and later to instantly resize the application window for a given workbook.
• Overviewing Excels Custom Views feature and the types of settings that can be managed in this fashion.
• Using Excels Custom Views feature to make a single set of data serves multiple purposes.
• Turning screen elements such as scroll bars, gridlines, worksheet tabs, and more on or off by way of Custom Views.
• Using Excels Custom Views feature to apply different print settings, including orientation and margins, to a single worksheet.
• Freezing or unfreezing worksheet panes on-demand with a custom view.
• Gaining control of long lists of data by filtering instead of sorting.
• Setting a print range that includes multiple noncontiguous areas of a worksheet.
• Making the same edits on multiple worksheets at once by grouping worksheets.
• Using Custom Views to remember frequently selected cell ranges or cursor positions.
• Applying different filter settings with just a couple of mouse clicks using the Custom Views feature.
• Protecting hidden sheets from within a workbook.

Learning Objectives:

After reading the course material, you will be able to:

• Define how to replace an existing custom view with new settings.
• Identify how to remove all custom views from a given workbook.
• Recall how to freeze or unfreeze worksheet panes.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

The process of culling data from text files or from databases, such as Access or SQL Server, can be intimidating to Excel users. In this comprehensive course, Excel expert David Ringstrom, CPA, shows you how to get the data you need into Excel where you then can work with it a variety of ways, including using worksheet functions to summarize data, querying text files and databases from within Excel, creating self-updating links to databases and other data sources, 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.

Topics include:

• Discovering how Microsoft Query allows you to create self-updating links to databases, spreadsheets, text files, and other data sources.
• Understanding what SELECT, FROM, WHERE, ORDER BY, and TOP mean within SQL statements.
• Using the SUMIF function to summarize data based on a single criterion.
• Using the COUNTIF function to determine the number of times an item appears on a list.
• Implementing the SUMIFS function to sum values based on multiple criteria.
• Comparing the AGGREGATE function in Excel 2010 and later to the SUBTOTAL function available in all versions of Excel.
• Streamlining filtering of lists in Excel 2013 and later by using the Slicer feature with tables.
• Using Microsoft Query to extract data from Access databases.
• Eliminating the risk of workbook links by using Microsoft Query to get data from one workbook into another.
• Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
• Removing automatic links between tables within a query in Microsoft Query.
• Applying sort criteria to queries youve established in Microsoft Query.
• Linking data from text files to Excel spreadsheets by way of Microsoft Query.
• Adding new data sources to Microsoft Excel so you can extract data from text files.

Learning Objectives:

After reading the course material, you will be able to:

• Identify how Microsoft Query allows you to link data from text files to Excel spreadsheets.
• Compare the AGGREGATE function in Excel 2010 and later to the SUBTOTAL function available in all versions of Excel.
• Recognize how to duplicate columns, rows, or cells within an Excel worksheet.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

If youre often stopped in your tracks by error prompts you always get when youre performing seemingly simple actions in your spreadsheets, youll benefit from this broad-reaching course. Excel expert David Ringstrom, CPA, delves into the intricacies of Excel to explain why error prompts can be encountered, and he outlines in detail what actions to take when Excel appears to go away.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

User-proofing Excel spreadsheets don't have to be a tedious task, nor does it have to feel Draconian for spreadsheet users. In this presentation Excel expert David Ringstrom, CPA shares how to implement internal control features within your spreadsheets. Techniques covered include Data Validation, Conditional Formatting, hiding worksheets securely, as well as protecting worksheets and workbooks. You'll also see how to improve the integrity of your spreadsheets with VLOOKUP and SUMIF, as well as Excel's Table feature.

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.

Topics include:

• Using Data Validation to create a rule that ensures dates entered within a cell are greater than or equal to todays date.
• Protecting sensitive information by hiding formulas within an Excel workbook.
• Using Excels IF function to make certain fields appear blank until a required input is provided.
• Creating self-expanding drop-down lists with Excels Data Validation feature.
• Toggling the Locked status of a worksheet cell on or off by way of a custom shortcut.
• Creating an in-cell list by way of Excels Data Validation feature.
• Ensuring proper VLOOKUP integrity by using Data Validation to create an in-cell drop-down list.
• Utilizing Data Validation to limit percentages entered in a cell to a specific range of values.
• Using a custom number format to hide zero amounts within a specific area of a spreadsheet.
• Using Excels IFERROR function to mask # sign errors, such as #N/A.
• Viewing two worksheets at the same time within the same workbook.
• Protecting hidden sheets from within a workbook.

Learning Objectives:

After reading the course material, you will be able to:

• Recall the ribbon tab that the Table feature appears within.
• State what can be included within a formula to make a cell appear blank under certain conditions.
• Identify which input is valid for a data validation rule limiting inputs to whole numbers between 0 and 100.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

If you're not presently using look-up formulas such as VLOOKUP, HLOOKUP, SUMIF, and others in your work, then there's much about your experience in Excel that is more difficult than necessary.

In this comprehensive presentation, Excel expert David Ringstrom, CPA, introduces several lookup functions and provides context on when youre likely to use them. He demonstrates troubleshooting techniques and prepares you to deal with nuances that can frustrate users new to lookup functions.

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.

Topics include:

• Improving the integrity of spreadsheets with Excels VLOOKUP function.
• Understanding situations that cause Excels VLOOKUP function to return #REF! instead of a value.
• Future-proofing VLOOKUP by referencing entire columns when available.
• Utilizing Excels IFERROR function to display alternate values when VLOOKUP returns an error.
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
• Future-proofing VLOOKUP by using Excels Table feature versus referencing static ranges.
• Implementing Data Validation to ensure users make choices that VLOOKUP will recognize as valid.
• Learning about the MAXIFS function available in certain versions of Excel 2016.
• Determining if your version of Excel 2016 is perpetually licensed or part of the Office 365 platform, which periodically adds new features to Excel.
• Simplifying multiple-field lookups with concatenation (combining fields into a single cell).

Learning Objectives:

After reading the course material, you will be able to:

• Identify which menu in Excel contains the command that launches Excel's Function Wizard.
• Recall how to determine if you're using the Office 365 version of Excel.
• Recall the keyboard shortcut that toggles absolute references within a formula.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

In this fast-paced presentation Excel expert David Ringstrom, CPA begins with a brief discussion of the IF function and then quickly moves beyond the basics. You'll see the pros and cons of nesting multiple IF functions together, along with that include IFS, CHOOSE, VLOOKUP, SUMIF, and several other worksheet functions. You'll also see techniques useful in confirming that complex formulas, such as nested IF functions, are working properly.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. Hell draw to your attention 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.

Topics include:

• Using the SUMIF function to summarize data based on a single criterion.
• Learning about the IFNA function available in Excel 2013 and later.
• Viewing the pros, cons, and limitations of nesting IF functions.
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
• Returning the inverse of a TRUE/FALSE test by way of Excels NOT function.
• Reconstructing spreadsheet data to use VLOOKUP as a better alternative to nesting IF functions.
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
• Facilitating decision-making within Excel formulas by way of the IF function.
• Understanding when you might wish to use ISERROR or ISNA instead of IFERROR.
• Streamlining the decision-making process with the IFS function in Office 365.
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
• Employing the SUBSTITUTE function as an alternative to complex formulas based on the IF function.

Learning Objectives:

After reading the course material, you will be able to:

• Recall the maximum number of criteria pairs that the SUMIFS function permits.
• Identify the worksheet function that enables you to determine whether at least one logical test returns true.
• Identify the versions of Excel that offer the IFNA worksheet function.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

User-proofing Excel spreadsheets don't have to be a tedious task, nor does it have to feel Draconian for spreadsheet users. In this presentation Excel expert David Ringstrom, CPA shares how to implement internal control features within your spreadsheets. Techniques covered include Data Validation, Conditional Formatting, hiding worksheets securely, as well as protecting worksheets and workbooks. You'll also see how to improve the integrity of your spreadsheets with VLOOKUP and SUMIF, as well as Excel's Table feature.

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.

Topics include:

• Using Data Validation to create a rule that ensures dates entered within a cell are greater than or equal to todays date.
• Protecting sensitive information by hiding formulas within an Excel workbook.
• Using Excels IF function to make certain fields appear blank until a required input is provided.
• Creating self-expanding drop-down lists with Excels Data Validation feature.
• Toggling the Locked status of a worksheet cell on or off by way of a custom shortcut.
• Creating an in-cell list by way of Excels Data Validation feature.
• Ensuring proper VLOOKUP integrity by using Data Validation to create an in-cell drop-down list.
• Utilizing Data Validation to limit percentages entered in a cell to a specific range of values.
• Using a custom number format to hide zero amounts within a specific area of a spreadsheet.
• Using Excels IFERROR function to mask # sign errors, such as #N/A.
• Viewing two worksheets at the same time within the same workbook.
• Protecting hidden sheets from within a workbook.

Learning Objectives:

After reading the course material, you will be able to:

• Recall the ribbon tab that the Table feature appears within.
• State what can be included within a formula to make a cell appear blank under certain conditions.
• Identify which input is valid for a data validation rule limiting inputs to whole numbers between 0 and 100.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

Historically, each version of Excel has been identical for all users, but thats no longer the case. In this on-demand webcast, Excel expert David Ringstrom, CPA, explains how the Office 365 version of Excel 2016 differs from previous versions as well as perpetual licensed versions of Excel 2016. David discusses updated features and worksheet functions available in the Office 365 version of Excel 2016, and he shows you step-by-step how to use them. He also shares helpful keyboard shortcuts and tips.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the Office 365 version of Excel 2016. He draws your attention to any differences in the perpetual licensed versions of Excel 2016, 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. In addition, David provides an Excel workbook that includes most of the examples he uses during the webcast.

Topics include:

• Updating Office 365 to take advantage of recently released features and improvements.
• Visualizing data with a Funnel chart, which is exclusive to the Office 365 version of Excel 2016.
• Preventing pivot tables from automatically resizing columns when you refresh or filter the data.
• Exploring the risks and benefits of participating in the free Microsoft Office Insider program.
• Removing the background from pictures within Microsoft Excel.
• Jump-starting pivot tables by way of the Recommended Pivot Tables feature in Excel 2013 and later.
• Viewing historical versions of documents that have been saved to Microsofts OneDrive service.
• Analyzing your data with artificial intelligence by way of the Insights feature in Office 365.
• Embedding 3D models into Office 365 spreadsheets while seeing whats viewable in other versions of Excel.
• Deselecting one or more cells from within a selection of cells without having to start over again with the selection process.
• Mapping geographic data with just a couple of mouse clicks.

Learning Objectives:

After reading the course material, you will be able to:

• Name recently released features and improvements Office 365 offers.
• Identify the menu where the Recommended PivotTables feature appears in Excel 2013 and later.
• Define how you can easily map geographic data.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

Excel offers multiple features and functions payroll professionals can implement to improve the accuracy and efficiency of payroll production. In this comprehensive course, Excel expert David Ringstrom, CPA, demonstrates and explains: mathematics for employee timesheets, date and time formatting, and stamping, conditional formatting to highlight HR requirements, password protection for sensitive payroll files and worksheets, salary information formatting, and using pivot tables for HR reporting and analysis tasks.

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.

Topics include:

• Redacting portions of Social Security numbers by way of Excels TEXT worksheet function.
• Employing the NETWORKDAYS.INTL worksheet function to determine the number of workdays in a period by excluding holidays as well as specific days of the week.
• Using Excels Text to Columns features to convert Social Security numbers to values and then apply Excels Social Security number format.
• Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting.
• Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.
• Transforming a column of salaries into an instant heat map by way of Excels Conditional Formatting feature.
• Discerning the nuance involved in making pivot tables present data in tabular form.
• Limiting access to sensitive workbooks by way of password protection.
• Removing the Table feature from Excel spreadsheets once its no longer needed or simply erasing the alternate row shading.
• Utilizing the RANDBETWEEN worksheet function to create a series of random numbers.
• Gleaning the nuances of adding time values together in Microsoft Excel.
• Drilling down into the details behind any amount within a pivot table with just a double-click.

Learning Objectives:

After reading the course material, you will be able to:

• Apply the Text to Columns feature to convert Social Security numbers to values and then apply Excels Social Security number format.
• Define how to assign random numbers to a data set in Excel.
• Recall how to remove Conditional Formatting when its no longer needed.

Who Should Attend:

• All Certified Public Accountants (CPAs)

Qualifies and Approved with all State Boards of Accountancy and the following sponsorships:

COURSE DESCRIPTION

In this session Excel expert David H. Ringstrom, CPA, introduces one of the most powerful, and yet overlooked features in Excel: pivot tables. Many users shy away from this feature, thinking that specialized knowledge is required.

As you'll see in this presentation, pivot tables allow you to instantly summarize lists of data into meaningful reports with just a few actions with your mouse. As with many aspects of Excel, pivot tables have several nuances that
can frustrate new users, so David will be sure to bring these to your attention so that you can use the feature effectively.

Topics include:

• Managing information overload by collapsing or expanding pivot table fields.
• Filtering pivot tables to show fewer columns and/or rows of data.
• Presenting the largest or smallest values in chart form by way of a Top 10 pivot chart.
• Filtering pivot table data based on a new dimension by using the Report Filter command.
• Avoiding the dreaded PivotTable Field Name Already Exists error prompt.
• Determining which refresh commands in Excel update a single pivot table versus all pivot tables in a workbook.
• Exploring the nuances of formatting numbers within pivot tables.
• Discovering four different ways to remove data from a pivot table report.
• Converting a pivot table to static numbers for archival purposes or to prevent drilling down into the underlying data.
• Contrasting sorting data within worksheets to the nuances of sorting data within pivot tables.
• Utilizing the Tabular Format command to display pivot table data in two or more columns instead of a single column in Compact Form.
• Distinguishing the differences among pivot table-related menus in Excel 2013 and later versus older versions of Excel.

Learning Objectives:

After reading the course material, you will be able to:

• Recall the requirement that enables the Show Report Filter Pages command.
• Identify which of four ways is not a method for removing fields from a pivot table.
• Recall the requirement that enables the Show Report Filter Pages command

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

In this installment of the Pivot Table series Excel expert David Ringstrom, CPA takes you beyond the basics of pivot tables. You'll learn how pivot tables differ from worksheet formulas, the importance of the Refresh and Report
Filter commands, how to disable the GETPIVOTDATA function, how to drill down into numbers with a simple double-click, and much more. You'll also be empowered to easily transform unwieldy data sets into pivot table-ready lists.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws to your attention 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.

Topics include:

• Understanding the data integrity risks posed by pivot tables when users add additional data to the original source list.
• Building a pivot table report from a list of data.
• Adding a percentage column to a pivot table with just a couple of mouse actions.
• Adding fields to a blank pivot table to create instant reports.
• Creating a pivot table to transform lists of data into on-screen reports.
• Understanding how the pivot chart formatting works much like formatting other types of charts in Excel.
• Improving the integrity of pivot tables by utilizing the Table feature in Excel.
• Filtering data within pivot tables in Excel 2010 and later by way of the Slicer feature.
• Transforming an unwieldy list of data into a format thats ready to be analyzed within a pivot table.
• Developing calculated fields that perform math on data within the source data.
• Utilizing the Tabular Format command to display pivot table data in two or more columns instead of a single column in Compact Form.
• Visualizing lists of data graphically by way of Excels PivotChart feature.

Learning Objectives:

After reading the course material, you will be able to:

• Recall the keyboard shortcut that creates a pivot chart based on an existing pivot table.
• State the mouse action that enables you to reconstruct the underlying pivot table source data.
• Recall which menus appear and disappear as you click within or outside of a pivot table.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

Follow along as Excel expert David Ringstrom, CPA explores several ways to sum data beyond the venerable SUM function. Expanding your knowledge of worksheet functions can markedly improve the resilience and integrity of your spreadsheets. Take summing to new levels with the SUBTOTAL and AGGREGATE functions, while you learn how to have SUMIF and SUMIFS add up values based upon up to 127 criteria. David also will discuss the SUMPRODUCT function.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. Hell draw to your attention 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.

Topics include:

• Using the SUMIF function to summarize data based on a single criterion.
• Using the SUM function to drill through two or more worksheets.
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
• Using Excels OFFSET function to dynamically reference data from one or more accounting periods.
• Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula.
• Comparing the AGGREGATE function in Excel 2010 and later to the SUBTOTAL function available in all versions of Excel.
• Using the SUMIFS function to sum values based on multiple criteria.
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
• Verifying sums and totals quickly by simply selecting cells with your mouse.
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
• Using the SUMIFS function to sum values based on multiple criteria.
• Exploring how the INDIRECT worksheet function can automate pulling values from a group of individual worksheets.

Learning Objectives:

After reading the course material, you will be able to:

• State which versions of Excel that the AGGREGATE function is available in.
• Name what the SUMIFS function returns if a match cannot be found.
• Recall the keyboard shortcut for the AutoSum feature.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

In this extensive presentation Excel expert David Ringstrom, CPA explores the Table feature. This feature offers a dazzling number of automation and data integrity opportunities. Start saving time immediately with pivot tables and formulas that automatically accommodate expanding lists of data. Sift through lists of data with ease, and eliminate the risk of forgetting to copy formulas both up and down a given column.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws to your attention 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.

Topics include:

• Simplifying formula writing by way of table and field names eliminates the need to activate other worksheets.
• Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
• Using the SUMIF function to summarize data based on a single criterion.
• Managing cumbersome lists of data using the Table feature.
• Creating an in-cell list by way of Excels Data Validation feature, and then automating the addition of new items with a table.
• Minimizing a data integrity risk within pivot tables by way of Excels Table feature.
• Improving the integrity of pivot tables by utilizing the Table feature in Excel.
• Improving the integrity of spreadsheets with Excels VLOOKUP function.
• Applying different filter settings with just a couple of mouse clicks using the Custom Views feature.
• Applying different filter settings with just a couple of mouse clicks using the Custom Views feature.
• Determining whether formulas within tables use cell references or field names.
• Avoiding the need to write repetitive formulas using Excels Data Table feature.

Learning Objectives:

After reading the course material, you will be able to:

• Recall the arguments used within Excel's VLOOKUP function.
• Identify which versions of Excel permit using slicers with both tables and pivot tables.
• Identify the feature that makes charts expand automatically as you add additional data to the source range.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

Youll discover how to implement Excels powerful What-If Analysis Tools in this course presented by Excel expert David Ringstrom, CPA. David outlines how and when to make use of Excels Scenario Manager as well as how to use the Data Table feature to compare calculation results based on two or three inputs. Youll also learn how to implement Excels Goal Seek feature to perform basic what-if analysis to solve for a single value.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. Hell draw to your attention 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.

Topics include:

• Enhancing the Summary Report generated by the Scenario Manager with range names.
• Using the Summary Report aspect of Scenario Manager to compare different scenarios side by side.
• Implementing a simple Excel macro that will empower you to apply multiple scenarios at once when you have more than 32 inputs.
• Exploring the Forecast Sheet feature in Excel 2016, which can extrapolate trends based on existing data in your spreadsheets.
• Enabling Excels Solver Add-in for more complex what-if analyses.
• Pausing Goal Seek when necessary to check the status of calculations that involve large numbers.
• Contrasting Excels Data Table feature with traditional approaches used in spreadsheets.
• Avoiding the need to write repetitive formulas with Excels Data Table feature.
• Utilizing the PMT worksheet function to calculate loan payment amounts.

Learning Objectives:

After reading the course material, you will be able to:

• Describe how to apply Excels Solver Add-in for more complex what-if analyses.
• Apply the PMT worksheet function to calculate loan payment amounts.
• Define how to make one workbook serve multiple purposes by way of Excels Scenario Manager.

Who Should Attend:

• All Certified Public Accountants (CPAs)

COURSE DESCRIPTION

In this comprehensive course, Excel expert David Ringstrom, CPA, explains the pros and cons of workbook links and takes you step-by-step through the process of creating and working with them. Workbook links are of great benefit, as they allow you to connect one Excel spreadsheet to other spreadsheets, Word documents, databases, and even web pages. David also covers how to dramatically improve the integrity of linked workbooks, copy links across rows or down columns, repair broken links, 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.

Topics include:

• Controlling whether or not linked workbooks update automatically and how to suppress unwanted prompts.
• Creating flexible workbook links that can be copied down columns or across rows.
• Learning about a free tool you can use to locate even the most buried workbook links.
• Eliminating the risk of workbook links by using Microsoft Query to get data from one workbook into another.
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
• Integrating data on web pages into your workbooks and creating automatic links.
• Learning how to arrange two worksheets from within the same workbook on-screen and at the same time.
• Learning how to break links to other workbooks and data sources.
• Learning how to repair broken workbook links.
• Learning the most efficient way to link Word documents and Excel spreadsheets.
• Learning the risks of linked workbooks, determining if a workbook contains links, and seeing how links can hide within Excel features.
• Learning which functions to avoid when creating workbook links.

Learning Objectives:

After reading the course material, you will be able to:

• Identify and take control of security prompts and settings related to linked workbooks.
• Recall how to determine if a workbook contains links.
• Describe how to break links to other workbooks and data sources.

Who Should Attend:

• All Certified Public Accountants (CPAs)