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
Solution: Prepare Report for Printing
Lecture content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock