About
About Us
Help
Privacy Policy
Terms of Service
LearnDesk
Learn
Academics
Business
Creative
Health and Fitness
Lifestyle
Personal Development
Software
Topics
Admissions
Engineering
Hardware
Hospitality
Humanities
Chinese
Languages
Maths
Pharma
View All Topics
Topics
Accounting
Advertising
Analysis
Analytics
Business Communication
eCommerce
Entrepreneurship
Finance
Finance - India
Freelancing
View All Topics
Topics
Arts & Crafts
Audio Editing
Audio Production
Dance
Design
Film Production
Music
Photography
Video Production
Writing
View All Topics
Topics
Dieting
Fitness
Food Safety
Games
Medical
Medical Professionals
Meditation
Mental Health
Pregnancy
Safety & First Aid
View All Topics
Topics
Beauty & Makeup
Food
Fashion
Gaming
Home Improvement
Parenting
Pet Care & Training
Relationships
Sustainable Living
View All Topics
Topics
Career Development
Learning
Religion and Spirituality
Self-Improvement
View All Topics
Topics
Accounting
Amazon Web Services
App Development
Continuous Integration
Backup Software
Business Automation
Computational Fluid Dynamics
Business Intelligence
Computer Aided Design (CAD)
View All Topics
Teach
Teach Online
Schedule
Items
Billing
Users
Marketing
Analytics
Settings
Change School
Account
Log In
Sign Up
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 9: Organising Data
Advanced Excel Crash Course
By:
Yoda Learning
Share
Share the link to this page
Copied
Facebook
Twitter
WhatsApp
LinkedIn
Email
Add to Calendar
Add the class to your calendar
Add to Google Calendar
Add to Apple Calendar
Add to Yahoo Calendar
Add to Outlook Calendar
Print
Completed
We'll cover the following topics in this section:
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
Previous
Next
Sign Up
Signup to access thousands of classes
Continue with Facebook
Continue with Google
OR
Continue with Email
Already a member?
Log In
By registering for a LearnDesk account, you agree to our
Terms of Service
and
Privacy Policy
.
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.
Share with Google Contacts