Excel University Topics

The following topics are taught in the Undergraduate and Masters lessons. These topics are the same whether you take the lessons through a Course, Campus Pass, or CPE Pass.

Undergraduate Topics

The Undergraduate lessons teach classic Excel features and functions as follows:

  • Undergrad 1 (Freshman) covers the foundations needed to build reliable, recurring-use workbooks.
  • Undergrad 2 (Sophomore) demonstrates how to build efficient formula-based reports.
  • Undergrad 3 (Junior) digs into the details of PivotTables.
  • Undergrad 4 (Senior) illustrates how to create efficient internal-use workpapers.

Undergrad 1 - Freshman Year

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

Undergrad 2 - Sophomore Year

  • Conditional Summing
    • Conditional Summing Basics with SUMIFS
    • Remove Duplicates
  • Lookups
    • Lookup Basics VLOOKUP
    • Improving VLOOKUP with MATCH
    • Improving VLOOKUP with VALUE and TEXT
    • Moving Beyond VLOOKUP with INDEX
    • Trap Errors with IFERROR
    • The IF Function
    • List Comparisons
    • The ISERROR Function
    • Multicolumn List Comparisons with COUNTIFS
    • Indentation
    • Perform Lookups with SUMIFS
  • Date and Text Functions
    • Determine the Last Day of the Month with EOMONTH
    • Date Parts – MONTH and YEAR
    • Build Your Own Date with DATE
    • Concatenation Basics
  • Formula-based Reports
    • Improve SUMIFS with CONCATENATE
    • Dynamic Headers
    • Horizontal Reports
    • Mapping Tables
    • Data Validation and Reporting
    • Improving Error Check with Boolean Values and the Logical AND Function
    • Concepts Applied

Undergrad 3 - Junior Year

  • 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
    • Hybrid Reports with GETPIVOTDATA
  • Obtain > Prepare > Summarize
    • Web Data
    • External Data
    • Text Files
    • Data Preparation
    • Accounting System Data
    • Getting Graphic

Undergrad 4 - Senior Year

  • 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 Program

Masters Topics

The Masters curriculum teaches automation skills, and is broken down into two parts.

  • Masters 1 covers Power Query, Power Pivot, Power BI, and table and graph design practices.
  • Masters 2 demonstrates how to build macros with VBA (Visual Basic for Applications) to automate repetitive, routine tasks.

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, constants, scope, type, object variables
    • VBA functions, MsgBox, InputBox
    • Program flow, If Then, Exit, Labels, Select Case
    • Loops, collection loops, For Next, Do While
    • Error handling, On Error, Debugging, Watch/Locals
    • Copy cell, range, table
    • Charts, chart sheets
    • Performance, ScreenUpdating
    • Module variables
    • Workbook, worksheet loops
    • Names, StatusBar, variant/array, Split
  • 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