Excel Course

Excel Content // My Excel Course


Are You The Office Guru?

{Learn Something New}


80 Lessons : ~11 Hours

- Preview lessons
- Locked lessons
Create Custom Course Show All [+]Hide All [-]
Shortcuts 26 min

1 Shortcuts: Ctrl, Shift, Arrows

06:38
00:45

Precisely select cells with 'Ctrl + Click'

01:05

Extend cell ranges with 'Shift + Click'

01:28

Move quickly between cells with 'Ctrl + Arrows'

02:30

Extend cell ranges with 'Shift + Arrows'

03:10

Select cell ranges fast with 'Ctrl + Shift + Arrows'

2 Shortcuts: Home, End, Page Up/Dn

06:25
00:23

Freeze Panes

00:30

Move from 'Home' and 'End' of your worksheet

01:25

Top Left to Bottom Right - 'Ctrl+Home' & 'Ctrl+End'

02:35

Move Between Tabs Quickly

02:58

Top Left to Bottom Right PLUS Selecting 'Ctrl+Shift+Home' & 'Ctrl+Shift+End'

3 Shortcuts: F2, insert/delete, copy sheets, speed editing

07:34
00:18

Edit Cells Quickly

00:40

Lock Your Reference Cells - Absolute vs. Relative

05:45

Quickly Insert Cell Comments

06:15

Copy Tabs Like a Ninja

4 Shortcuts: Ctrl+C,V,X,Z,Y and F4. Plus more insert/delete

05:31
00:25

Hot Keys: Ctrl+C,V,X,Y,Z & F4

01:40

Quickly Insert & Delete Rows or Columns

03:10

Quickly Insert & Delete Cells

Data & Formula Integrity 60 min

5 Data Styling & Layout

07:36
00:50

Consistency Matters - Table Elements & Design

02:20

Format Cells Hotkey

04:12

Customize YOUR Quick Access Toolbar

05:20

Show / Hide Gridlines

06:20

Macro Show & Tell

6 More on Layouts, Plus Formula Consistency and Auditing

05:42
02:30

Auto Sum Hotkey

03:10

Audit Formulas

7 Green Triangles & Data Validation

05:14
00:17

Formula Errors - Green Triangles

02:40

Validate Cell Input

8 Simplifying Formulas w/ Templates

05:47
00:48

Type less - Reusable Templates

9 Summarizing Data While Keeping Integrity

09:20
00:38

Use Vertical, NOT Horizontal Space

01:30

Remove Duplicate Values

05:49

Summary Table Integrity

10 Integrity Between Users: Workbook Protection

11:31
00:00

Protection: File

02:26

Protection: Workbook

03:30

Protection: Worksheet

06:35

Protection: Range

08:40

Protection: Track Changes

11 Conditional Formatting for Integrity

04:58
00:40

Variances That POP

12 Name Manager & Visibility Tools for Integrity

09:44
Formulas: Text 33 min

13 Left() Right() Mid()

05:49
00:36

Left() Function

01:34

Right() Function

03:42

Mid() Function

14 Len() Search() Trim()

08:54
00:10

Len() Function

00:50

Search() Function

06:40

Trim() Function

15 Find() Replace() Substitute()

06:33
00:18

Find() Function

02:30

Replace() Function

04:17

Substitute() Function

16 Text() Concatenate()

12:00
01:00

Text() Function

05:30

Concatenate() Function

Formulas: Logic 30 min

17 If() And() Or() Not()

10:01
00:50

If() Function

03:45

And() Function

06:37

Or() Function

08:58

NOT() Function

18 IfError() IfNA()

05:49
01:37

IfError() Function

03:45

IfNA() Function

19 Nested If() Statements

08:06
00:47

Nested If() Function

04:10

Evaluate Formulas & Functions

20 If() Statement Comparison Operators

06:24
00:35

Comparison Operators Explained

Formulas: Info 15 min

21 Cell(): more useful than you'd think

06:52
00:12

Cell() Function: Filename

03:40

Cell() Function: Address

22 If() Statement Using Is...() Statements

08:23
00:22

Is...() Functions

Formulas: Math 56 min

23 Sum() Count() Average() Plus 3D Formulas

09:42
00:40

Sum() Count() Average() Functions

04:55

3D Formulas

24 CountA() CountBlank() AverageA()

04:05
00:30

CountA(), CountBlank(), AverageA() Functions

