Autoplay
Autocomplete
Previous Lesson
Complete and Continue
TAS BootCamp - Excel to PowerPoint (July 2019)
Downloadable Files
eBook - Become an Excel Ninja.
Introduction
A Basic Overview of Essential Excel Tricks (9:12)
One of Excel's Best Tricks - Flash Fill - Basic to Intermediate (6:27)
Project Work: Electricity Consumption Log Book (10:28)
Introduction - Quiz
001 Ninja Shortcuts
What is faster than the regular Copy and Paste shortcuts? (3:23)
What is the shortcut to automatically fill selected cells? (1:16)
6 Essential [data selection and navigating] shortcuts (4:34)
6 Essential [rows and column] shortcuts (3:07)
7 Essential [formatting] shortcuts (4:39)
Ninja Shortcuts - Quiz
002 Formatting Tricks
Learn 2 fastest tricks to copy-paste cell formats 3-5x times faster (1:56)
How to create a basic drop down list? (2:37)
Four tricks to color cells with Errors, Specific Values, Duplicates and Blank (5:03)
Formatting Tricks - Quiz
003 Formula Pre-Requisite
What is "locking" the cell reference - Basics of using $ (3:02)
Learn the two advanced cases of using $ while locking the cell reference (3:20)
Formula Pre-Requisite - Quiz
004 Sort & Filter
How to analyse the data using Shortcuts after applying Filter? (2:52)
Why you should never apply SUM or AVERAGE function on a Filtered list? (5:06)
Sort & Filter - Quiz
005 Data Cleaning
Learn one of the best Data Cleaning Tricks - Go To (5:01)
Learn to use Find & Replace effectively (4:50)
How to keep the zeroes when applying Text to Column on data with preceeding zeroes? (4:06)
How to rectify incorrectly formatted date such as 24.05.2007 using Text to Column? (3:24)
How to rectify incorrectly formatted date such as 20080109 using Text to Column? (3:18)
How is MID more powerful than LEFT and RIGHT functions? (3:42)
Data Cleaning - Quiz
006 Working with Dates
Do you know how Excel stores a Date value to avoid MDY vs DMY confusion? (3:16)
How to get the day name of a date - Sun, Mon etc. (3:06)
4 Tricks of Current Date and Time for Excel Modeling (1:29)
How to calculate 5th of next month? (3:41)
How to calculate expiry date for agreements and warranty period? (3:55)
Working with Dates - Quiz
007 Pivot Table
Top 7 Pivot Table Tricks (11:20)
Step 1 - How to create a Pivot Table? (2:40)
Step 2 - Understanding the 4 grids of a Pivot Table report (3:21)
Step 3 - How to calculate SUM, AVERAGE and COUNT in a Pivot Table (5:06)
Step 4 - How to calculate PERCENTAGE in a Pivot Table (2:22)
Step 5 - What are the different PERCENTAGE calculations types in a Pivot Table? (4:50)
Step 6 - Why is "Grouping" numbers important for analysis? (3:38)
Step 7 - Why is "Grouping" dates important for analysis? (4:09)
Step 8 - Why is "Grouping" text important for analysis? (3:36)
Step 9 - What are the 2 things to do if your Pivot Table's source data changes? (6:19)
Step 10 - How to activate "Auto-Refresh" Pivot Table setting? (0:41)
Step 11 - How to add Sparkline and Pivot charts to visualise your data? (2:13)
Step 12 - How to drill into the details of Pivot Table data? (0:59)
Step 13 - How to create 100s of Pivot Table reports in just 3 clicks? (3:39)
Step 14 - What is Pivot Table Slicer and how is it different from Report Filter? (2:33)
Pivot Table Project for Practice - Sales Data Analysis - Part 1 of 2 (7:57)
Pivot Table Project for Practice - Sales Data Analysis - Part 2 of 2 (3:09)
Pivot Table Project for Practice - Inventory Analysis - Part 1 of 1 (7:01)
Pivot Table - Quiz
008 Lookup & Conditional Calculations
Learn the basics the most important formula of Excel - VLOOKUP (7:59)
What is the difference between VLOOKUP and HLOOKUP? (2:45)
Use Slabs based Lookup to avoid complex Nested IF statements (6:41)
Learn the basics of VLOOKUP's best friend - MATCH function (3:38)
What is 2D Lookup and how can it avoid complex Nested IF statements? (5:54)
Lookup & Conditional Calculations - Quiz 01
009 Lookup & Conditional Calculations
What is "reverse" lookup, and why VLOOKUP fails you sometimes? (3:27)
How to write a "reverse" lookup using INDEX with MATCH? (8:12)
"Reverse" Lookup Project for Practice - using INDEX with MATCH (6:07)
Lookup & Conditional Calculations - Quiz 02
010 Lookup & Conditional Calculations
How is VLOOKUP different from SUMIFS? (4:28)
How can you add multiple criteria while writing SUMIFS? (4:12)
Three most important tricks of writing COUNTIFS function (6:10)
Lookup & Conditional Calculations - Quiz 03
011 Logical Formula
The two must under-used logical formulas - AND & OR (5:15)
How to combine IF with AND & OR formulas to make the logic 5x times powerful? (3:35)
Logical Formula - Quiz
What are the Top 7 error and data validity checking formulas? (4:18)
How to write IF formulas - Basic to Intermediate level? (5:19)
Logical Formula - Quiz
012 What-If Analysis
How to add Spin buttons to make your financial model interactive? (5:06)
How to do Sensitivity Analysis of Loan EMI using Data Tables? (5:20)
Part 1: How to do use Data Tables with more than one output variable? (Hint: INDIRECT) (2:38)
Part 2: How to do use Data Tables with more than one output variable? (Hint: INDIRECT) (10:06)
What-If Analysis - Quiz
013 Macros
Excel Macros for Non-Programmers (11:12)
014 Excel Course Wrap Up
Post Training Recap for Excel (15:49)
PowerPoint - Speed Shortcuts and Tricks
Advanced PowerPoint Tricks - Best 5 Tricks (7:25)
Rotate Shortcut (4:03)
Converting Bullet Point Text in SmartArt (2:40)
De Assembling SmartArt (3:30)
Slide Master - Introduction (4:47)
PowerPoint - Speed Shortcuts and Tricks - Quiz
Word - Speed and Formatting
0203 How to create Multi-level numbered bullet points (2:59)
0304 Add dynamic page numbering - Page X of Y (2:00)
0406 Shortcut to Split a table into two separate tables (2:25)
0411 Cell Shading with Pattern (1:20)
0606 Shortcuts to clear text and paragraph formatting (3:39)
0613 Shortcut to Copy and Paste Format (1:19)
0618 Shortcut to Change font size (0:32)
0619 Alignment and Text Direction tricks (3:50)
0620 Why should you use Page Break (3:22)
0634 Use Portrait and Landscape layout in the same document (2:40)
0903 Create Table of Contents (4:58)
1001 Essential Print Settings (2:31)
1002 Shortcut to Print Preview (1:21)
1201 Why should you use Track Changes (2:18)
1202 How to turn the Track Changes ON OFF (1:35)
1207 Accept and Reject changes of multiple reviewers fast (3:33)
1601 Compare two versions of the same document - Why and How (4:17)
1602 Accept Reject changes using right click (1:00)
Course Feedback & Final Assessment
Course Feedback
Final Excel Assessment Quiz
Final PowerPoint Project
Use Slabs based Lookup to avoid complex Nested IF statements
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock