About
LearnDesk
Learn  
Topics
Topics
Topics
Topics
Topics
Topics
Topics
Teach
  Teach Online
  Schedule
  Items
  Billing
  Users
  Marketing
  Analytics
  Settings
  Change School
Account
 

Advanced Excel Crash Course

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

Section 13: Conditional Formatting

Advanced Excel Crash Course
By: Yoda Learning
Share the link to this page
Copied
  Twitter
  Email
Add the class to your calendar
Print
  Completed
We'll cover the following topics in this section:
Previous
Next
Home
Search
Saved
Trending Classes
Filter
The latest and the best classes on LearnDesk
Filter by Interest
Close
All Topics
Academics
Business
Creative
Health and Fitness
LifeStyle
Personal Development
Software
Admissions
Engineering
Hardware
Hospitality
Humanities
Chinese
Languages
Maths
Other
Pharma
Research
Science
Social Science
Teaching
Test Preparation
K-12
School
Accounting
Advertising
Analysis
Analytics
Business Communication
eCommerce
Entrepreneurship
Finance
Finance - India
Freelancing
Internet of Things
Digital Transformation
Human Resources
Industry
Management
Marketing
Media
Operations
Other
Project Management
Public Relations
Real Estate
Sales
Strategy
Arts & Crafts
Audio Editing
Audio Production
Dance
Design
Film Production
Music
Photography
Video Production
Writing
Dieting
Fitness
Food Safety
Games
Medical
Medical Professionals
Meditation
Mental Health
Pregnancy
Safety & First Aid
Self Defense
Sports
Beauty & Makeup
Food
Fashion
Gaming
Home Improvement
Parenting
Pet Care & Training
Relationships
Sustainable Living
Travel
Career Development
Learning
Religion and Spirituality
Self-Improvement
Accounting
Amazon Web Services
App Development
Continuous Integration
Backup Software
Business Automation
Computational Fluid Dynamics
Business Intelligence
Computer Aided Design (CAD)
Content Management System
Customer Relationship Management
Database
Data Mining
E-Commerce
Enterprise Asset Management
Enterprise Resource Planning
Game Development
Google Cloud
Linux
Artificial Intelligence
Machine Learning
Master Data Management
Microsoft
Music Software
Network and Security
Open Source
Operating System
Other
Process Management
Productivity Software
Programming Languages
Robotics
Supply Chain Management
Testing
Teaching
Web Development

Sign Up

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.