11-min Excel Macros for Non-Programmers

Advanced Excel Ninja [ Part of Beginner - Advanced Excel Course ]

Introduction 🔒
How to use this section? 🔒
The "ONE" Universal shortcut & a faster Copy-Paste technique 🔒
AutoFill selected cell(s) with pre-defined formula/text/no. 🔒
Navigating across Worksheets; Data Table selection & manoeuvring 🔒
Adding/Deleting/Selecting Rows/Columns 🔒
Formatting cells 🔒
Using Quick Access Toolbar (QAT) 🔒
For "Formula" One Speedstars 🔒
Formula auditing magic 🔒
MAX() in Financial Modeling 🔒
LARGE() & SMALL() - for bidding/auction pricing strategies 🔒
Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 1 of 2 🔒
Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 2 of 2 🔒
COUNT() vs. COUNTA() vs. COUNTBLANK() vs. COUNTIFS() 🔒
SUMPRODUCT() for weighted average computation 🔒
POWER() vs. Caret sign ( ^ ) 🔒
Formatting Tricks incl. Special Custom Formats 🔒
Format Painter - "Uninterrupted Click-ability" 🔒
Defining Cell Style - for "one-click" universal change 🔒
Transferring (Merging) Cell Style from one workbook to another 🔒
Cell drag-n-drop Auto Fill Options + Activation Setting 🔒
Cell drag-n-drop Auto Fill Options - Yr-end, Month-end, AlphaNumeric, Fill - Justify 🔒
Cell drag-n-drop Auto Fill Options vs. EOMONTH() - End of Year/Quarter/Month 🔒
Paste Special - Transpose vs. TRANSPOSE() 🔒
Cell Referencing ($) - 1 of 2 🔒
Cell Referencing ($) - 2 of 2 🔒
Go To (Special) - Formulas with Errors 🔒
Go To (Special) - Formulas without Errors 🔒
Go To (Special) w. Ctrl+Enter 🔒
Go To (Special) - Fill Intermittent Blank Cells - Case 2 🔒
Go To (Special) - Fill Intermittent Blank Cells - Case 1 🔒
Go To (Special) - Visible Cells w. Shortcut 🔒
Vertical Sort - 1-level & 2-level 🔒
Custom Sort 🔒
Sort Trick - add alternate blank rows in-between existing rows 🔒
Horizontal Sorting (Left to Right) - auto-realigning 100s of columns 🔒
Filter - Basics 🔒
Filter - Choosing the dataset correctly 🔒
Filter analysis w. shortcuts 🔒
Filter - SUBTOTAL() 9 vs. 109 series 🔒
Filter - SUBTOTAL() for calculations 🔒
Filter - Applying 2 or more Filters simultaneously on the same sheet 🔒
Filter - Color Filter & Text Filter 🔒
Advanced Filter - Basics 🔒
Advanced Filter - More parameters 🔒
Advanced Filter - Differential Criteria 🔒
Dates - Important Concepts 1 of 2 🔒
Dates - Important Concepts 2 of 2 🔒
Date Formulas - DAY(), MONTH(), YEAR(), DATE() 🔒
Date Formulas - TEXT() 🔒
Date Formulas - WEEKDAY(), WORKDAY(), NETWORKDAYS() 🔒
Date Formulas - NETWORKDAYS.INTL() for no. of business days calculations w. custom weekends/holidays 🔒
Date Formulas - WORKDAY.INTL() for deadline/due date calculations w. custom weekends/holidays 🔒
Date Formulas - TODAY() and NOW() w. Shortcut 🔒
Date Formulas - EOMONTH() for Financial Modeling, Budgets, Due Dates 🔒
Data Validation - Drop Down List using hard coded text & pre-defined range 🔒
Date Formulas - EDATE() for Financial Modeling, Budgets, Due Dates🔒
Data Validation - Drop Down List using named range 🔒
Data Validation - Numbers w. Error Alert and Input Message 🔒
Data Validation - Text Length w. Error Alert 🔒
Data Validation - Dates w. Error Alert and Circle Invalid Data 🔒
Data Validation - Custom w. formula logic 🔒
Grouping/UnGrouping Columns and Rows - 1 of 2 🔒
Grouping/UnGrouping Columns and Rows - 2 of 2 🔒
Grouping Trick: Changing placement of Grouping Button 🔒
Cell Gridlines: Turning On/Off 🔒
Trick - Hide/Unhide Rows and Columns and Freeze panes 🔒
Pivot Table - Why should you learn? 🔒
Pivot Table - How to create one? 🔒
Pivot Table - Pre-requisites 🔒
Pivot Table - Exploring Pivot Table grid (Fields) 🔒
Pivot Table - Value Field Settings - "Summarize Values By" - Sum, Average, Count, Max etc. 🔒
Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Grand Total 🔒
Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Column/Row Total 🔒
Pivot Table - Grouping - Numbers 🔒
Pivot Table - Refresh vs. Refresh All, Change Data Source and creating Dynamic Table Data Source 🔒
Pivot Table - Grouping - Text 🔒
Pivot Table - Grouping - Dates [Years, Months etc.] 🔒
Pivot Table - Auto Refresh 🔒
Pivot Table - Pivot Chart Shortcut and Sparklines 🔒
Pivot Table - Report Filter - Generating 100s of reports in few seconds 🔒
Pivot Table - Drill Down option 🔒
Pivot Table - Slicer vs. Report Filter 🔒
Pivot Table - Practice - Sales Data Analysis - 1 of 2I (%, Grouping, Chart, Report Filter Pages) 🔒
Pivot Table - Practice - Sales Data Analysis - 2 of 2 (Slicer) 🔒
Pivot Table - Practice - Closing Stock Analysis 🔒
VLOOKUP() for Starters 🔒
VLOOKUP w. TRUE vs. FALSE 🔒
HLOOKUP() vs. VLOOKUP() 🔒
VLOOKUP w. TRUE - applications
SUMIFS(): Conditional Summation (3 criteria) w. date range 🔒
SUMIFS(): Conditional Summation (2 criteria) 🔒
SUMIFS(): Conditional Summation (1 criteria) 🔒
Reverse Lookup - INDEX() w. MATCH() - 2 of 2 🔒
Reverse Lookup - INDEX() w. MATCH() - 1 of 2 🔒
MATCH() - Basics 🔒
Reverse Lookup - Question 🔒
3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Answer 🔒
3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Question 🔒
INDIRECT() w. Range Naming - Applications 🔒
INDIRECT() - Basics 🔒
MATCH() match_type: -1 vs. 0 vs. 1 🔒
2-D Lookup (Horizontal + Vertical) - HLOOKUP w. MATCH 🔒
2-D Lookup - VLOOKUP w. MATCH - Practice Exercise 🔒
2-D Lookup - VLOOKUP w. MATCH - Common Mistake #2 🔒
2-D Lookup - VLOOKUP w. MATCH - Common Mistake #1 🔒
2-D Lookup - VLOOKUP w. MATCH - Framework 🔒
2-D Lookup (Vertical + Horizontal) - VLOOKUP w. MATCH 🔒
SUMIFS(): Condition based Selective Cumulative Running Total 🔒
COUNTIFS() - Single/Multiple Criteria: Duplicate Count, Instance No. 🔒
OFFSET() with MATCH() 🔒
Text Formulas – Change Case using UPPER(), PROPER() & LOWER() 🔒
Text Formulas – Remove Leading, Trailing & excess Intermittent spaces using TRIM() 🔒
Text Formulas – Using LEN() & TRIM() together 🔒
Text Formulas – T(), N() & VALUE() 🔒
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 – Basics (Delimited) 🔒
Text to Columns – Basics (Fixed Width) + Hidden Trick 🔒
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() - 2 of 2 🔒
Text Formulas – LEFT(), RIGHT() & SEARCH() - 1 of 2 🔒
Text Formulas – MID() & LEN() 🔒
Text Formulas – REPLACE() & SUBSTITUTE() 🔒
Logical formulas: ISNUMBER(), ISTEXT(), ISBLANK(), ISERROR(), ISFORMULA() 🔒
Logical formulas: Basics of IF() and Nested IF() 🔒
Conditional Formatting: Actual vs. Budget Comparison through Arrows 🔒
Logical formulas: Combining IF() w. AND() & OR() 🔒
Logical formulas: AND(), OR() 🔒
Conditional Formatting: Data Bars, Color Scales, Icon Sets 🔒
Conditional Formatting: w. Drop-down list based Input 🔒
Conditional Formatting: Blanks, Errors, Values, Duplicates 🔒
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 🔒
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 – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & 1 Output 🔒
What IF Analysis – Goal Seek 🔒
What IF Analysis – Scenario Managers 🔒
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 🔒
Subtotal - Creating a 1-level SubTotal on a Sorted list 🔒
Subtotal - Deleting a SubTotal 🔒
Subtotal - Creating a 2-level SubTotal on a Sorted list 🔒
Subtotal - Coloring the Subtotal rows with different Color 🔒
A Subtotal - Trick to Merge blocks of cells with similar names 🔒
Consolidate - 2 Dimensions 🔒
Consolidate - 3 Dimensions 🔒
Cell level Security 🔒
Cell level Security Challenge 🔒
File level Security 🔒
Sheet level Security 🔒
Printing - Rows to Repeat at Top (Print Titles) 🔒
Printing - Gridlines 🔒
Printing - Custom Header & Footer 🔒
Printing - Page Order - Vertical vs. Horizontal 🔒
Printing - Tricks for Financial Analysts - Check underlying formulas 🔒
Printing - Print Entire Workbook 🔒
Printing - Tricks for Financial Analysts - Cell Comments 🔒
Comments - Shortcuts, Inserting Picture in Comment Box 🔒
Split Windows, Viewing multiple Windows - For working with different workbooks, worksheets & scattered cell ranges simultaneously 🔒
Hyperlinking (Ctrl+K) 🔒

Excel Turboboost

11-min Excel Macros for Non-Programmers