In this combined "Excel Efficiency" course series Excel expert David Ringstrom, CPA, explores the various tools, techniques, and uses of Microsoft Excel.
First you’ll explore pivot tables, a feature that nearly 80% of Excel users don’t use, despite it’s great utility. David begins by covering the basics of creating and using pivot tables before moving on to more complex topics like 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, 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.
Next David explains helpful ways you can improve the integrity of your spreadsheets using Excel’s lookup functions, including a comparison of exact and approximate matches with VLOOKUP, and then comparisons to the HLOOKUP and LOOKUP functions.
Large and interactive spreadsheets are explored next. David points out some of the nuances and best practices of working with large workbooks and worksheets, as well as how to manage workbooks and worksheets with multiple users. He’ll also explain how to dramatically improve the integrity of linked workbooks, copy links across rows or down columns, repair broken links, and more.
We then learn how to create dynamic Excel charts that will save you time by looking at several helpful features, including the Recommended Charts feature, the Slicer feature, the Sparkline feature, the PivotChart feature, and more. In addition, he explains how to avoid repetitive formatting, create self-updating chart titles, and liven up your charts with clip art.
David then moves into charts and tables, teaching you step-by-step how to create and manage charts and tables with zero coding, as well as how to work with data extracted from databases like Access or SQL Server.
Continue learning about Excel and how to automate your work with Custom Views (an often-overlooked feature in Excel), how to use logic functions most effectively, and how to implement internal controls within your Excel spreadsheets.
Of course, a course on Excel wouldn’t be complete without exploring the problem of errors. David dives deep into the nuances of Excel to explain frequently encountered error prompts, as well as what to do to recover from or mitigate errors that trigger prompts. He’ll also explore how to minimize errors in the first place.
Finally, learn some of David’s favorite spreadsheet auditing techniques including how to determine at a glance if a spreadsheet contains links to other documents, as well as explore nuances surrounding cell comments, plus much more.
Learning Objectives
- Identify the location of the pivot table-related Subtotals command within Excel's ribbon menu interface, the location of the Field List command within Excel's ribbon menu interface, and which of four ways is not a method for removing fields from a pivot table.
- Recognize which menus appear and disappear, explore how to enable or disable GETPIVOTTABLE function, and learn how to use the slicer feature.
- Identify what you can use instead of TRUE in VLOOKUP and the purpose of the IFERROR function.
- Discover how to use the Watch Window and Custom Views features.
- Identify which features and options are best to select all form controls and store text or data.
- Discover new features and how to improve the integrity of your charts.
- Identify which versions of Excel permit using slicers with both tables and pivot tables and which feature that makes charts expand automatically as you add additional data to the source range.
- Learn how to link Excel workbooks to other sources and keep them secure.
- Identify the worksheet function that enables you to determine whether at least one logical test returns TRUE and what the SUMIFS function returns if a match can’t be found.
- Recognize the location of the menu command that allows you to determine categorically if a workbook contains links or not and the location of the Enable Iterative Calculations setting within the Excel Options dialog box.
- Discover how to manage iterative calculations (also known as circular references) within Excel workbooks.
34 Reviews (103 ratings)
Prerequisites
No Advanced Preparation or Prerequisites are needed for this course.
Education Provider Information
Course Questions and Answers( Questions)
![Instructor for this course Instructor for this course](https://www.illumeo.com/sites/proformative.com/files/imagecache/user_image_large/pictures/picture-68826-1569878597.jpg)
Some may argue that 18 is not between 0 and 18