25 Subtotal() Plus Outlines

08:09
00:37

Subtotal() Function

03:42

Outline Button

26 SumIf() CountIf() AverageIf() SumIfs() CountIfs() AverageIfs() #1

07:09
00:31

SumIf(), CountIf(), AverageIf() Functions

03:45

SumIfs(), CountIfs(), AverageIfs() Functions

27 SumIf() CountIf() AverageIf() SumIfs() CountIfs() AverageIfs() #2

07:44
00:25

SumIfs() Comparison Operators

28 Math + Logic + Text + Info ALL TOGETHER!

05:12
00:40

All Together: Info, Text, & Math

29 SumProduct(), Plus Conditions, Plus Array Intro

09:47
00:47

SumProduct(): Simple & w/ Conditions

06:53

SumProduct() Plus Intro into Array Formulas

30 Math + Wildcards, Question Marks & Dates

04:31
00:20

SumIf(): Asterisk Wildcard (*)

01:43

SumIf(): Question Mark Wildcard

03:10

SumIf(): Dates Filters

Formulas: Lookup 63 min

31 Lookup(), VLookup(), HLookup()

09:03
00:40

Lookup()

04:38

VLookup(): Intro

08:20

HLookup(): Intro

32 Mastering VLookup() #1

13:26
00:47

Mastering VLookup()

01:50

Dynamic VLookup()

06:02

Dynamic VLookup() using Match()

10:38

VLookup() Table Summary

33 Mastering VLookup() #2

07:26
00:11

VLookup() Issue - Static Range

02:50

VLookup() Combine Tables

05:42

Summarize With a PivotTable

34 Index() & Match(): More powerful than VLookup()

10:24
00:13

Match(): Intro

01:25

Index() - Simple Static Example

05:42

Index() - Dynamic Example

35 All Together Now: Lookup, Math, Text, Logic #1

07:21
00:18

All Together: Lookup, Math, Text, Logic

36 All Together Now: Lookup, Math, Text, Logic #2

06:52
00:10

All Together: Index, Math, Text, Logic

37 Lookup Functions: Review

07:59
00:28

Lookup(): Review Array Method

01:30

Lookup(): Review Vector Method

02:33

VLookup(): Review - Approx Match

04:00

Match(): Review - Approx Match

05:42

Index(): Review - Exact Match

06:28

VLookup(): Review - Exact Match

Formulas: Finance 32 min

38 Time Value of Money: Loan Example

08:40
00:40

PMT() - Calculate A Loan Payment

04:15

PV() - Find the Present Value

05:35

Rate() - What's The Interest Rate?

06:27

NPER() - How Many Loan Payments?

39 Time Value of Money: Future Value

05:22
00:13

FV() - What's the Future Value?

40 Financial Modeling

08:10
01:49

NPV() - Net Present Value

03:04

IRR() - Internal Rate of Return

05:43

MIRR() - Modified Internal Rate of Return

06:21

Discounted Cash Flow Table

07:29

Discounted Cash Flow Chart

41 Depreciation & Amortization

09:37
01:00

SLN() - Depreciation: Straight Line

01:51

SYD() - Depreciation: Sum of Years' Digits

03:37

DB() - Depreciation: Declining Balance

06:16

DDB() - Depreciation: Double Declining Balance

08:31

Depreciation Method Compared

Formulas: Date & Time 42 min

42 Understanding Excel Dates & Time

05:37
00:17

Understanding Excel Dates

02:36

Understanding Excel Times

03:39

Custom Formatting: Date & Time

43 Year(), Month(), Day(), Dates() - Hour(), Minute(), Second(), Time() - Today(), Now()

06:42
00:24

Year(): Extract the Year from a Date Value

00:55

Shortcut: Ctrl+Shift+#

01:26

Month(): Extract the Month from a Date Value

01:52

Day(): Extract the Day from a Date Value

01:59

Date(): Create a date serial number

02:54

Hour(): Extract the Hour from a Time Value

02:54

Minute(): Extract the Minute from a Time Value

02:54

Second(): Extract the Second from a Time Value

03:26

Time(): Create a time serial number

03:39

