Autoplay
Autocomplete
Previous Lesson
Complete and Continue
EY - Excel Bootcamp
Downloadable Files
eBook - Become an Excel Ninja.
Introduction
Introduction to Excel (8:50)
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)
9 Essential [formula writing & auditing] shortcuts (6:18)
7 Essential [formatting] shortcuts (4:39)
Ninja Shortcuts - Quiz
002 Business Math Calculations
Learn the basics of ROUND(), ROUNDUP() and ROUNDDOWN() (3:43)
Learn the advanced level of ROUND(), ROUNDUP() and ROUNDDOWN() (6:38)
Learn the 4 most important COUNT formula types (3:20)
Why is SUMPRODUCT so important for calculating weighted average? (3:33)
How do you perform compounding and discounting calculations in Excel? (2:39)
Business Math Calculations - Quiz
003 Formatting Tricks
Learn 3 hidden "Custom Format" tricks for formatting the data (5:18)
Learn 2 fastest tricks to copy-paste cell formats 3-5x times faster (1:56)
What is AutoFill and how to activate it? (1:19)
5 tricks of AutoFill options that will save you time in data entry (2:47)
What is the difference between Paste Special Transpose & TRANSPOSE() formula? (4:17)
Formatting Tricks - Quiz
004 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
005 Data Cleaning - Part 1
Learn one of the best Data Cleaning Tricks - Go To (5:01)
How to color the visible cells in a filtered list? (2:24)
Learn to use Find & Replace effectively (4:50)
Data Cleaning - Part 1 - Quiz
006 Sort & Filter
Learn the basics of Sorting a list (2:23)
Learn the basics of Filtering a data (3:37)
Avoid this #1 common mistake while applying Filter (1:25)
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)
How to apply Color Filter and Custom Filter? (1:13)
Sort & Filter - Quiz
007 Working with Dates
Do you know how Excel stores a Date value to avoid MDY vs DMY confusion? (3:16)
How to change Control Panel settings to change acceptable format for date input? (4:06)
4 "must-know" date formulas for every Excel user - YEAR, MONTH, DAY, DATE (2:15)
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
008 Data Input
How to create a basic drop down list? (2:37)
How to allow the source of drop down list to include future additions? (3:51)
Data Input - Quiz
009 Organising Data
How to make your Excel sheet look like a white page? (0:49)
How to activate two way Freeze panes? (5:31)
Organising Data - Quiz
010 Pivot Table
What are the Top 3 tricks of Pivot Table for day to day use? (3:58)
What are the two basic things to avoid for creating Pivot Tables successfully? (2:25)
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:48)
Step 6 - Why is "Grouping" numbers important for analysis? (3:37)
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)
Recap of Top 7 Pivot Table Tricks (11:20)
Pivot Table - Quiz
011 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)
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
012 Data Cleaning - Part 2
How to change case of the text to Upper case, Lower case and Proper case? (1:42)
How to remove extra spaces in a sentence using TRIM function? (1:43)
How to find the length of a text using LEN function? (1:33)
How to convert numbers stored as text back to number using VALUE function? (3:16)
How to join text from different cells using CONCATENATE and Ampersand? (4:55)
Why should you use MS Word's Find and Replace over MS Excel's? (6:06)
How to apply basic Text to Column to split the text of first name and last name? (2: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 to use LEFT and RIGHT functions to extract data from a cell? (4:19)
How can you increase the power of LEFT and RIGHT formulas using SEARCH function? (2:26)
How is MID more powerful than LEFT and RIGHT functions? (3:42)
Data Cleaning - Part 2 - Quiz
013 Logical Formula
What are the Top 7 error and data validity checking formulas? (4:18)
How to write IF formulas - Basic to Intermediate level? (5:19)
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
014 Conditional Formatting
How to change cell colors automatically based on data entered in a cell? (3:15)
Three popular Conditional Formatting Tricks - Heat Map, Data Bars, Icon Sets (2:30)
Four tricks to color cells with Errors, Specific Values, Duplicates and Blank (5:03)
Conditional Formatting - Quiz
015 Subtotal
How to insert Subtotals in a list of data in a worksheet? (5:34)
How to remove Subtotals in a list of data in a worksheet? (1:16)
Subtotal - Quiz
016 Printing & Comments
Printing - Rows to Repeat at Top (Print Titles) (2:19)
Printing - Gridlines (1:14)
Printing - Custom Header & Footer (2:53)
Printing - Quiz
017 Course Wrap Up
Post Training Recap for Excel (15:49)
018 Course Feedback & Final Assessment
Course Feedback
Final Assessment Quiz [activated]
How to keep the zeroes when applying Text to Column on data with preceeding zeroes?
Download
text_to_col_keep zeroes.xlsx
Complete and Continue
Discussion
0
comments
Load more
0 comments