Get ahead before the holidays. Individual webinars are $149 throughout October. *Pricing automatically applied in cart.

On-Demand Webinar

Maximize Excel: Certification Exam Prep

  • Recorded January 1, 2026
  • 100 Minutes
  • Available now
CE credits
  • 2.00 CPE (Continuing Professional Education)

What You'll Learn

In this Microsoft Excel Certification Exam Prep presentation, author and Microsoft Excel expert David H. Ringstrom, CPA, walks you through a wide range of topics that frequently appear on the MO-210 certification exam and are essential for real-world productivity. You’ll learn practical skills such as importing data, filtering records, and linking workbooks; streamlining navigation with named ranges; and using tools like Sparkline and chart sheets. David also explains how to work with newer Excel functions such as UNIQUE, SEQUENCE, and RANDBETWEEN, as well as modern cell references—including spilled ranges. Throughout the session, you’ll discover efficiency tips like customizing the Quick Access Toolbar, using Show Formulas to audit worksheets, and improving accessibility by avoiding image-based data. Whether you’re preparing for certification or sharpening your skills, this session provides essential insights to elevate your Excel game.
  1. Integrating data on web pages into your workbooks and creating automatic links.
  2. Saving Excel workbooks in alternate formats, such as .XLS for backwards compatibility.
  3. Displaying all formulas in a worksheet at once with the Show Formulas feature.
  4. Reviewing and updating workbook properties.
  5. Adding a recorded macro to the Quick Access Toolbar for quick access.
  6. Reorganizing the Quick Access toolbar to prioritize frequently used keyboard shortcuts.
  7. Making the same edits on multiple worksheets at once by grouping worksheets.
  8. Streamlining navigation and formula writing by turning worksheet labels into named ranges.
  9. Creating line, column, and win/loss sparklines to show data trends.
  10. Creating a dynamic list of period numbers in an amortization table with the SEQUENCE function.
  11. Adding totals and filters that automatically adjust based on which rows are visible
  12. Comparing relative, mixed, absolute, named, structured, and spilled range cell references.
  13. Pairing the trim (.) and spill (#) operators with UNIQUE and SUMIF in Microsoft 365
  14. Making formulas easier to read, more resilient, and table-aware.
  15. Utilizing chart sheets to easily fit multiple charts onto a single printed page.
  16. Specifying chart styles and borders.
View Full Description

David H. Ringstrom, CPA, has more than 30 years of experience as a spreadsheet and accounting software consultant and speaker. He has presented over 2,500 live webinars and is the author or co-author of ten books, including “Microsoft 365 Excel All-in-One for Dummies”, “Microsoft 365 Excel for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and “QuickBooks Online for Dummies”.

In his webinars, David demonstrates every technique twice: first on a PowerPoint slide with numbered steps, and then live in Excel for Microsoft 365 for Windows. He highlights any differences in Excel 2024, 2021, or 2019 during the presentation and in his detailed handouts. Attendees also receive an Excel workbook containing most of the examples he uses, making it easy to follow along and apply the techniques later. David additionally supports Excel for Mac users by answering their follow-up questions via email.

Who should attend:

Professionals seeking to take the MO-210 Microsoft Office Specialist: Excel Associate (Microsoft 365 Apps) exam.


Learning objectives:

  • Recall the key that allows you to group a series of adjacent worksheets at once.
  • Recall the keyboard shortcut that toggles absolute references within a formula.
  • Describe how Excel Tables and structured references improve formula readability and cross-sheet referencing.

Level:

Intermediate

Format:

Self-Study

Instructional Method:

On-Demand Webcast

NASBA Field of Study:

Computer Software & Applications

Program Prerequisites:

Prior experience with Microsoft Excel is recommended.

Advance Preparation:

None


Meet Your Speaker

David H. Ringstrom, CPA

David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in...

View Full Bio of David H. Ringstrom, CPA

CE Credits

These are the continuing education credits available to you for this webinar.

  • 2.00 CPE Continuing Professional Education

    Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

    For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

     You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.
Table of Contents
  1. Maximize Excel Certification Exam Prep 00:00:01
  2. Certification Exam Overview 00:01:05
  3. Presenting with Microsoft 365 for Windows 00:02:17
  4. Section 1: Manage Worksheets and Workbooks (25–30%) 00:04:42
  5. Import Text Files with Power Query 00:06:29
  6. Import Text Files with Power Query 00:11:13
  7. Import Text Files with Power Query 00:15:39
  8. Import Text Files with Power Query 00:19:23
  9. Extracting Online Data with the From Web Feature 00:22:34
  10. Streamlining Navigation with Hyperlinks 00:26:32
  11. Showing All Worksheet Formulas 00:31:59
  12. Adjusting Workbook Properties 00:37:40
  13. Adding Commands to the Quick Access Toolbar 00:39:53
  14. Reorganizing the Quick Access Toolbar 00:45:45
  15. Section 2: Manage Data Cells and Ranges (25–30%) 00:47:06
  16. Group Worksheets 00:49:18
  17. Creating Named Ranges from Worksheet Labels 00:53:25
  18. Using the Sparklines Feature 00:59:10
  19. Using the RANDBETWEEN Function 01:04:20
  20. SEQUENCE Function (Excel 2021+) 01:09:47
  21. Section 3: Manage Tables and Table Data (10–15%) 01:15:05
  22. Transforming Static Lists into Dynamic Tables 01:16:37
  23. Customizing Table Styles 01:24:06
  24. Workbook Themes Versus Table Styles 01:27:42
  25. Section 4: Using Formulas and Functions (15–20%) 01:29:30
  26. Contrasting Cell Reference Types 01:30:10
  27. Combining UNIQUE and SUMIF with Spill & Trim 01:34:57
  28. Comparing Standard References to Structured 01:39:27
  29. Section 5: Manage Charts (15–20%) 01:42:01
  30. Placing Multiple Charts on a Chart Sheet 01:43:08
  31. Applying Chart Styles and Borders 01:44:58
  32. Adding Alt Text to Images and Objects 01:46:44
  33. Presentation Summary 01:48:13
  34. Now It’s Your Turn—But I’m Here If You Need Me! 01:49:11
Index
  • Chart Sheet 01:42:13, 01:42:13, 01:42:21, 01:43:08, 01:43:17, 01:43:17, 01:43:31, 01:43:41, 01:44:01, 01:44:03, 01:44:27, 01:44:39, 01:44:41, 01:45:49, 01:48:25
  • Column 00:07:34, 00:08:54, 00:11:27, 00:11:27, 00:11:40, 00:11:40, 00:11:55, 00:11:58, 00:11:58, 00:12:10, 00:12:10, 00:12:10, 00:12:18, 00:12:18, 00:13:11, 00:13:34, 00:14:00, 00:14:00, 00:14:00, 00:14:24, 00:15:28, 00:16:29, 00:16:51, 00:18:54, 00:27:12, 00:28:07, 00:33:20, 00:34:58, 00:35:03, 00:35:03, 00:35:51, 00:36:02, 00:50:39, 00:54:10, 00:55:00, 00:56:14, 01:02:38, 01:03:04, 01:06:24, 01:08:48, 01:10:02, 01:11:31, 01:11:48, 01:12:08, 01:13:59, 01:17:44, 01:18:13, 01:30:28, 01:30:35, 01:30:35, 01:30:49, 01:30:49, 01:31:06, 01:31:20, 01:31:20, 01:31:20, 01:31:58, 01:32:08, 01:36:22, 01:36:41, 01:36:41, 01:36:41, 01:36:41, 01:36:41, 01:37:57, 01:37:57, 01:39:37, 01:41:05, 01:41:16, 01:41:55
  • Formula 00:02:10, 00:05:59, 00:05:59, 00:16:10, 00:31:59, 00:32:15, 00:32:15, 00:32:47, 00:33:20, 00:33:20, 00:33:20, 00:34:19, 00:34:24, 00:34:24, 00:34:24, 00:34:24, 00:34:24, 00:34:24, 00:34:24, 00:34:58, 00:35:03, 00:35:03, 00:35:03, 00:35:19, 00:35:51, 00:37:10, 00:37:10, 00:48:24, 00:48:24, 00:48:24, 00:53:29, 00:54:01, 00:54:28, 00:54:35, 00:54:35, 00:55:53, 00:55:53, 00:56:22, 01:05:13, 01:06:24, 01:11:17, 01:11:31, 01:11:38, 01:11:48, 01:17:44, 01:18:18, 01:18:50, 01:23:37, 01:23:47, 01:23:47, 01:29:30, 01:29:43, 01:30:28, 01:30:59, 01:31:32, 01:31:45, 01:31:51, 01:32:31, 01:33:12, 01:33:45, 01:34:04, 01:34:57, 01:36:09, 01:37:31, 01:38:48, 01:39:18, 01:39:27, 01:39:37, 01:40:17, 01:40:45, 01:41:16, 01:41:36, 01:42:01
  • Group Worksheets 00:47:36, 00:47:41, 00:47:55, 00:49:18, 00:50:25, 00:53:00, 01:48:25
  • Pivot Table 00:21:25, 00:21:32, 00:21:32, 00:21:44, 00:21:50, 00:22:04, 01:06:24, 01:06:24, 01:07:14, 01:19:29, 01:19:54, 01:19:57, 01:20:03, 01:20:08, 01:20:31, 01:21:21, 01:21:33, 01:21:33, 01:21:43, 01:22:05, 01:22:07, 01:35:24
  • Power Query 00:04:42, 00:04:49, 00:05:05, 00:05:05, 00:05:33, 00:06:29, 00:06:41, 00:06:59, 00:07:05, 00:07:34, 00:07:34, 00:07:34, 00:08:35, 00:08:35, 00:08:43, 00:09:19, 00:11:13, 00:12:28, 00:12:41, 00:13:05, 00:14:24, 00:15:39, 00:15:44, 00:16:34, 00:18:54, 00:20:30, 00:20:42, 00:20:51, 00:21:32, 00:21:56, 00:22:57, 00:25:35, 00:31:37, 00:31:43, 00:31:46
  • Range Names 00:48:10, 00:48:34, 00:53:25, 00:54:06, 00:55:57, 00:56:29, 00:57:46, 01:15:47, 01:48:25
  • Row 00:03:40, 00:04:07, 00:05:05, 00:07:34, 00:08:07, 00:11:21, 00:11:27, 00:11:40, 00:11:40, 00:13:11, 00:13:23, 00:13:25, 00:13:28, 00:13:34, 00:13:47, 00:14:15, 00:14:15, 00:14:24, 00:14:51, 00:33:20, 00:36:02, 00:37:20, 00:45:59, 00:50:39, 00:53:35, 00:55:00, 01:07:27, 01:07:33, 01:10:02, 01:10:24, 01:11:38, 01:11:48, 01:12:08, 01:12:20, 01:13:59, 01:17:22, 01:17:30, 01:17:50, 01:18:13, 01:18:18, 01:20:58, 01:24:14, 01:24:14, 01:24:20, 01:24:55, 01:24:55, 01:26:55, 01:26:57, 01:26:57, 01:27:02, 01:27:03, 01:30:28, 01:30:35, 01:30:35, 01:30:49, 01:30:49, 01:31:20, 01:32:08, 01:35:38, 01:36:22, 01:36:57, 01:38:14, 01:41:43
  • SEQUENCE 00:45:23, 00:49:09, 01:09:47, 01:09:59, 01:10:45, 01:11:02, 01:11:48, 01:11:48, 01:32:27, 01:34:01, 01:48:25
  • Sparkline 00:48:46, 00:59:10, 00:59:10, 00:59:24, 00:59:44, 00:59:54, 01:00:02, 01:00:10, 01:00:20, 01:00:20, 01:00:27, 01:02:03, 01:02:10, 01:02:19, 01:02:38, 01:02:44, 01:02:55, 01:03:14, 01:03:27, 01:03:36, 01:48:25
  • Spilled Range Operator 01:11:36, 01:29:43, 01:32:16, 01:36:41, 01:36:57, 01:38:04
  • Table Feature 00:01:51, 00:01:57, 00:21:06, 00:21:32, 01:15:05, 01:15:15, 01:15:15, 01:15:27, 01:15:57, 01:16:04, 01:16:50, 01:17:02, 01:17:38, 01:18:30, 01:19:02, 01:19:26, 01:20:19, 01:20:28, 01:21:21, 01:23:37, 01:23:47, 01:24:06, 01:24:42, 01:24:55, 01:24:55, 01:25:24, 01:25:27, 01:25:35, 01:26:02, 01:26:09, 01:26:21, 01:26:30, 01:27:06, 01:27:06, 01:27:31, 01:27:49, 01:27:49, 01:31:58, 01:32:08, 01:39:37, 01:40:08, 01:42:27, 01:48:25, 01:48:25
  • Worksheets 00:01:13, 00:01:42, 00:05:59, 00:09:19, 00:21:16, 00:21:16, 00:27:12, 00:28:22, 00:28:42, 00:29:07, 00:29:41, 00:29:41, 00:31:59, 00:33:20, 00:33:20, 00:35:19, 00:35:32, 00:37:03, 00:40:15, 00:47:36, 00:47:41, 00:47:55, 00:48:10, 00:48:46, 00:48:53, 00:49:09, 00:49:18, 00:49:18, 00:49:35, 00:49:46, 00:49:52, 00:50:02, 00:50:02, 00:50:07, 00:50:20, 00:50:25, 00:50:39, 00:50:39, 00:50:39, 00:50:39, 00:50:39, 00:51:53, 00:52:13, 00:52:25, 00:52:25, 00:52:53, 00:53:00, 00:53:25, 00:53:29, 00:54:21, 00:55:00, 00:55:24, 00:56:45, 00:59:10, 00:59:24, 01:00:02, 01:00:10, 01:00:20, 01:00:27, 01:00:31, 01:00:38, 01:00:38, 01:01:05, 01:01:10, 01:01:10, 01:03:14, 01:04:53, 01:04:53, 01:05:01, 01:05:01, 01:08:48, 01:17:50, 01:17:56, 01:19:11, 01:19:21, 01:20:34, 01:20:37, 01:20:54, 01:21:21, 01:31:58, 01:41:36, 01:43:08, 01:43:31, 01:43:34, 01:43:41, 01:44:51, 01:45:49, 01:45:56, 01:46:53, 01:48:25 

Key Terms
Chart Sheet
A chart sheet is a workbook tab that contains one or more charts but no worksheet cells.
Column
A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Formula
A formula is an expression which calculates the value of a cell.
Group Worksheets
To group two or more worksheets, hold down the Shift key to select a range of sheets or the Ctrl key to select individual sheets. When sheets are grouped, any changes are made simultaneously on any other worksheets presently included in the group.
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.
Power Query
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Range Names
A user-defined identifier for a cell or block of cells. Range names simplify formula writing and auditing, as the words SalesTax are readily identifiable within a formula as opposed to $B$1. Range names cannot contain spaces; must begin with a letter; be no longer than 255 characters in length; and can be comprised of only letters, numbers, underscores, periods, and slashes.
Row
A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
SEQUENCE
The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.
Sparkline
A sparkline is a very small line chart, typically drawn without axes or coordinates. It presents the general shape of the variation in some measurement, such as temperature or stock market price, in a simple and highly condensed way. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.
Spilled Range Operator
Spilled Range Operator (Page 28): In Microsoft 365 the # symbol can be used after a cell reference in certain instances when the cell reference points to a cell that contains a dynamic array function. A #REF! error will occur when the Spilled Range Operator is used inappropriately.
Table Feature
The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Worksheets
A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.