Advanced Excel Crash Course

Automate your routine
Learn industry best practices, shortcuts, and tips and tricks to get things done and become 4x faster
This course will help you create efficient workflows without any coding involved
The modules are prepared to understand the need and requirement of the learners and will help them create workflows with ease
The course and study materials are available at a reasonable price
Self-paced online course, which you can view at your comfort
Lifetime access to each of the videos present the course

Who should take this course?

Professionals working with a large amount of data
Somebody looking to gain a tangible skill on your resume or gain a competitive advantage over your peers
An analyst or the ones who work in consulting, management, or supervisory roles
Graduate students who aspire to become investment bankers, financial advisory professionals, consultants, etc.

What will you learn in this course?

Learn to become an advanced excel user
Learn to reduce the complexity of spreadsheets
Learn to manipulate data with calculations
Learn to save time using excel tricks
Learn to automate tasks using excel
Learn to create dynamic reports
Learn to unlock the power of dynamic formulas
Learn to make custom formulas/functions
Learn to conquer logical operators
Learn to set up special commands
Learn to build valuation models
Learn to apply easy coding techniques
Learn to use pivot tables
Learn to build spreadsheets
Learn to master unique tips

Author

Yoda Learning

Director, Yoda Learning | Previously J.P. Morgan, Futures First, KPMG

I have tried three work profiles in 4.5 yrs. before I settled on the fourth one (9.5 yrs). I transformed from a College Fresher to a polished professional by working for a Big Four (Audit). I traded Interest Rates Futures & Options and learnt what is greed, fear and stop loss. I worked for an I-Bank and understood how to pitch for new...

School

Yoda Learning's School

Requirements

You should have any version of Microsoft Excel installed on your PC/Mac. The course works on all Microsoft Excel versions 2003, 2007, 2010, 2013, 2016. Of course, the newer, the better, but some features work the same in all the versions.
You should have a basic understanding of Microsoft Excel because we'll take you to the advanced level in this Excel crash course.

Class Contents

Section 1: Ninja Shortcuts

0000 Introduction

Preview

0101 How to Use This Section?

Preview

0102 The "One" Universal Shortcut and a Faster Copy-Paste Technique

Preview

0103 AutoFill Selected Cell(s) With Pre-defined Formula/Text/No.

0104 Navigating Across Worksheets, Data Table Selection and Maneuvring

0105 Adding/Deleting/Selecting Rows/Columns

0106 For "Formula" One Speedstars

0107 Formatting Cells

0108 Using Quick Access Toolbar (QAT)

0109 Formula Auditing Magic

Downloadable Practice Files

Excel Practice Files

Section 2: Business Math Calculations

0201 MAX() in Financial Modeling

0202 LARGE() & SMALL() - For Bidding/Auction Pricing Strategies

0203 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 1 of 2

0204 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 2 of 2

0205 COUNT() vs. COUNTA() vs. COUNTBLANK() vs. COUNTIFS()

0206 SUMPRODUCT() for Weighted Average Computation

0207 POWER() vs. Caret Sign ( ^ )

Section 3: Formatting Tricks

0301 Formatting Tricks Incl. Special Custom Formats

0302 Format Painter - "Uninterrupted Click-ability"

0303 Defining Cell Style - For "One-click" Universal Change

0304 Transferring (Merging) Cell Style From One Workbook to Another

0305 Cell Drag-n-drop Auto Fill Options + Activation Setting

0306 Cell Drag-n-drop Auto Fill Options - Yr-end, Month-end, AlphaNumeric, Fill - Justify

0307 Cell Drag-n-drop Auto Fill Options vs. EOMONTH() - End of Year/Quarter/Month

0308 Paste Special - Transpose vs. TRANSPOSE()

Section 4: Formula Pre-Requisite

0401 Cell Referencing ($) - 1 of 2

0402 Cell Referencing ($) - 2 of 2

Section 5: Special Cell Selections

0501 Go To (Special) - Formulas with Errors

0502 Go To (Special) - Formulas Without Errors

0503 Go To (Special) w. Ctrl+Enter

