Transcription
Excel & Analytics CoursesTable of Contents1. Excel Ninja . 22. Excel VBA . 83. Excel Dashboard. 114. Power Query . 135. MS PowerPoint . 156. MS Word . 197. MS Access . 238. MS Outlook . 269. MS Visio . 2810. Power BI . 3011. Tableau. 3212. Alteryx . 3413. KNIME . 3514. 360 Financial Modeling & Valuation Ninja Course . 3715. Project Finance Financial Modelling . 3916. Python for Finance . 4017. QlikSense. 4218. Power BI DAX . 4419. SQL Ninja . 4520. PMP . 4821. Libre Office . 5322. SAS Programming. 5423. Excel for HR . 5624. Excel for Sales & Marketing . 5625. New Microsoft Excel 2019 and Office 365 Features . 5626. Google Sheets for Excel Users. 56
Excel & Analytics CoursesExcel NinjaSN01020304050607080910Lecture NameSection 1: Ninja Shortcuts0000 Introduction0101 How to use this section?0102 The "ONE" Universal shortcut & a faster Copy-Paste technique0103 AutoFill selected cell(s) with pre-defined formula/text/no.0104 Navigating across Worksheets; Data Table selection & manoeuvring0105 Adding/Deleting/Selecting Rows/Columns0106 For "Formula" One Speedstars0107 Formatting cells0108 Using Quick Access Toolbar (QAT)0109 Formula auditing :09Section 2: Business Math Calculations111213141516170201 MAX() in Financial Modeling0202 LARGE() & SMALL() - for bidding/auction pricing strategies0203 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 1 of 20204 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 2 of 20205 COUNT() vs. COUNTA() vs. COUNTBLANK() vs. COUNTIFS()0206 SUMPRODUCT() for weighted average computation0207 POWER() vs. Caret sign ( )2:072:063:076:063:033:062:06Section 3: Formatting Tricks18192021222324250301 Formatting Tricks incl. Special Custom Formats0302 Format Painter - "Uninterrupted Click-ability"0303 Defining Cell Style - for "one-click" universal change0304 Transferring (Merging) Cell Style from one workbook to another0305 Cell drag-n-drop Auto Fill Options Activation Setting0306 Cell drag-n-drop Auto Fill Options - Yr-end, Month-end, AlphaNumeric, Fill Justify0307 Cell drag-n-drop Auto Fill Options vs. EOMONTH() - End ofYear/Quarter/Month0308 Paste Special - Transpose vs. TRANSPOSE()5:031:094:001:031:032:081:024:03Section 4: Formula Pre-Requisite26270401 Cell Referencing ( ) - 1 of 20402 Cell Referencing ( ) - 2 of 23:003:03Section 5: Special Cell selections2829303132330501 Go To (Special) - Formulas with Errors0502 Go To (Special) - Formulas without Errors0503 Go To (Special) w. Ctrl Enter0504 Go To (Special) - Fill Intermittent Blank Cells - Case 10505 Go To (Special) - Fill Intermittent Blank Cells - Case 20506 Go To (Special) - Visible Cells w. Shortcut2:072:002:016:002:012:04
Excel & Analytics CoursesSection 6: Sort & Filter34353637383940414243444546470601 Vertical Sort - 1-level & 2-level0602 Custom Sort0603 Sort Trick - add alternate blank rows in-between existing rows0604 Horizontal Sorting (Left to Right) - auto-realigning 100s of columns0605 Filter - Basics0606 Filter - Choosing the dataset correctly0607 Filter analysis w. shortcuts0608 Filter - SUBTOTAL() for calculations0609 Filter - SUBTOTAL() 9 vs. 109 series0610 Filter - Applying 2 or more Filters simultaneously on the same sheet0611 Filter - Color Filter & Text Filter0612 Advanced Filter - Basics0613 Advanced Filter - Differential Criteria0614 Advanced Filter - More 1:024:054:023:01Section 7: Working with Dates484950515253545556570701 Dates - Important Concepts 1 of 20702 Dates - Important Concepts 2 of 20703 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/holidays0707 Date Formulas - NETWORKDAYS.INTL() for no. of business days calculationsw. custom weekends/holidays0708 Date Formulas - TODAY() and NOW() w. Shortcut0709 Date Formulas - EOMONTH() for Financial Modeling, Budgets, Due Dates0710 Date Formulas - EDATE() for Financial Modeling, Budgets, Due on 8: Data Input5859606162630801 Data Validation - Drop Down List using hard coded text & pre-defined range0802 Data Validation - Drop Down List using named range0803 Data Validation - Numbers w. Error Alert and Input Message0804 Data Validation - Dates w. Error Alert and Circle Invalid Data0805 Data Validation - Text Length w. Error Alert0806 Data Validation - Custom w. formula logic2:063:081:063:091:062:00Section 9: Organising Data64656667680901 Grouping/UnGrouping Columns and Rows - 1 of 20902 Grouping/UnGrouping Columns and Rows - 2 of 20903 Grouping Trick: Changing placement of Grouping Button0904 Cell Gridlines: Turning On/Off0905 Trick - Hide/Unhide Rows and Columns and Freeze panes3:000:071:070:085:05Section 10: Pivot Table691001 Pivot Table - Why should you learn?4:00
Excel & Analytics Courses7071728586871002 Pivot Table - Pre-requisites1003 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 (%) ofGrand Total1007 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) ofColumn/Row Total1008 Pivot Table - Grouping - Numbers1009 Pivot Table - Grouping - Dates [Years, Months etc.]1010 Pivot Table - Grouping - Text1011 Pivot Table - Refresh vs. Refresh All, Change Data Source and creatingDynamic Table Data Source1012 Pivot Table - Auto Refresh1013 Pivot Table - Pivot Chart Shortcut and Sparklines1014 Pivot Table - Drill Down option1015 Pivot Table - Report Filter - Generating 100s of reports in few seconds1016 Pivot Table - Slicer vs. Report Filter1017 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 1061101 VLOOKUP() for Starters1102 VLOOKUP w. TRUE vs. FALSE1103 VLOOKUP w. TRUE - applications1104 HLOOKUP() vs. VLOOKUP()1105 MATCH() - Basics1106 MATCH() match type: -1 vs. 0 vs. 11107 2-D Lookup (Vertical Horizontal) - VLOOKUP w. MATCH1108 2-D Lookup - VLOOKUP w. MATCH - Framework1109 2-D Lookup - VLOOKUP w. MATCH - Common Mistake #11110 2-D Lookup - VLOOKUP w. MATCH - Common Mistake #21111 2-D Lookup - VLOOKUP w. MATCH - Practice Exercise1112 2-D Lookup (Horizontal Vertical) - HLOOKUP w. MATCH1113 INDIRECT() - Basics1114 INDIRECT() w. Range Naming - Applications1115 3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Question1116 3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Answer1117 Reverse Lookup - Question1118 Reverse Lookup - INDEX() w. MATCH() - 1 of 21119 Reverse Lookup - INDEX() w. MATCH() - 2 of tion 11: Lookup & Conditional 036:026:084:036:004:037:023:048:026:01
Excel & Analytics Courses1071081091101111121120 SUMIFS(): Conditional Summation (1 criteria)1121 SUMIFS(): Conditional Summation (2 criteria)1122 SUMIFS(): Conditional Summation (3 criteria) w. date range1123 SUMIFS(): Condition based Selective Cumulative Running Total1124 COUNTIFS() - Single/Multiple Criteria: Duplicate Count, Instance No.1135 OFFSET() with MATCH()4:044:025:035:056:0115:00Section 12: Data 1271281291301311321331341201 Text Formulas – Change Case using UPPER(), PROPER() & LOWER()1202 Text Formulas – Remove Leading, Trailing & excess Intermittent spaces usingTRIM()1203 Text Formulas – Using LEN() & TRIM() together1204 Text Formulas – T(), N() & VALUE()1205 Text Formulas – REPT() for artifical Column Chart in a Cell1206 Text Formulas – REPT() & LEN() for adding leading zeroes before incompleteCheque Nos.1207 Joining data strings using CONCATENATE, & - to be updated1208 Find & Replace – Using Wildcard character ( * )1209 Find & Replace – Using Wildcard character ( ? )1210 Find & Replace – Neutralising Wildcard characters to remove them from data1211 Find & Replace – Word vs. Excel1212 Find & Replace – Cell Format1213 Text to Columns – Basics (Delimited)1214 Text to Columns – Basics (Fixed Width) Hidden Trick1215 Text to Columns – Cleaning up numbers w. trailing minus sign1216 Text to Columns – Cleaning up numbers w. leading "Dr/Cr" text1217 Text to Columns – Correcting invalid Dates (DMY)1218 Text to Columns – Correcting invalid Dates (YMD)1219 Text Formulas – LEFT(), RIGHT() & SEARCH() - 1 of 21220 Text Formulas – LEFT(), RIGHT() & SEARCH() - 2 of 21221 Text Formulas – MID() & LEN()1222 Text Formulas – REPLACE() & Section 13: Logical Formula1351361371381301 Logical formulas: ISNUMBER(), ISTEXT(), ISBLANK(), ISERROR(), ISFORMULA()1302 Logical formulas: Basics of IF() and Nested IF()1303 Logical formulas: AND(), OR()1304 Logical formulas: Combining IF() w. AND() & OR()4:035:035:023:06Section 14: Conditional Formatting1391401411421431441401 Conditional Formatting: Actual vs. Budget Comparison through Arrows1402 Conditional Formatting: w. Drop-down list based Input1403 Conditional Formatting: Data Bars, Color Scales, Icon Sets1404 Conditional Formatting: Blanks, Errors, Values, Duplicates1405 Conditional Formatting: Formula based (Colored rows based in user input)1406 Conditional Formatting: Formula based w. AND() - 2-way input Coloring5:083:022:055:004:025:03
Excel & Analytics Courses1451407 Conditional Formatting: Formula based w. COUNTIFS() - Highlighting inputs iffrom restricted list4:02Section 15: What-If Analysis1461471481491501511521531501 What IF Analysis – Using Form Control Buttons from Developer Tab (Spin Bar,Scroll Bar)1502 What IF Analysis – Limitations of Spin Bar, Scroll Bar1503 What IF Analysis – Scenario Managers1504 What IF Analysis – Goal Seek1505 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & 1Output1506 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - with PMT forLoan EMI1507 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs &multiple Output - Question1508 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs &multiple Output - Answer5:014:007:004:058:035:038:038:03Section 16: Subtotal1541551561571581591601611621631641601 Subtotal - Creating a 1-level SubTotal on a Sorted list1602 Subtotal - Deleting a SubTotal1603 Subtotal - Creating a 2-level SubTotal on a Sorted list1604 Subtotal - Coloring the Subtotal rows with different Color1604A Subtotal - Trick to Merge blocks of cells with similar names1605 Consolidate - 2 Dimensions1606 Consolidate - 3 Dimensions1701 Cell level Security1702 Cell level Security Challenge1703 Sheet level Security1704 File level 08Section 17: Printing & Comments1651661671681691701711801 Printing - Rows to Repeat at Top (Print Titles)1802 Printing - Gridlines1803 Printing - Custom Header & Footer1804 Printing - Page Order - Vertical vs. Horizontal1805 Printing - Tricks for Financial Analysts - Check underlying formulas1806 Printing - Tricks for Financial Analysts - Cell Comments1807 Printing - Print Entire Workbook2:031:022:092:021:081:081:00Section 18: Security & Password1721731741751901 Comments - Shortcuts, Inserting Picture in Comment Box1902 Split Windows, Viewing multiple Windows - For working with differentworkbooks, worksheets & scattered cell ranges simultaneously1903 Hyperlinking (Ctrl K)2001 New Feature in v. 2013 - FlashFill (Hindi)Section 19: Charts4:047:013:06
Excel & Analytics 90191192193194195196Charts - Basic Setting 01Charts - Basic Setting 02Trendline with Column ChartThermometer Chart 01Thermometer Chart 022 axis Chart with Ghost linesHorizontal Bar Chart100% Stacked Bar ChartWaterfall Chart - A Quick DemoWaterfall Chart - LogicWaterfall Chart - Exercise 01Waterfall Chart - Exercise 02Combo Stacked Dual-axis Chart100% Stacked Column ChartRinged Doughnut ChartRadar or Spider ChartGantt ChartCharts Aesthetics - 7 tipsCase Study - NBFS Company 01Case Study - NBFS Company 02Tornado / Butterfly :017:033:033:085:005:017:058:003:098:00
Excel & Analytics CoursesExcel VBASN0102Lecture NameSection 1: Introduction0101 Why do we learn Excel VBA Macros?0102 How to learn Excel VBA Macros?Duration4:441:41Section 2: Basic Settings030405060201 How to enable Developer Tab in Excel?0202 Basics of Developer Tab in Excel0203 Important Macro Security Settings in Excel0204 How to save Macros in Excel for future use?1:591:012:113:45Section 3: Record & Run Macros0708091011120301 How to create a Macros in Excel - Part 10302 How to run a Macro in all open Workbooks - Part 20303 Using a Button to run a Macros in Excel - Part 30304 Running a Macro in different ways in Excel - Part 40305 Understanding VBA Workspace in Excel (Basics) - Part 50306 Watch a Macro being recorded - Part 65:532:012:554:352:552:07Section 4: Google Readymade VBA Codes13140401 Searching Google for VBA Codes - using the 3 magic words0402 Searching Google for VBA Codes - in Blogs vs Forum8:096:55Section 5: Know your Workspace151617180501 VBA Workspace - a detailed tour0502 How to increase the font size of the VBA Codes0503 3 shortcuts to write VBA Codes faster0504 VBA Code Testing Tricks2:561:222:338:33Section 6: Objects, Properties, Methods192021220601 Concept of Objects, Properties, Methods in Excel VBA - Part 10602 Concept of Objects, Properties, Methods in Excel VBA - Part 20603 Concept of Objects, Properties, Methods in Excel VBA - Part 30604 Concept of Objects, Properties, Methods in Excel VBA - Part 43:142:272:231:27Section 7: Range & Cells Object2324250701 Range & Cells Object in Excel VBA0702 Example - Using Cells Object with a simple Do-While Loop in Excel VBA0703 What is OFFSET property in Excel VBA6:296:213:39Section 8: Range Navigation2627280801 Range Navigation in Excel VBA (Basics)0802 Exercise - Finding Last filled Row & Next Row in Excel VBA0803 Exercise - Finding Last Column No.3:515:193:42Section 9: Variable Declaration290901 Variables - Concept, How & Why declare Variables, Initialization - Part 17:43
Excel & Analytics Courses3031323334350902 Variables - Data Types - Part 20903 Variables - Best Practices to name Variable - Part 30904 Variables - How to remember to declare Variables (Option Explicit) - Part 40905 Variables - DIM vs SET - Part 50906 Pvt vs Public - Part 10907 Pvt vs Public - Part 22:061:321:392:432:165:31Section 10: Loops36373839404142431001 Loops - For Next Loop - Basics1002 Loops - If Then Else with For Next Loop1003 Loops - For Each Next - Basics1004 Loops - If Then Else with For Each Next Loop1005 Loops - Do While Loop - Basics1006 Loops - Do Until Loop - Basics1007 Loops - With Statement1008 Loops - Select Case - Basics5:066:085:313:446:274:594:287:01Section 11: Input Box & Message Box4445464748495051521101 Input Box - Basics - Part 11102 Input Box - Inserting user input in a cell - Part 21103 Input Box - Different Value Types - Part 31104 Input Box with IF Then Else Statement - Part 41105 Introduction1106 MsgBox - Part 11107 MsgBox - Part 21108 MsgBox - Part 31109 MsgBox - Part 43:303:323:385:521:353:486:261:232:16Section 12: User Forms5354555657585960616263646566671201 TextBox & CommandButton - Basics - Part 1 - Project 11202 TextBox & CommandButton - Designing - Part 2 - Project 11203 TextBox & CommandButton - Configuring - Part 3 - Project 11204 TextBox & CommandButton - Load button - Part 4 - Project 11205 Option Button & CommandButton - Basics - Part 1 - Project 21206 Option Button & CommandButton - Configuring - Part 1 - Project 21207 Option Button in Multiple Clusters (3 ways) - Part 1 - Project 31208 Option Button in Multiple Clusters (3 ways) - Part 2 - Project 31209 Multi Page - Introduction1210 Multi Page - Part 11211 Multi Page - Part 21212 Multi Page - Part 31213 Check Box - Part 11214 Combo Box - Method 11215 Combo Box - Method 36:372:204:57Section 13: User Defined Functions (UDF)681301 Basics6:15
Excel & Analytics Courses697071721302 Example 1 - ScoreResult with IF END IF1303 Example 2 - CellComments1304 Samples - Series A1305 Samples - Series B7374401 Form Controls vs ActiveX Controls1402 MouseMove Event w. Appln Statusbar3:534:044:550:59Section 14: ActiveX Controls3:494:43Section 15: Events75767778798081821501 Introduction1502 Events - Workbook Open and Worksheet Activate1503 Exercise 1 - Tracking Workbook Opens1504 E
1011 Pivot Table - Refresh vs. Refresh All, Change Data Source and creating Dynamic Table Data Source 6:03 80 1012 Pivot Table - Auto Refresh 0:07 81 1013 Pivot Table - Pivot Chart Shortcut and Sparklines 2:02 82 1014 Pivot Table - Drill Down option 1:00 83 1015 Pivot Table - Report Filte
