Excel Content // My Excel Course

Are You The Office Guru?

{Learn Something New}

Create Custom Course Show All [+]Hide All [-]

80 Lessons : 11 Hours

- Preview lessons
- Locked lessons

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
53:47 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