English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 114 lectures (9h 41m) | 3.48 GB
Master 75+ MS Excel formulas and learn data analysis with a top Microsoft Excel & business intelligence instructor
It’s time to show Excel who’s boss. Whether you’re starting from square one or aspiring to become an absolute Excel power user, you’ve come to the right place.
This course will give you a deep understanding of the advanced Excel formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool.
While most Excel courses focus on simply what each formula does, I teach through hands-on, contextual examples designed to showcase why these formulas are awesome and how they can be applied in a number of ways. I will not train you to regurgitate functions and formula syntax; I will teach you how to THINK like Excel.
By the end of the course you’ll be writing robust, elegant formulas from scratch, allowing you to:
- Easily build dynamic tools & Excel dashboards to filter, display and analyze your data
- Create your own formula-based Excel formatting rules
- Join datasets from multiple sources with XLOOKUP, INDEX & MATCH functions
- Manipulate dates, times, text, and arrays
- Automate tedious and time-consuming tasks using cell formulas and functions in Excel (no VBA required!)
- Pull real-time data from APIs directly into Excel (weather, stock quotes, directions, etc.)
We’ll dive into a broad range of Excel formulas & functions, including:
- Lookup/Reference functions
- Statistical functions
- Formula-based formatting
- Date & Time functions
- Logical operators
- Dynamic Array formulas
- Text functions
- INDIRECT & HYPERLINK
- Web scraping with WEBSERVICE & FILTERXML
I have a genuine passion for Excel that most people reserve for things like kittens, ice cream, and significant others. The only thing I love more than learning Excel is teaching it, and as the founder of Maven Analytics I’ve been lucky enough to teach Excel to 500,000+ students across 180+ countries. My teaching style is conversational, authentic and to the point, and I will always communicate complex concepts in a framework that is clear and easy to comprehend.
As a full-time analytics consultant and Excel instructor, I cut my teeth using Excel to solve real-world business problems and develop award-winning analytics & data visualization tools for Fortune 500 companies. If you care about creds, I’m a card-carrying MOS Certified Excel Expert and my work has been featured by Microsoft and the New York Times. Ok so I don’t actually carry the card, but you get the idea.
If you’re looking for the ONE course with all of the advanced Excel formulas and functions that you need to know to become an absolute Excel ninja, you’ve found it.
What you’ll learn
- Learn how to THINK like Excel, and write powerful and dynamic Excel formulas from scratch
- Automate, streamline, and completely revolutionize your workflow with Excel
- Master unique tips & techniques that you won’t find in ANY other course, guaranteed
- Explore fun, interactive, and highly effective demos from a best-selling Excel instructor
- Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
- Write advanced conditional, text, date and lookup functions, including XLOOKUP & Dynamic Arrays
Table of Contents
Getting Started
1 Course Structure & Outline
2 READ ME Important Notes for New Students
3 DOWNLOAD Course Resources
4 Setting Expectations
Excel Formulas 101
5 Introduction Excel Formulas 101
6 Excel Formula Syntax
7 Writing Efficient Formulas with Fixed & Relative References
8 Common Excel Error Types
9 Formula Auditing Trace Precedents & Dependents
10 Formula Auditing Evaluate Formula & Error Checking (PC Only)
11 Navigating Excel Worksheets with Ctrl Shortcuts
12 Saving Time with Function Shortcuts
13 Accessing Tools with Alt Key Tips
14 BONUS Mac Shortcuts
15 PRO TIP Creating Drop-Down Menus with Data Validation
16 Congrats, You’re 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 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 Common 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 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 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 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 Formula-Based Formatting
Dynamic Array Formulas
85 Intro & Compatibility
86 Project Intro Maven Recruiters
87 Legacy vs. Dynamic Excel
88 Spill Range Properties
89 PRO TIP Growing Source Data
90 Common Dynamic Array Functions
91 The SORT Function
92 The SORTBY Function
93 The FILTER Function
94 The UNIQUE Function
95 PRO TIP Drop-Down Lists with SORT & UNIQUE
96 Combining SORT, FILTER & UNIQUE
97 Referencing Spill Ranges in Calculations
98 Conditional Formatting with Dynamic Arrays
99 PRO TIP Creating Charts from Dynamic Arrays
100 The SEQUENCE Function
101 PRO TIP Top N Calculations with SEQUENCE & LARGE
102 The RANDARRAY Function
103 PRO TIP Creating Random Top N Lists
104 The FREQUENCY Function
105 The TRANSPOSE Function
106 PRO TIP Joining Dynamic Arrays with CHOOSE
107 PRO TIP Declaring Variables with LET
108 HOMEWORK Dynamic Array Formulas
Bonus Functions
109 Introduction
110 Creating Custom References with INDIRECT
111 Linking to Worksheet Locations with HYPERLINK
112 Real-Time Weather Conditions with WEBSERVICE & FILTERXML (Part 1)
113 Real-Time Weather Conditions with WEBSERVICE & FILTERXML (Part 2)
Wrapping Up
114 BONUS LECTURE More from Maven
Resolve the captcha to access the links!