Shortcut: Ctrl+Shift+`

03:39

Shortcut: Ctrl+Shift+@

04:09

Today(): Returns Today's Date Serial Number

04:44

Now(): Returns Today's Date & Time Serial Number

05:13

Text(): Create a Custom Date & Time Format

44 EDate(), EOMonth(), WeekNum(), WeekDay(), NetWorkdays.Intl(), Workday.Intl()

07:02
00:14

EDate(): Returns Serial Number for the SAME Day DIFFERENT Month

01:01

EOMonth(): Returns End Of Month Date Serial Number

01:48

WeekNum(): Returns the Week Number in a Year

02:20

WeekDay(): Returns the Day Number of the Week

03:24

NetWorkdays.Intl(): Returns the Count of Days between Two Dates

04:46

Workday.Intl(): Returns Date Serial Number of "X" Workdays from Given Date

45 Powerful Date/Time Formulas In Action #1

09:40
01:07

NetWorkdays.Intl(): In Action

03:16

Complex Date Functions: In Action

46 Powerful Date/Time Formulas In Action #2

12:56
00:08

Time Functions: In Action

02:23

Time Functions: Analysis Within A PivotTable

Formulas: Array 26 min

47 Array Formulas Explained

12:02
00:10

Array Formulas: What Are They?

09:10

Shortcut: F9

48 Array Formulas In Action

09:25
00:11

Array Formulas: Real Life Example 1

49 Combining an Array formula with a Financial formula

04:51
00:10

Array Formulas: Real Life Example 2

Name Manager 22 min

50 Name Manager: The How's & Why's

05:33
00:13

Name Manager: The How

03:58

Name Manager: The Why

51 Name Manager in Action

09:17
01:50

Create Name From Selection

04:23

Apply Names - Finance Example In Action

05:06

Create Names & Apply Them - Financial Statement Ratios

52 Name Manager + Data Validation + Offset()

07:20
00:14

Name A Block Of Cells!

02:02

Use a Formula As a Named Range: Offset()

Conditional Formatting 58 min

53 CF Basics: Highlighting, Data Bars, Color Scales, Icon Sets

08:55
00:47

Greater Than / Less Than / Equal To

02:50

Manage Conditional Formatting

03:45

Top / Bottom / Average

06:00

Data Bars / Color Scales / Icon Sets

54 Unique/ Duplicate Values and a Formula Alternative

05:54
00:32

Format Unique & Duplicate Values

01:25

Find Duplicate Value - Formula Driven

55 CF Formula Intro with IS...() formulas

11:38
00:10

TRUE / FALSE Formulas

01:55

Conditionally Format Your Variances

05:22

Highlight Reconciled Rows

56 CF Real Life Examples (Built-In & Formulas)

08:30
00:50

P&L Data Bar Example

02:15

Max / Min Formula

57 CF w/in Tables: Real Life Form Example

12:15
00:12

Conditionally Format a Form Built within an Excel Table

06:52

Stop If True?

08:39

Icon Sets In Depth

58 Fun with CF: Find Intersecting Point with Formulas

07:28
00:11

Format Intersecting Point: Adv Lookup Formula

59 Calendar Example: Highlight MY Task

03:28
00:40

Format Only Cells That Contain ____

Tables 23 min

60 Sort & Filter Like a Pro

07:47
00:45

Ctrl+Backspace: Pop to Activecell

01:38

Ctrl+Shift+L: Auto Filter Shortcut

03:00

Wildcard Searches: ? *

05:47

Advanced Filters

61 Excel Tables In a Nutshell

06:43
00:15

Ctrl+T: Create Table Shortcut

01:49

Interactive Filtering: Slicers

02:50

No Need To Freeze Panes!

03:34

Ctrl+Shift+T: Piece of Cake Total Row

04:06

Dynamic Formulas!

04:52

Add a Row/Record with TAB

05:54

Excel Table Formatting

62 Excel Tables: Formulas, CF, PivotTables

08:17
00:15

Dynamic & Intuitive Formulas!

03:25

Conditional Formatting Excel Table

05:30

Manually Expand or Contract Your Excel Table Range

06:05

Analyze Excel Table as a PivotTable

Charting 73 min

63 Clustered Column Chart: Real Life Example

10:09
00:16

Create Cluster Column Chart

02:13

Set up Dynamic Chart Label: Title, Axis, etc.

02:50

Missing Legend Labels: Series1, Series2.

04:43

Set up A Chart Like a Boss

06:57

Shortcut: Ctrl+1 within a Chart

64 Chart System & Customization

07:36
01:30

Chart Creation System

03:15

Formatting Chart: Auto Style & Auto Layout

04:43

Chart + Custom Shapes

06:32

Display Units in Thousands

65 Chart System, Pie Charts, Sparkline Intro

09:25
00:13

Create Pie Chart: Systematically

02:33

Pie Chart: Custom Formatting

04:59

Create Doughnut Chart: Systematically

06:40

Understanding Sparklines: Set One Up

07:46

Grouping Sparklines & Comparing Apples to Apples

09:04

Show Sparkline: High/Low/Negative/First/Last Points

66 Chart System, XY Scatter, Trendlines, & Customization

09:05
00:09

Create an XY Scatter: Systematically

03:48

Custom Formatting: Labels, Axis, Data Points, Trendline

67 Chart System, Combo Charts, Google Finance Example

05:39
02:10

Create a Secondary Axis

03:17

Format Chart Axis: Bounds

68 Chart System, Combo Charts, Financial Modeling

12:45
03:30

Combo Chart: Finance Example

04:19

Create a Custom Combo Chart

07:36

Update Axis With Referenced Labels

08:39

Combo Chart: Align Gridlines to Axis Bounds & Units

11:04

Setting Up a Gradient Background

69 Sparklines: Real Life Google Example

11:04
02:08

Delete Sparkline

02:49

Create a Sparkline

04:52

Group Your Related Sparklines

05:02

Switch Sparkline Direction: Right-to-Left/ Left-to-Right

05:57

Format Markers & Line

07:43

Dynamic Sparklines: Hidden & Empty Cells

70 Dynamic & Interactive Charts: Tables, Slicers, Spin Button

07:24
00:48

Interactive Charts: Insert a Chart Slicer

02:32

Interactive Charts: Spin Button

PivotTables 128 min

71 Simple Example: Setup Example & Create 1st Pivot

07:57
01:48

Creating Your First PivotTable

03:33

Understanding PivotTables: Drag & Drop

72 Simple Example: Pivot Continued & Ribbon Options

16:46
00:05

Understanding PivotTables In-Depth: Drag & Drop

04:52

PivotTable Vs Manual Creation Summary

06:52

Overview of All PivotTable Ribbon Options: Analyze Tab

11:27

Overview of All PivotTable Ribbon Options: Design Tab

73 Big Data Example: Example Setup + 1st Pivot

10:44
00:20

BIG DATA: Example Setup

08:24

BIG DATA: First Pivot Table

74 Big Data Example: Pivot Features #1

11:20
00:12

BIG DATA: Vertical Vs Horizontal Space

02:18

BIG DATA: Filter for Top 10

04:19

BIG DATA: Sort Data

07:55

BIG DATA: Remove Duplicate Subtotal

75 Big Data Example: Pivot Features #2

13:40
00:17

BIG DATA: Field Settings - Sum/Count/Average

05:43

BIG DATA: Remove Grand Totals

06:18

BIG DATA: Drill Into Summarized Value

09:33

BIG DATA: Value Quadrant - Multiple Amounts Summarized

10:54

BIG DATA: Select Field, THEN Filter

12:03

BIG DATA: Turn Generate GetPivotData OFF

76 Big Data Example: Pivot Features #3

15:45
00:50

BIG DATA: Pivot Layout

03:08

BIG DATA: Show Unique Labels

03:43

BIG DATA: Remove Subtotals

05:25

BIG DATA: Filter Quadrant

05:52

BIG DATA: Grouping - by Dates, by Number

77 Big Data Example: Pivot Features #4

16:15
01:16

BIG DATA: Expand & Collapse [+] [-]

02:03

BIG DATA: Show Value As?

05:41

BIG DATA: Custom Labels Names

07:21

BIG DATA: Custom Label Order

11:43

BIG DATA: Show Details of Value

13:29

BIG DATA: Wildcard Filters & Multiple Filters

78 Messy Data Example + Calculated Field/Item

09:38
02:28

Insert Calculated Field

05:56

Insert Calculated Item

79 PivotCharts & Features

07:20
00:34

PivotChart: First Time Setup

02:53

PivotChart: Additional Chart Fields

04:55

PivotChart: Insert Slicer (aka Filter)

80 Dashboard Development

18:33
01:33

Dashboard: Set up Pivot Summaries

05:07

Dashboard: Set up Slicers & Edit Connections

08:38

Dashboard: Set up PivotChart Summaries

14:51

Dashboard: Set up Conditional Formatting