• Introduction to Excel
• Excel Formula Syntax
• Writing Efficient Formulas with Fixed & Relative References
• Common Excel Error Types
• Formula Auditing: Trace Precedents & Dependents
• Formula Auditing: Evaluate Formula & Error Checking (PC Only)
• Navigating Excel Worksheets with Ctrl Shortcuts
• Saving Time with Function Shortcuts
• Creating Drop-Down Menus with Data Validation

Conditional Statements and Logical Operators

• Anatomy of the IF Statement
• Nesting Multiple IF Statements
• Adding Conditional AND/OR Operators
• Using NOT & “<>” Conditionals
• Fixing Errors with IFERROR
• Excel Logical Operators

Common Excel Statistical Function

• Basic Excel Statistical Functions
• Extracting Values with SMALL/LARGE & RANK/PERCENTRANK
• Randomization with RAND () & RANDBETWEEN
• Row-Level Calculation with SUMPRODUCT
• Conditional Aggregation with COUNTIFS, SUMIFS & AVERAGEIFS
• Building a Basic Dashboard with COUNTIFS & SUMIFS
• Counting Duplicates with COUNTIF & SUMPRODUCT
• Data Profiling with COUNT & SUMPRODUCT

Lookup & Reference Function

• Working with Named Ranges
• Counting Rows & Columns with ROW/ROWS & COLUMN/COLUMNS
• Introduction to VLOOKUP/HLOOKUP
• Joining Data with VLOOKUP
• Fixing Errors with IFERROR & VLOOKUP
• VLOOKUP Reference Array Options
• Approximate Match Lookups
• Navigating Cell Ranges with INDEX
• Matching Text & Values with MATCH
• Combining INDEX & MATCH to Dynamically Search Ranges
• Combining MATCH & VLOOKUP for More Flexible Lookups
• Selecting List Items with CHOOSE
• Defining Ranges with OFFSET
• Combining OFFSET with COUNTA to Create a Flexible Range
• PRO TIP: Using OFFSET to Create Interactive Charts

Text Function

• Capitalization with UPPER, LOWER, PROPER & TRIM
• Combining Text with CONCATENATE (&)
• Extracting Strings with LEFT, MID, RIGHT & LEN
• Converting Text to Values with TEXT & VALUE
• Searching Text String with SEARCH & FIND
• Categorizing Data with IF(ISNUMBER(SEARCH))
• Combining RIGHT, LEN, and SEARCH
• Replacing Text with SUBSTITUTE

Date & Time Functions

• Understanding Excel Date Syntax with DATEVALUE
• Formatting Dates & Filling Date Series
• Creating Real-Time Functions with TODAY & NOW
• Extracting Time Periods with YEAR, MONTH, DAY, HOUR, MINUTE & SECOND
• Calculating the Month Start or End with EOMONTH
• Calculating % of Year with YEARFRAC
• Defining Time Periods with WEEKDAY, WORKDAY & NETWORKDAYS
• Calculating Differences Between Dates with DATEDIF (Legacy Function)
• Excel Date & Time Functions

Formula Based Formatting

• Creating, Editing & Managing Formula-Based Rules
• Highlighting Rows Using the MOD Function
• Formatting Based on the Value of Another Cell
• Formatting Cells Using Stats Functions
• Formatting Cells Using Text Functions & Logical Operators
• Excel Formula-Based Formatting

Dynamic Array Formulas

• The SORT Function
• The SORTBY Function
• The FILTER Function
• The UNIQUE Function
• Drop-Down Lists with SORT & UNIQUE
• Combining SORT, FILTER & UNIQUE
• Referencing Spill Ranges in Calculations
• Conditional Formatting with Dynamic Arrays
• PRO TIP: Creating Charts from Dynamic Arrays
• The SEQUENCE Function
• Top N Calculations with SEQUENCE & LARGE
• The RANDARRAY Function
• The FREQUENCY Function
• The TRANSPOSE Function

Understanding on Pivot Table

• Why Pivot Tables?
• Structuring Source Data for Analysis in Excel
• Creating Your First Pivot Table
• IMPORTANT: Add to Data Model
• Navigating the Pivot Table Field List
• Exploring Pivot Table Analyze& Design Options
• Selecting, Clearing, Moving & Copying Pivot Tables
• Refreshing & Updating Pivot Tables

Sorting , Filtering & Grouping Data with Pivot Tables

• Sorting Data with Pivot Tables
• PRO TIP: Fixing Incorrect Alphabetical Sorting
• Filtering Data with Pivot Table Label & Selection Filters
• PRO TIP: Pivot Table Label Filters with Wildcards
• Filtering Data with Pivot Table Value Filters
• PRO TIP: Enabling Multiple Pivot Table Filters
• Grouping & Segmenting Data with Pivot Tables
• PRO TIP: Enabling & Disabling Automatic Date Grouping
• Filtering Data with Pivot Table Slicers & Timelines
• Usage of Calculated Field
• Breaking Out Pivot Table Report Filter Pages

Final Bonus Lecture: Top 10 Tips on excel you might have not Known