84 Enrolled
32 Hours (On-Demand)
Instructor for this course
more

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.
Last updated/reviewed: November 13, 2019

4 Reviews (16 ratings)Reviews

4
Anonymous Author
Overall, this course was very thorough and easy to follow. I would suggest breaking the material up into different courses as the cumulative exam was quite lengthy.
4
Anonymous Author
Overall it was a good course. Some repeating of information, but that's ok.
5
Anonymous Author
GREAT COURSE, LIKED IT VERY MUCH. VERY DETAILED. FOR INTERMEDIATE LEVEL
5
Member's Profile
Very cool course! Many really useful things have been explained.

Prerequisites

Course Complexity: Intermediate

No Advanced Preparation or Prerequisites are needed for this course.

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
Excel Efficiency: Pivot Tables
  1:40:02Excel Efficiency: Pivot Tables
  quizREVIEW QUESTIONS:Excel Efficiency: Pivot Tables
Excel Efficiency: Intermediate Pivot Tables
  1:40:53Excel Efficiency: Intermediate Pivot Tables
  quizREVIEW QUESTIONS:Excel Efficiency: Intermediate Pivot Tables
Excel Efficiency: Advanced Pivot Tables
  1:40:24Excel Efficiency: Advanced Pivot Tables
  quizREVIEW QUESTIONS:Excel Efficiency: Advanced Pivot Tables
Excel Efficiency: VLOOKUP
  1:41:39Excel Efficiency: VLOOKUP
  quizREVIEW QUESTION:Excel Efficiency: VLOOKUP
Excel Efficiency: Taming Large Spreadsheets
  1:46:28Excel Efficiency: Taming Large Spreadsheets
  quizREVIEW QUESTIONS:Excel Efficiency:Taming Large Spreadsheets
Excel Efficiency: Interactive Spreadsheets
  1:44:01Excel Efficiency: Interactive Spreadsheets
  quizREVIEW QUESTIONS:Excel Efficiency: Interactive Spreadsheets
Excel Efficiency: Excel Chart Speed Tips
  1:42:49Excel Efficiency: Excel Chart Speed Tips
  quizREVIEW QUESTION:Excel Efficiency:Excel Chart Speed Tips
Excel Efficency: Table Feature
  1:40:54Excel Efficency: Table Feature
  quizREVIEW QUESTIONS:Excel Efficiency: Table Feature
Excel Efficiency: Workbook Links
  1:40:00Excel Efficiency: Workbook Links
  quizREVIEW QUESTIONS:Excel Efficiency: Workbook Links
Excel Efficiency: Database Techniques
  1:44:29Excel Efficiency: Database Techniques
  quizREVIEW QUESTIONS:Excel Efficiency: Database Techniques
Excel Efficiency: Custom Views
  1:40:52Excel Efficiency: Custom Views
  quizREVIEW QUESTIONS:Excel Efficiency: Custom Views
Excel Efficiency: Logic Functions
  1:45:29Excel Efficiency: Logic Functions
  quizREVIEW QUESTIONS:Excel Efficiency: Logic Functions
Excel Efficiency: Internal Control
  1:40:29Excel Efficiency: Internal Control
  quizREVIEW QUESTIONS:Excel Efficiency: Internal Control
Excel Efficiency: Error Prompts Explained
  1:44:44Excel Efficiency: Error Prompts Explained
  quizREVIEW QUESTIONS:Excel Efficiency: Error Prompts Explained
Excel Efficiency: Minimizing Worksheet Errors
  1:40:07Excel Efficiency: Minimizing Worksheet Errors
  quizREVIEW QUESTIONS:Excel Efficiency: Minimizing Worksheet Errors
Excel Efficiency: Auditing Spreadsheets
  1:48:10Excel Efficiency: Auditing Spreadsheets
  quizREVIEW QUESTION:Excel Efficiency: Auditing Spreadsheets
SUPPORTING MATERIALS
  ZIPExpert Excel Suppporting Materials
FINAL EXAM
 examFINAL EXAM