Join SuperDataScience
Start Your Free Trial Today >>It's time to show Excel who's boss. Whether you're starting from square one or aspiring to become an absolute Excel badass, you've come to the right place.
This course will give you a deep understanding of the advanced 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 and functions from scratch, allowing you to:
Easily build dynamic tools & Excel dashboards to filter, display and analyze your data
Go rogue and design your own formula-based formatting rules
Join datasets from multiple sources in seconds with lookup, index & match functions
Pull real-time data from APIs directly into Excel (weather, stock quotes, directions, etc.)
Manipulate dates, times, text, and arrays
Automate tedious and time-consuming analytics tasks using cell formulas and functions in Excel (no VBA required!)
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
Array formulas
Text functions
Powerful analytics tools and formulas like INDIRECT, HYPERLINK, WEBSERVICE & FILTERXML
$35
/ MonthAccelerate your Career and boost your Earning Potential with our Expert Instructors & Community! What Youāll Unlock: - š 40+ Courses (over 200 hours!) - š 17 Specialized Career Paths - š Quizzes and Practice Activities - š Certificates for Courses & Career Paths - š Prizes for Learning - š§Ŗ Weekly Live Labs (plus recordings!) - šÆ Monthly Missions to practice even more - š¼ Monthly Career Booster Events - š¬ Full access to the SDS Community - ā” Monthly Speed Networking - š„ Monthly Resume Clinics - š„ Group Mentorship Program "In just a few months of learning at SDS, I landed a Data Analyst job!" ā Sanaz Afshar, California
$157
/ MonthAccelerate your Career and boost your Earning Potential with our Expert Instructors & Community! What Youāll Unlock: - š 40+ Courses (over 200 hours!) - š 17 Specialized Career Paths - š Quizzes and Practice Activities - š Certificates for Courses & Career Paths - š Prizes for Learning - š§Ŗ Weekly Live Labs (plus recordings!) - šÆ Monthly Missions to practice even more - š¼ Monthly Career Booster Events - š¬ Full access to the SDS Community - ā” Monthly Speed Networking - š„ Monthly Resume Clinics - š„ Group Mentorship Program Plus, Pro Plan perks just for you: - š A Personalized Career Path built around your goals - š§āš« 1-on-1 Mentoring Sessions every month - āļø Personalized Resume Reviews "In just a few months of learning at SDS, I landed a Data Analyst job!" ā Sanaz Afshar, California
Course content
Introduction
01:06
The Formula Library & Auditing Tools
04:37
Demo: Using the Evaluate Formula Tool
01:25
Basic Formula Syntax
03:31
Fixed, Relative, & Mixed References
04:58
Common Errors & the IFERROR statement
07:25
Function, CTRL & ALT Shortcuts
06:36
Bonus: Shortcuts for Mac Users
02:09
Creating Custom Data Validation Rules
03:29
Fixed vs. Volatile Functions
03:22
Homework: Formulas 101
00:47
Introduction
01:23
Working with Named Arrays
03:30
ROW/ROWS & COLUMN/COLUMNS
03:19
VLOOKUP/HLOOKUP
05:54
Joining Data with VLOOKUP
06:24
Fixing Errors with IFERROR & VLOOKUP
04:30
VLOOKUP Reference Array Options
06:19
The INDEX Function
01:59
The MATCH Function
02:33
Using INDEX & MATCH Together
06:09
Combining MATCH with VLOOKUP
04:48
Update: VLOOKUP Correction
05:06
The OFFSET Function
02:06
Combining OFFSET with COUNTA To Return a Variable List
02:59
Project Showcase: Using OFFSET To Create a Dynamic Scrolling Chart
09:51
Homework: Lookup/Reference Functions
00:55
Introduction
02:14
DATEVALUE: Your New BFF
05:06
Date Formatting & Fill Series
03:40
TODAY()/NOW()
02:16
YEAR/MONTH/DAY & HOUR/MINUTE/SECOND
03:05
The EOMONTH Function
06:17
The YEARFRAC Function
02:27
WEEKDAY, WORKDAY & NETWORKDAYS
07:24
The DATEDIF Function
04:24
Project Showcase: Designing a Sample Budget Pacing Tool
11:08
Homework: Date & Time Functions
00:36
Introduction
01:31
Creating / Editing Formatting Rules
04:39
Demo: Highlighting Every Other Row With the MOD() Function
03:50
Demo: Formatting Cells Based On the Value of Another Cell
03:30
Demo: Formatting Rows Using Statistical Functions
05:28
Demo: Formatting Rows Using Text Functions
05:08
Homework: Formula-Based Formatting
00:35
Introduction
01:27
Rules of Array Functions
03:25
Pros & Cons of Array Functions
02:16
Vertical, Horizontal, and 2-Dimensional Array Constants
06:26
Using Array Constants in Formulas
03:39
Named Array Constants
04:31
The Transpose Function
04:22
Linking Data Between Sheets: Array vs. Non-Array Comparison
02:36
Returning the "X" Largest Values in a Range
03:09
Counting Characters Across Cells
01:52
Creating a "MAX IF" Array Formula
03:10
Bonus: "MAX IF" with Multiple Criteria
06:55
Bonus: Using the Double Unary Operator ("--")
03:50
Homework: Array Functions
02:10