0504 Go To (Special) - Fill Intermittent Blank Cells - Case 1

0505 Go To (Special) - Fill Intermittent Blank Cells - Case 2

0506 Go To (Special) - Visible Cells w. Shortcut

Section 6: Sort and Filter

0601 Vertical Sort - 1-level and 2-level

0602 Custom Sort

0603 Sort Trick - Add Alternate Blank Rows In-between Existing Rows

0604 Horizontal Sorting (Left to Right) - Auto-realigning 100s of Columns

0605 Filter - Basics

0606 Filter - Choosing the Dataset Correctly

0607 Filter Analysis w. Shortcuts

0608 Filter - SUBTOTAL() for Calculations

0609 Filter - SUBTOTAL() 9 vs. 109 Series

0610 Filter - Applying 2 or More Filters Simultaneously on the Same Sheet

0611 Filter - Color Filter and Text Filter

0612 Advanced Filter - Basics

0613 Advanced Filter - Differential Criteria

0614 Advanced Filter - More Parameters

Section 7: Working with Dates

0701 Dates - Important Concepts 1 of 2

0702 Dates - Important Concepts 2 of 2

0703 Date Formulas - DAY(), MONTH(), YEAR(), DATE()

0704 Date Formulas - TEXT()

0705 Date Formulas - WEEKDAY(), WORKDAY(), NETWORKDAYS()

0706 Date Formulas - WORKDAY.INTL() for Deadline/Due date Calculations w. Custom Weekends/Holidays

0707 Date Formulas - NETWORKDAYS.INTL() for No. of Business Days Calculations w. Custom Weekends/Holidays

0708 Date Formulas - TODAY() and NOW() w. Shortcut

0709 Date Formulas - EOMONTH() for Financial Modeling, Budgets, Due Dates

0710 Date Formulas - EDATE() for Financial Modeling, Budgets, Due Dates

Section 8: Data Input

0801 Data Validation - Drop Down List Using Hard Coded Text and Pre-defined Range

0802 Data Validation - Drop Down List Using Named Range

0803 Data Validation - Numbers w. Error Alert and Input Message

0804 Data Validation - Dates w. Error Alert and Circle Invalid Data

0805 Data Validation - Text Length w. Error Alert

0806 Data Validation - Custom w. Formula Logic

Section 9: Organising Data

0901 Grouping/UnGrouping Columns and Rows - 1 of 2

0902 Grouping/UnGrouping Columns and Rows - 2 of 2

0903 Grouping Trick: Changing Placement of Grouping Button

0904 Cell Gridlines: Turning On/Off

0905 Trick - Hide/Unhide Rows and Columns and Freeze Panes

Section 10: Pivot Table

1001 Pivot Table - Why Should You Learn?

1002 Pivot Table - Pre-requisites

1003 Pivot Table - How to Create One?

1004 Pivot Table - Exploring Pivot Table Grid (Fields)

1005 Pivot Table - Value Field Settings - "Summarize Values By" - Sum, Average, Count, Max etc.

1006 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Grand Total

1007 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Column/Row Total

1008 Pivot Table - Grouping - Numbers

1009 Pivot Table - Grouping - Dates [Years, Months etc.]

1012 Pivot Table - Auto Refresh

1013 Pivot Table - Pivot Chart Shortcut and Sparklines

1014 Pivot Table - Drill Down Option

1015 Pivot Table - Report Filter - Generating 100s of Reports in Few Seconds

1016 Pivot Table - Slicer vs. Report Filter

1017 Pivot Table - Practice - Sales Data Analysis - 1 of 2I (%, Grouping, Chart, Report Filter Pages)

1018 Pivot Table - Practice - Sales Data Analysis - 2 of 2 (Slicer)

1019 Pivot Table - Practice - Closing Stock Analysis

1011 Pivot Table - Refresh vs. Refresh All, Change Data Source and Creating Dynamic Table Data Source

Section 11: Lookup and Conditional Calculations

MATCH() match_type: -1 vs. 0 vs. 1

2-D Lookup (Vertical + Horizontal) - VLOOKUP w. MATCH

2-D Lookup - VLOOKUP w. MATCH - Framework

