Excel University Volume 2 Course (11 CPE, 1 Year)

(EUV2OD1)

Overview

Excel University Volume 2 builds upon the foundation set in Volume 1, and explores ways to automate recurring-use reports. We refer to workbooks that update their report values automatically when new data is inserted into the workbook as “hands-free” reports. Volume 2 demonstrates how to set up a workbook for hands-free reporting. The features, functions, and techniques presented in this course are sure to shave valuable time off of your reporting tasks.

 

 

  • Opening Information

    • • Chapter 1: Overview
    • • Chapter 2: Selected Shortcuts
  • Conditional Summing

    • • Chapter 3: Conditional Summing Basics with SUMIFS
    • • Chapter 4: Remove Duplicates
  • Lookups

    • • Chapter 5: Lookup Basics
    • • Chapter 6: Improving VLOOKUP with MATCH
    • • Chapter 7: Improving VLOOKUP with VALUE and TEXT
    • • Chapter 8: Moving Beyond VLOOKUP with INDEX
    • • Chapter 9: Trap Errors with IFERROR
    • • Chapter 10: The IF Function
    • • Chapter 11: List Comparisons
    • • Chapter 12: The ISERROR Function
    • • Chapter 13: Multicolumn List Comparisons with COUNTIFS
    • • Chapter 14: Indentation
    • • Chapter 15: Perform Lookups with SUMIFS
  • Date and Text Function Basics

    • • Chapter 16: Determine the Last Day of the Month with EOMONTH
    • • Chapter 17: Date Parts – MONTH and YEAR
    • • Chapter 18: Build Your Own Date with DATE
    • • Chapter 19: Concatenation Basics
  • Hands-Free Reporting

    • • Chapter 20: Improve SUMIFS with CONCATENATE
    • • Chapter 21: Dynamic Headers
    • • Chapter 22: Horizontal Reports
    • • Chapter 23: Mapping Tables
    • • Chapter 24: Data Validation and Reporting
    • • Chapter 25: Improving Error Check
    • • Chapter 26: Concepts Applied
  • Author

    Jeff Lenning CPA CITP
  • Subject

    Excel University Volume 2
  • Benefits

    Begin Now: begin training immediately
    Moderated: ask questions in the moderated forums
    Certificate: download and print your certificate instantly upon completion
    Pause and Resume: pause and resume training as needed
    Exam: unlimited exam attempts
  • Software

    Featuring Microsoft Excel for Windows Versions
  • Format

    On-Demand
  • Access Begins

    Immediately
  • Duration

    Designed around an 8 week schedule; typical participants spend 1-2 hours per week. You may work at your own speed, either faster or slower than the suggested eight week schedule.
  • Certificate of Completion

    Download instantly once a passing score on the final exam is achieved. You may re-attempt the final exam as many times as necessary.
  • Enrollment Period

    Your enrollment is valid for one year.

Format Descriptions

