# Excel University Topics

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

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
• 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
• Functions
• 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 Topics

The Masters curriculum teaches technical Excel skills, and is broken down into two courses.

• 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

## Courses - CPE Credit

Excel University is a NASBA-approved CPE sponsor, and as such, our courses are offered for CPE (Continuing Professional Education) credit for CPAs and other professionals who require it.

The Undergraduate courses earn a total of 55 CPE hours of QAS self-study continuing professional education credit. CPE is earned at the end of each course, as follows:

• Undergrad 1 - 9 CPE
• Undergrad 2 - 11 CPE
• Undergrad 3 - 15 CPE
• Undergrad 4 - 20 CPE

Note: in prior years we offered an accelerated undergraduate program that combined all four undergrad courses and it provided 40 CPE credits as follows:

• 6 CPE hours when you pass the Freshman exam
• 12 CPE hours when you pass the Sophomore exam
• 10 CPE hours when you pass the Junior exam
• 12 CPE hours when you pass the Senior exam

The Masters courses earn a total of 46 CPE credits, as follows:

• Masters 1 - 19 CPE
• Masters 2 - 27 CPE (16 for part 1; 11 for part 2)

CPE is available in our Courses. If you do not require CPE credit, consider our Campus Pass option.