Study Pass (Group)

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. Includes one year of access. Does not auto-renew.

You will be able to assign the subscription licenses to students through the website. Once a license is assigned to a specific student, it can't be switched to another student until the next renewal. That is, the system doesn't enable you to switch licenses each week or month to various students within your organization ... the licenses are assigned one year at a time.

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.

 

 

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

 

Price: $290.00
Quantity:

Return to Catalog

Categories