Study Pass (Monthly)

Study Pass

Our Study Pass is a self-service product that includes the training videos and files from our Undergraduate (1-4) and Masters (1-2) lessons. Stop the monthly subscription anytime with the Manage Subscription link.

Note: a Study Pass is a self-service program which does not include support, forums, live Office Hours, quizzes, certificatates of completion, and more. If you'd like these elements, please consider a Campus Pass or CPE Pass instead. Compare our Training Passes to see which items are included in each pass.

Have a team? Check out our Group Study Pass.

 

Undergrad Lessons

The Graduate Program teaches classic Excel topics and includes the lessons from the four undergraduate courses (v1-4).

  • Freshman covers the foundations needed to build reliable workbooks.
  • Sophomore demonstrates how to build efficient formula-based reports.
  • Junior digs into the details of PivotTables.
  • Senior illustrates how to create efficient internal-use workpapers.

 

Freshman (Undergrad 1)

Foundations
  • Selected Shortcuts
  • Named References
  • Tables
  • Data Validation
  • Conditional Formatting Basics
  • Better Summing
  • Skinny Row
  • Hide Worksheets
  • Highlight Input Cells
Workbook Organization
  • Workbook Organization
  • Worksheet Organization
  • Nested Functions
  • Selection Groups
  • Workbook Design Principles

Sophomore (Undergrad 2)

Conditional Summing
  • SUMIFS
  • Remove Duplicates
Lookups
  • VLOOKUP, INDEX, MATCH
  • IFERROR, IF, ISERROR
  • List Comparisons COUNTIFS
  • Indentation
  • Lookup with SUMIFS
Date and Text Functions
  • EOMONTH, MONTH, YEAR, DATE
  • Concatenation Basics
Formula-based Reports
  • SUMIFS with CONCATENATE
  • Dynamic Headers
  • Mapping Tables

Junior (Undergrad 3)

PivotTable Fundamentals
  • PivotTable Basics
  • Row Fields
  • Value Fields
  • Column Fields
  • Filter Fields
  • Updating Data
  • Groups and Subtotals
  • Value Formats
  • Report Type Comparison
Working with PivotTables
  • Options
  • Report Layout and Design
  • Multiple Value Fields
  • Calculated Fields
  • Calculated Items
  • Show Values As
  • Sorting
  • Filtering
  • GETPIVOTDATA
Obtain > Prepare > Summarize
  • Text Files
  • Data Preparation
  • Accounting System Data
  • Getting Graphic

Senior (Undergrad 4)

Features
  • Sorting & Filtering
  • Outlining
  • Formatting
  • Printing
  • Protecting
  • Hyperlink Feature
Functions
  • HYPERLINK Function
  • IF Alternatives
  • Lookup Functions
  • Text Functions
  • Date Functions
  • Insert Function
Techniques
  • Ribbon Review
  • Conditional Formatting Revisited
  • Names Revisited
  • Wildcards Revisited
  • Tables Revisited
  • Start Here Revisited
Applications
  • JE Preparation
  • Budget Ideas
  • Common Workpapers

 

Masters Lessons

The Masters Program teaches more technical Excel topics and includes the lessons from the two masters courses (m1-2).

  • Masters 1 covers Power Query, Power Pivot, Power BI, and table/graph design.
  • Masters 2 demonstrates how to automate repetitive tasks with VBA and Macros.

 

Masters 1

Power Query Topics
  • Get external data
  • Various transformations, including unpivot
  • Load to data model and table
  • Chain queries
  • Merge queries
Power Pivot Topics
  • Relationships
  • Measures (implicit / explicit)
  • Various DAX functions
  • Date (calendar) table
  • Data vs lookup tables
Power BI Topics
  • Getting external data
  • Modeling data and writing measures
  • Various visuals (matrix, table, column, bar, line, scatter, map)
  • Power BI desktop, service, mobile app
  • Slicers (and sync slicers)
Table and Graph Design Topics
  • Display type: selecting a table or graph
  • Chart design practices
  • How to feature key relationships graphically
  • Nominal (order not important)
  • Ranking (order matters)
  • Part-to-whole (contributions to whole)
  • Time series (over time)
  • Distribution (distributed over range)
  • Deviation (difference)
  • Correlation (vary in relation to each other)
  • Geospatial (locations)
Case Studies (practical applications of skills)
  • Sales manager (nominal)
  • Travel expenses for CFO (ranking, part-to-whole)
  • Year-end sales promotion (time series)
  • Budget vs actual (deviation)
  • We Collect 4U (distribution)
  • Employee surveys (correlation)
  • Donor state map (geospatial)
  • Dashboard

Masters 2

VBA Topics
  • Visual Basic Editor
  • Macro Recorder
  • Excel object model
  • Properties
  • Methods
  • Procedures, subs, functions
  • Collections
  • Variables, scope, type
  • Constants
  • Object variables
  • VBA functions
  • MsgBox
  • InputBox
  • Program flow
  • If Then
  • Exit, Labels
  • Select Case
  • Program flow, If Then, Exit, Labels, Select Case
  • Loops, For Next, Do While
  • Collection loops
  • Error handling, On Error, Debugging, Watch/Locals
  • Copy cell, range, table
  • Charts, chart sheets
  • Performance
  • ScreenUpdating
  • Module variables
  • Workbook, worksheet loops
  • Names, Variant/Array, Split
  • StatusBar
Case Studies (practical application of skills)
  • Insert standard worksheet title
  • Insert the SUBTOTAL function
  • Prepare a workbook to deliver to staff
  • Apply standard PivotTable design
  • Apply standard chart design
  • Export journal entries
  • Update a set of budget workbooks
  • Create PDF reports from workbooks

 

Subscription: $29.00 per period

Return to Catalog

Categories