2-D Lookup - VLOOKUP w. MATCH - Common Mistake #1

2-D Lookup - VLOOKUP w. MATCH - Common Mistake #2

2-D Lookup - VLOOKUP w. MATCH - Practice Exercise

2-D Lookup (Horizontal + Vertical) - HLOOKUP w. MATCH

INDIRECT() - Basics

INDIRECT() w. Range Naming - Applications

3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Question

3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Answer

Reverse Lookup - Question

Reverse Lookup - INDEX() w. MATCH() - 1 of 2

Reverse Lookup - INDEX() w. MATCH() - 2 of 2

SUMIFS(): Conditional Summation (1 Criteria)

SUMIFS(): Conditional Summation (2 Criteria)

SUMIFS(): Conditional Summation (3 criteria) w. Date Range

SUMIFS(): Condition Based Selective Cumulative Running Total

COUNTIFS() - Single/Multiple Criteria: Duplicate Count, Instance No

OFFSET() with MATCH()

Section 12: Data Cleaning

Text Formulas – REPT() for Artifical Column Chart in a Cell

Text Formulas – REPT() & LEN() for Adding Leading Zeroes Before Incomplete Cheque Nos.

Joining Data Strings Using CONCATENATE, & - to be Updated

Find & Replace – Using Wildcard Character ( * )

Find & Replace – Using Wildcard Character ( ? )

Find & Replace – Neutralising Wildcard Characters to Remove Them From Data

Find & Replace – Word vs. Excel

Find & Replace – Cell Format

Text to Columns – Cleaning up Numbers w. Trailing Minus Sign

Text to Columns – Cleaning up Numbers w. Leading "Dr/Cr" Text

Text to Columns – Correcting Invalid Dates (DMY)

Text to Columns – Correcting Invalid Dates (YMD)

Text Formulas – LEFT(), RIGHT() & SEARCH() - 1 of 2

Text Formulas – LEFT(), RIGHT() & SEARCH() - 2 of 2

Text Formulas – MID() & LEN()

Text Formulas – REPLACE() & SUBSTITUTE()

Section 13: Conditional Formatting

Conditional Formatting: Formula Based (Colored Rows Based in User Input)

Conditional Formatting: Formula Based w. AND() - 2-way Input Coloring

Conditional Formatting: Formula Based w. COUNTIFS() - Highlighting Inputs if From Restricted List

Section 14: What-If Analysis

What IF Analysis – Using Form Control Buttons from Developer Tab (Spin Bar, Scroll Bar)

What IF Analysis – Limitations of Spin Bar, Scroll Bar

What IF Analysis – Scenario Managers

What IF Analysis – Goal Seek

What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & 1 Output

What IF Analysis – Data Tables Basics (Sensitivity Analysis) - with PMT for Loan EMI

What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & Multiple Output - Question

What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & Multiple Output - Answer

One-time Fee

$99.99

List Price: $139.99

You save: $40

€93.59

List Price: €131.03

You save: €37.43

£80.45

List Price: £112.64

You save: £32.18

CA$136.81

List Price: CA$191.54

You save: CA$54.73

A$153.85

List Price: A$215.39

You save: A$61.54

S$136.10

List Price: S$190.55

You save: S$54.44

HK$783.19

List Price: HK$1,096.50

You save: HK$313.30

CHF 91.41

List Price: CHF 127.98

You save: CHF 36.57

NOK kr1,095.33

List Price: NOK kr1,533.51

You save: NOK kr438.17

DKK kr697.93

List Price: DKK kr977.13

You save: DKK kr279.20

NZ$168.49

List Price: NZ$235.89

You save: NZ$67.40

د.إ367.25

List Price: د.إ514.16

You save: د.إ146.91

৳10,960.96

List Price: ৳15,345.79

You save: ৳4,384.82

₹8,331.14

List Price: ₹11,663.93

You save: ₹3,332.78

RM477.70

List Price: RM668.80

You save: RM191.10

₦126,037.39

List Price: ₦176,457.39

You save: ₦50,420

₨27,728.08

List Price: ₨38,820.43

