Microsoft Excel – Advanced Excel Formulas & Functions

Getting Started
1 Course Structure Outline
2 READ ME Important Notes for New Students
3 DOWNLOAD Course Resources
4 Setting Expectations
5 Looking to become an Excel MASTER

Excel Formulas 101
6 Introduction Excel Formulas 101
7 Excel Formula Syntax
8 Writing Efficient Formulas with Fixed Relative References
9 Common Excel Error Types
10 Formula Auditing Trace Precedents Dependents
11 Formula Auditing Evaluate Formula Error Checking (PC Only)
12 Navigating Excel Worksheets with Ctrl Shortcuts
13 Saving Time with Function Shortcuts
14 Accessing Tools with Alt Key Tips
15 PRO TIP Creating Drop-Down Menus with Data Validation
16 Congrats Youre a Developer
17 HOMEWORK Excel Formulas 101

Conditional Statements Logical Operators
18 Introduction
19 Anatomy of the IF Statement
20 Nesting Multiple IF Statements
21 Adding Conditional ANDOR Operators
22 Using NOT Conditionals
23 Fixing Errors with IFERROR
24 Common IS Statements
25 HOMEWORK Excel Logical Operators

Common Excel Statistical Functions
26 Introduction
27 Basic Excel Statistical Functions
28 Extracting Values with SMALLLARGE RANKPERCENTRANK
29 Randomization with RAND() RANDBETWEEN
30 Row-Level Calculation with SUMPRODUCT
31 DEMO Calculating Shipping Costs with SUMPRODUCT
32 Conditional Aggregation with COUNTIFS SUMIFS AVERAGEIFS
33 DEMO Building a Basic Dashboard with COUNTIFS SUMIFS
34 DEMO Counting Duplicates with COUNTIF SUMPRODUCT
35 PRO TIP Data Profiling with COUNT SUMPRODUCT
36 HOMEWORK Excel Stats Functions

Lookup Reference Functions
37 Introduction
38 Working with Named Ranges
39 Counting Rows Columns with ROWROWS COLUMNCOLUMNS
40 Introduction to VLOOKUPHLOOKUP
41 Joining Data with VLOOKUP
42 Fixing Errors with IFERROR VLOOKUP
43 VLOOKUP Reference Array Options
44 Approximate Match Lookups
45 Navigating Cell Ranges with INDEX
46 Matching Text Values with MATCH
47 Combining INDEX MATCH to Dynamically Search Ranges
48 Combining MATCH VLOOKUP for More Flexible Lookups
49 TROUBLESHOOTING VLOOKUP with Duplicate Keys
50 SNEAK PEEK XLOOKUP (Office 365 Only)
51 DEMO Building Dashboards with XLOOKUP (Office 365 Only)
52 Selecting List Items with CHOOSE
53 Defining Ranges with OFFSET
54 Combining OFFSET with COUNTA to Create a Flexible Range
55 PRO TIP Using OFFSET to Create Interactive Charts
56 HOMEWORK Excel Lookup Reference Functions

Text Functions
57 Introduction
58 Capitalization with UPPER LOWER PROPER TRIM
59 Combining Text with CONCATENATE ()
60 Extracting Strings with LEFT MID RIGHT LEN
61 Converting Text to Values with TEXT VALUE
62 Searching Text String with SEARCH FIND
63 Categorizing Data with IF(ISNUMBER(SEARCH))
64 Combining RIGHT LEN and SEARCH
65 Replacing Text with SUBSTITUTE
66 HOMEWORK Excel Text Functions

Date Time Functions
67 Introduction
68 Understanding Excel Date Syntax with DATEVALUE
69 Formatting Dates Filling Date Series
70 Creating Real-Time Functions with TODAY NOW
71 Extracting Time Periods with YEAR MONTH DAY HOUR MINUTE SECOND
72 Calculating the Month Start or End with EOMONTH
73 Calculating of Year with YEARFRAC
74 Defining Time Periods with WEEKDAY WORKDAY NETWORKDAYS
75 Calculating Differences Between Dates with DATEDIF (Legacy Function)
76 DEMO Building a Simple Budget Pacing Tool
77 HOMEWORK Excel Date Time Functions

Formula-Based Formatting
78 Introduction
79 Creating Editing Managing Formula-Based Rules
80 Highlighting Rows Using the MOD Function
81 Formatting Based on the Value of Another Cell
82 Formatting Cells Using Stats Functions
83 Formatting Cells Using Text Functions Logical Operators
84 HOMEWORK Excel Formula-Based Formatting

Array Formulas
85 Introduction
86 READ ME Array Formulas are Changing
87 Rules of Excel Array Functions
88 Pros Cons of Array Functions
89 Defining Vertical Horizontal and 2-Dimensional Arrays
90 Using Array Constants in Formulas
91 Defining Referencing Named Array Constants
92 Restructuring Data with TRANSPOSE
93 Linking Data Between Sheets (Array vs. Non-Array Comparison)
94 Returning the Largest Values in a Range
95 Counting Characters Across Cells
96 Creating a MAX IF Array Formula
97 Creating a MAX IF Array Formula with Multiple Criteria
98 Converting Boolean Values Using the Double Unary (–)
99 HOMEWORK Excel Array Functions

Extra Bonus Functions
100 Introduction
101 Creating Custom References with INDIRECT
102 Linking to Worksheet Locations with HYPERLINK
103 Real-Time Weather Conditions with WEBSERVICE FILTERXML (Part 1)
104 Real-Time Weather Conditions with WEBSERVICE FILTERXML (Part 2)

Wrapping Up
105 Conclusion Next Steps
106 More from Maven Analytics