Autoplay
Autocomplete
HTML5
Flash
Player
Speed
Previous Lecture
Complete and continue
Excel Essentials for the Real World
Section 1: Before You Dive In
How to Get the Most Out of This Course (3:01)
Download Course Files (1:09)
Course Outline for Quick Reference
Section 2: Quick Tour of Excel
Excel Workbook, Worksheet, Cell Structure (8:43)
Quick Tour of Excel - Ribbon & Quick Access Toolbar (3:45)
Customizing the Excel Interface (6:00)
Summary: Overview of Important Excel Features
Quiz: Excel Fundamentals
Section 3: Data Input & Hidden Features to Save Time
Overview - Data Input & Hidden Excel Features (1:40)
Data Entry and Editing (7:14)
Basic Excel Formulas (9:26)
Text to Speech (Hidden Feature) (3:09)
Correctly Input Dates and Time Stamps (4:25)
Use AutoFill To Do Your Work (3:56)
Flash Fill for Major Time Saving (Become Pro) (5:00)
Custom Lists With AutoFill (Hidden Feature) (2:30)
Inputting & Editing Comments & Notes (8:13)
Finding Formula & Input Cells with ONE Click (Become Pro) (3:59)
Data Validation to Reduce Time Checking Data (11:04)
Adding Tool Tips (Screen Tips in Excel) (2:18)
Learn to Work with Pictures, Shapes & Other Objects (5:30)
Quiz: Data Input in Excel
Challenge: Data Manipulation & Preparation
Solution: Data Manipulation & Preparation (5:41)
Summary: Key Features to Impress & Save Time
Section 4: Worksheet Navigation, Copying and Protecting
Overview - Worksheet Navigation (1:07)
Useful Excel Shortcuts for Navigation (Become Pro) (11:01)
Copying, Moving & Hiding Worksheets (3:12)
Freeze Panes (Lock Rows, Columns) & Split Screen for Long Reports (3:01)
Hide & Unhide, Insert, Adjust Columns & Rows (6:32)
Copying and Pasting Data (Hidden Features) (5:55)
Protecting Worksheets & Specific Ranges (8:44)
Challenge: Update & Protect Report
Solution: Update & Protect Report (3:56)
Quiz: Worksheet Navigation
Summary: Important Worksheet Features & Shortcuts
Section 5: Excel Formulas & What to be Aware Of
Overview - Excel Formulas & What to be Aware Of (2:25)
How Excel Calculates - Order of Precedence (5:19)
Simple but Useful Formulas (5:18)
Essential Excel Rule (Don't Skip!) (2:30)
Absolute and Relative Cell Referencing (6:19)
Improve Clarity with Range Names (5:09)
Referencing other Workbooks or Worksheets (3:54)
Circular References & How to Locate Them (1:40)
Combine Values From Two or More Cells to ONE Cell (3:44)
Challenge: Allocate Yearly Value Based on Percentages
Solution: Allocate Yearly Value Based on Percentages (3:42)
Quiz: Excel Formulas
Summary: Key Points when Writing Formulas
Section 6: Important Excel Functions (Everything you need to become Pro)
Overview & Finding Functions from Insert Function (5:07)
Important! Working with Functions
COUNT, COUNTA, COUNTBLANK (4:12)
COUNT based on a Condition (COUNTIFS) (9:29)
SUM & AVERAGE Functions (3:10)
SUM by Criteria (SUMIFS, AVERAGEIFS) (6:17)
MAX & MIN (MAXIFS & MINIFS) (4:06)
Rounding Values (ROUND, ROUNDUP & ROUNDOWN) (5:31)
Essential Date Functions (9:21)
Time Calculations - Total Time Worked (6:00)
Handling Formula Errors (5:19)
IF (Then, Else) Function - Conditional Formulas (13:22)
VLOOKUP Function - Lookup Values from Another Place (7:48)
Challenge: How Good Are you with Excel Functions?
Solution: How Good Are you with Excel Functions? (7:11)
NEW 365 Functions: FILTER, SORT, UNIQUE, XLOOKUP & More
Summary: Important Excel Functions
Section 7: Data Cleaning & Management: Sorting, Filtering & Replacing Data
Overview - Data Cleaning, Analysis & Management Tools (2:27)
Sort Data (Organize & Create Order) (7:05)
Unsort Data to Revert to Original Order (1:49)
Add Subtotals to Sorted Data (6:27)
Filter Data to Extract What you Need (6:00)
Delete Blank or Empty Rows in Excel (3:52)
Fill Empty Cells in One Go (Become Pro) (3:26)
Remove Duplicates to Get a Unique List (3:01)
Find, Select & Emphasize (7:10)
Find and Replace Values & Formatting (Hidden Feature) (5:09)
Why Excel Tables Can be a Time Saver (Become Pro) (10:21)
Challenge: Data Management
Solution: Data Management (5:31)
Summary: Tools for Organizing, Cleaning & Managing Data
Special Message - You've Got This (2:14)
Section 8: Formatting including Conditional & Number Formatting
Overview - All About Formatting (1:29)
Formatting Tips - Alignment, Borders & More (5:26)
Better Than Merge (Become Pro) (3:24)
Justify Text Length (Hidden Feature) (2:38)
Number Formatting Options (& Special Formatting) (3:38)
Conditional Formatting (Top Bottom Analysis & More) (6:20)
Icons & Data Bars with Conditional Formatting (6:39)
Quiz: Excel Formatting
Challenge: Format Report
Solution: Format Report (6:45)
Summary: Useful Formatting Tips
Section 9:Printing Files & Saving as PDF
Checklist Before Printing (2:10)
Useful Printing Options (4:22)
Adding Page Breaks & Printing Parts of a Sheet (4:22)
Printing Large Datasets (Make sure you include this!) (3:47)
Header & Footer in Excel (Logo, Page Numbers, etc.) (4:00)
Saving as PDF & Printing Many Sheets (3:42)
Challenge: Prepare Report for Printing
Solution: Prepare Report for Printing (3:20)
Summary: Important Printing Options
Section 10: Workbook Design Principles
Good Spreadsheet Design Principles (with Examples) (5:24)
Adding Hyperlinks (Plus a Smart Workaround) (6:47)
Define Cell Styles & Set Defaults (e.g. for Excel Tables) (5:36)
Workbook Theme & Color to Save Time (5:22)
Use Your Own Excel Templates (4:05)
Quiz: Workbook Design
Summary: Workbook Design Principles
Section 11: Working with Charts
Overview - Create Helpful Charts (2:38)
Chart Basics for a Quick Start (13:37)
Adding More Series to a Chart (Comparison Charts - Method 1) (7:00)
Comparison Charts - Actual & Variance (Method 2) (6:33)
Combination Charts (Plus a Hidden Feature) (7:01)
Dynamic Chart Ranges with Excel Tables (2:33)
Pareto & Histogram Charts (Excel 2016) (7:08)
Treemap Chart (Excel 2016) (7:28)
Sunburst, Pie & Doughnut Charts (6:46)
Sparklines - Mini Charts Inside Cells (6:04)
Quiz: Excel Charts
Challenge: Treemap for Management Report
Solution: Treemap for Management Report (6:39)
Summary: Working with Excel Charts
Section 12: Quick Insights with Pivot Tables
Benefits of PivotTables & Starting Checklist (1:07)
Inserting a PivotTable (10:27)
Why You Should Use Tables as Pivot Source (Become Pro) (2:35)
Sorting Pivot Data & Analyzing Multiple Items (7:36)
Adding Calculations & Multiple Reports with PivotTables (5:40)
Using Slicers & Timeline (Working with Dates) (8:30)
Creating PivotCharts (8:16)
PivotTables or Formulas? When to Use What (2:13)
Challenge: Data Analysis with PivotTables
Solution: Data Analysis with PivotTables (6:11)
Summary: Important PivotTable Features
Section 13: Introduction to Power Query
Power Query, Power Pivot & Power BI - When to use which? (4:00)
Availability of Power Query / Get & Transform
Import Text File with Power Query (12:31)
Merge data in Power Query (for PivotTable) (7:48)
Clean up (Very Messy) Data (multiple headers, unpivot etc.) (10:38)
Challenge: Data Cleanup with Power Query
Solution: Data Cleanup with Power Query (6:56)
Summary: Key Takeaways Power Query
* NEW 365 Excel Functions: FILTER, SORT, UNIQUE, XLOOKUP & More *
Excel Dynamic Arrays & New Functions in Microsoft 365 (3:22)
Availability of the New Excel Functions
Excel's New Calculation Engine: Spill Error and @(at) sign (9:36)
Excel UNIQUE Function: Get List of Distinct Values (6:06)
Excel SORT & SORTBY Functions (8:43)
Excel FILTER Function: Return Multiple Match Results (8:32)
Excel XLOOKUP Function: The New Power Lookup Function (9:11)
Challenge: Get Total Salary by Division
Solution: Get Total Salary by Division (6:39)
Next Steps
Your Next Steps & More Learning (2:00)
Leave a Review
Share Your Success with Me!
BONUS! Detailed Course Manual
DOWNLOAD the Excel Essentials Detailed Manual
How to Get the Most Out of This Course
Discussion
72
comments
Load more
72 comments