You save: ₨11,092.34

฿3,701.79

List Price: ฿5,182.66

You save: ฿1,480.86

₺3,251.67

List Price: ₺4,552.47

You save: ₺1,300.80

B$513.17

List Price: B$718.47

You save: B$205.29

R1,913.51

List Price: R2,678.99

You save: R765.48

Лв183.04

List Price: Лв256.26

You save: Лв73.22

₩137,500.16

List Price: ₩192,505.72

You save: ₩55,005.56

₪376.08

List Price: ₪526.53

You save: ₪150.44

₱5,758.67

List Price: ₱8,062.37

You save: ₱2,303.69

¥15,492.10

List Price: ¥21,689.56

You save: ¥6,197.46

MX$1,697.24

List Price: MX$2,376.20

You save: MX$678.96

QR362.79

List Price: QR507.92

You save: QR145.13

P1,387.12

List Price: P1,942.02

You save: P554.90

KSh13,448.65

List Price: KSh18,828.65

You save: KSh5,380

E£4,787.02

List Price: E£6,702.02

You save: E£1,915

ብር5,664.26

List Price: ብር7,930.19

You save: ብር2,265.93

Kz83,485.81

List Price: Kz116,883.48

You save: Kz33,397.66

CLP$95,360.46

List Price: CLP$133,508.46

You save: CLP$38,148

CN¥724.53

List Price: CN¥1,014.38

You save: CN¥289.84

RD$5,902.47

List Price: RD$8,263.69

You save: RD$2,361.22

DA13,439.62

List Price: DA18,816

You save: DA5,376.38

FJ$229.29

List Price: FJ$321.01

You save: FJ$91.72

Q776.47

List Price: Q1,087.09

You save: Q310.61

GY$20,894.43

List Price: GY$29,253.04

You save: GY$8,358.61

ISK kr14,065.59

List Price: ISK kr19,692.39

You save: ISK kr5,626.80

DH1,009.26

List Price: DH1,413.01

You save: DH403.74

L1,779.82

List Price: L2,491.82

You save: L712

ден5,758.50

List Price: ден8,062.13

You save: ден2,303.63

MOP$806.12

List Price: MOP$1,128.60

You save: MOP$322.48

N$1,913.23

List Price: N$2,678.60

You save: N$765.36

C$3,661.78

List Price: C$5,126.64

You save: C$1,464.86

रु13,321.12

List Price: रु18,650.11

You save: रु5,328.98

S/368.06

List Price: S/515.29

You save: S/147.23

K378.12

List Price: K529.39

You save: K151.26

SAR375.03

List Price: SAR525.05

You save: SAR150.02

ZK2,574.10

List Price: ZK3,603.84

You save: ZK1,029.74

L465.65

List Price: L651.93

You save: L186.28

Kč2,362.20

List Price: Kč3,307.17

You save: Kč944.97

Ft36,810.41

List Price: Ft51,536.04

You save: Ft14,725.63

SEK kr1,086.49

List Price: SEK kr1,521.13

You save: SEK kr434.64

ARS$87,266.68

List Price: ARS$122,176.84

You save: ARS$34,910.16

Bs687.45

List Price: Bs962.46

You save: Bs275.01

COP$390,268.69

List Price: COP$546,391.78

You save: COP$156,123.08

₡50,117.34

List Price: ₡70,166.29

You save: ₡20,048.94

L2,455.43

List Price: L3,437.70

You save: L982.27

₲738,156.27

List Price: ₲1,033,448.32

You save: ₲295,292.04

$U3,836.09

List Price: $U5,370.68

You save: $U1,534.59

zł404.46

List Price: zł566.27

You save: zł161.80

What's Included

Language: English

Level: All levels

Skills: Data Visualization, Microsoft Excel, Excel, Data Management, Data Science, Data Analytics

Age groups: All ages

Duration: 8 hours 33 minutes

133 Videos

1 Document

Advanced Excel Crash Course

Advanced Excel Crash Course

About the Class

Author

Yoda Learning

School

Yoda Learning's School

Requirements

Class Contents

What's Included

Sign Up

Sign Up

Share