Courses are designed around a weekly schedule. Access to the content depends upon the course format.

  • Scheduled: Content is initially made available on the scheduled course start date. The weekly content is unlocked on a schedule, typically each Monday, throughout the duration of the course.
  • On-Demand: Content is initially made available immediately after you enroll and complete the online purchase transaction. The weekly content is unlocked as you obtain a passing score on each weekly quiz. This allows you to work at your own pace, and you are free to work through the entire course in a single week, or, you can take your time and finish it over 6 months. You truly work at your own pace according to your schedule.
  • Week 1

  • Lecture Videos
  • • Administration (7:46)
  • • Ch 1 Overview (3:52)
  • • Ch 2 Selected Shortcuts (7:12)
  • • Ch 3 Conditional Summing Basics (4:31)
  • • Ch 4 Remove Duplicates (3:38)
  • Homework Activities
  • • Excel Version Poll
  • • Introductions Forum Post
  • • Exercise Workbooks
  • • Week 1 Forum Post
  • Quiz
  • • Week 1 Quiz
  • Additional Resources
  • • Answer Files
  • • Shortcut Reference
  • • Chapter 2 Solutions Video
  • • Chapter 3 Solutions Video
  • • Chapter 4 Solutions Video
  • • Homework and content related questions forum
  • • How to Flatten Data Blog Post
  • Week 2

  • Lecture Videos
  • • Ch 5 Lookup Basics - Part 1 (11:21)
  • • Ch 5 Lookup Basics - Part 2 (9:41)
  • • Ch 6 Improve VLOOKUP with MATCH (9:01)
  • Homework Activities
  • • Wk 2 Poll
  • • Exercise Workbooks
  • • Wk 2 Forum Post
  • Quiz
  • • Week 2 Quiz
  • Additional Resources
  • • Answer Files
  • • Chapter 5 Solutions Video
  • • Chapter 6 Solutions Video
  • • Homework and content related questions forum
  • Week 3

  • Lecture Videos
  • • Chapter 7: Improve VLOOKUP with VALUE and TEXT (7:10)
  • • Chapter 8: Moving Beyond VLOOKUP with INDEX (8:30)
  • • Chapter 9 Trap Errors with IFERROR (4:15)
  • • Chapter 10 The IF Function (3:32)
  • Homework Activities
  • • Wk 3 Poll
  • • Exercise Workbooks
  • • Wk 3 Formula Challenge
  • • Wk 3 Forum Post
  • Quiz
  • • Week 3 Quiz
  • Additional Resources
  • • Answer Files
  • • Chapter 7 Solutions Video
  • • Chapter 8 Solutions Video
  • • Chapter 9 Solutions Video
  • • Chapter 10 Solutions Video
  • • Homework and content related questions forum
  • Week 4

  • Lecture Videos
  • • Chapter 11 List Comparisons (3:30)
  • • Chapter 12 The ISERROR Function
  • • Chapter 13 Multicolumn List Comparisons with COUNTIFS (6:46)
  • • Chapter 14 Indentation (6:14)
  • Homework Activities
  • • Wk 4 Poll
  • • Exercise Workbook
  • • Wk 4 Forum Post
  • Quiz
  • • Week 4 Quiz
  • Additional Resources
  • • Answer File
  • • Chapter 11 Solutions Video
  • • Chapter 12 Solutions Video
  • • Chapter 13 Solutions Video
  • • Chapter 14 Solutions Video
  • • Homework and content related questions forum
  • Week 5

  • Lecture Videos
  • • Chapter 15 Perform Lookups with SUMIFS (8:02)
  • • Chapter 16 Determine the Last Day of the Month with EOMONTH (7:50)
  • • Chapter 17 Date Parts MONTH and YEAR (2:40)
  • • Chapter 18 Build Your Own Date with DATE (5:17)
  • Homework Activities
  • • Wk 5 Poll
  • • Exercise Workbooks
  • • Wk 5 Formula Challenge
  • • Wk 5 Forum Post
  • Quiz
  • • Week 5 Quiz
  • Additional Resources
  • • Answer Files
  • • Chapter 15 Solutions Video
  • • Chapter 16 Solutions Video
  • • Chapter 17 Solutions Video
  • • Chapter 18 Solutions Video
  • • Homework and content related questions forum
  • Week 6

  • Lecture Videos
  • • Chapter 19 Concatenation Basics (7:42)
  • • Chapter 20 Improve SUMIFS with CONCATENATE (9:59)
  • • Chapter 21 Dynamic Headers (6:44)
  • • Chapter 22 Horizontal Reports (4:40)
  • Homework Activities
  • • Wk 6 Poll
  • • Exercise Workbooks
  • • Wk 6 Forum Post
  • Quiz
  • • Week 6 Quiz
  • Additional Resources
  • • Answer Files
  • • Chapter 19 Solutions Video
  • • Chapter 20 Solutions Video
  • • Chapter 21 Solutions Video
  • • Chapter 22 Solutions Video
  • • Homework and content related questions forum
  • Week 7

  • Lecture Videos
  • • Chapter 23 Mapping Tables (5:02)
  • • Chapter 24 Data Validation and Reporting (7:19)
  • • Chapter 25 Improve Error Check (9:57)
  • • Chapter 26 Concepts Applied (4:57)
  • Homework Activities
  • • Wk 7 Poll
  • • Exercise Workbooks
  • • Wk 7 Formula Challenge
  • • Wk 7 Forum Post
  • Quiz
  • • Week 7 Quiz
  • Additional Resources
  • • Answer Files
  • • Chapter 23 Solutions Video
  • • Chapter 24 Solutions video
  • • Chapter 25 Solutions video
  • • Chapter 26 Solutions video
  • • Homework and content related questions forum
  • Week 8

  • Lecture Videos
  • • Graduation (7:25)
  • Exam and Certificate
  • • Final Exam
  • • Certificate of Completion
  • Homework Activities
  • • Course Recap Forum Post
  • • Spread the Word
  • • Keep in Touch
  • • Success Stories
  • • Course Evaluation
  • Additional Resources
  • • Shortcut Reference
  • • Contents at a Glance

Materials

This course teaches the content of the Excel University Volume 2 book. While obtaining a copy of the book is not required in order to work through the online course, most participants enjoy having a copy as they work along and to keep as a reference.


Since there are six different versions of the book (Paperback, Kindle, iTunes for Excel 2010 and 2013), it is easier if participants purchase this book on their own. So, we discounted the price of the online course in order to enable participants to purchase the book directly through online retailers Amazon and Apple.


Use the related products link below to obtain a copy of the book if desired.

Comments from course participants

  • • "Very engaging, fun class!"

  • • "Valuable resource to increase personal organization and productivity."

  • • "SUMIFS function and mapping tables were valuable."

  • • "Enthusiastic and engaging!"

  • • "Excellent presenter, would recommend."

  • • "Very good training, would recommend to other professionals."

  • • "INDEX and VALUE functions were valuable."

  • • "Being exposed to new formulas and functions was valuable."

  • • "Book is great for my future reference."

  • • "Great course, really going to try to implement new things learned."

  • • "Thank you goes to Jeff for great training- very helpful and useful."

  • • "Really enjoyed Jeff. Very knowledgeable and great presenter. Exercises were also very helpful."

NASBA QAS CPE Microsoft Excel LogoObjectives

  • • Identify which Excel function can be used to perform a multicolumn list comparison
  • • Recognize which Excel function can return the last day of the month
  • • Recall at least three keyboard shortcuts and the arguments of at least three functions

Program Level

Intermediate

Prerequisites

Excel University Volume 1

Advance Preparation Needed

Ensure you have access to Excel for Windows, preferably versions 2010 or later, so that you can work through the required homework exercises. Excel for Mac is not officially supported.

Delivery Method

QAS Self-study

Recommended CPE Credits

11

Field of Study

Computer Science

Expiration

Program enrollment is valid for one year and participant must complete the final exam within this time period.

Refund Policy

Full refund through Week 2; full refund for any course cancellations or registration into upcoming session if preferred.

Complaint Resolution

For information regarding adminstrative policies such as complaint resolution, please contact Jeff Lenning CPA CITP at 949-200-7688 or via email info@excel-university.com

 

Price: $249.00
Buy 2 or more for $199.20 each
Quantity: