Microsoft Excel: Excel Sheet Comparison with VBA |
Introduction to Compare Data Workbook |
|
Introduction to Compare Data Workbook |
|
00:06:00 |
|
Download Resources |
|
00:00:00 |
|
Key requirements for Comparing any Datasets or Worksheets |
|
00:03:00 |
|
Tool to Compare Datasets or Worksheets |
|
00:06:00 |
Comprehensive Introduction to Compare Data Excel VBA Workbook |
|
Overview of Compare Data Workbook |
|
00:09:00 |
|
Most Important things to remember before you use compare data workbook |
|
00:03:00 |
|
IMPORTANT – First Column as KEY Column with UNIQUE Values |
|
00:09:00 |
|
Deep Dive into Compare Data Workbook’s Environment |
|
00:10:00 |
|
How to Change Header Rows for your data |
|
00:06:00 |
Modify VBA Codes to Suit Your Needs |
|
Basics of Using macro enabled Excel files and Code Modules |
|
00:04:00 |
|
Changing the Main two sheets name, variance tags in VBA Code |
|
00:05:00 |
|
Changing various elements across VBA Code modules |
|
00:05:00 |
|
Clear Sheet data button VBA Code |
|
00:04:00 |
Data Cleaning, Format and Structure Your Datasets with Power Query |
|
Convert Row data into Structured, clean and formatted data with Power Query 1 |
|
00:07:00 |
|
Convert Row data into Structured, clean and formatted data with Power Query 2 |
|
00:07:00 |
Practical and Real world application of Compare Data Workbook |
|
Mutual Funds Portfolio – Finding Variances at two different times |
|
00:07:00 |
|
Comprehensive Payroll Analysis – Creating Powerful Dashboard 1 |
|
00:05:00 |
|
Comprehensive Payroll Analysis – Creating Powerful Dashboard 2 |
|
00:07:00 |
|
Comprehensive Payroll Analysis – Creating Powerful Dashboard 3 |
|
00:08:00 |
|
Comprehensive Payroll Analysis – Creating Powerful Dashboard 4 |
|
00:07:00 |
|
Comprehensive Payroll Analysis – Creating Powerful Dashboard 5 |
|
00:08:00 |
Conclusion |
|
IMPORTANT Facts and Limitation of Compare Data Workbook |
|
00:03:00 |
|
Conclusion |
|
00:01:00 |
Assignment |
|
Assignment – Microsoft Excel: Excel Sheet Comparison with VBA |
|
00:00:00 |
Microsoft Excel Training: Depreciation Accounting |
Introduction |
|
Introduction |
|
00:03:00 |
|
Minimum requirements before taking the course |
|
00:01:00 |
|
Download master depreciation template |
|
00:00:00 |
Depreciation Amortization and Related Terms |
|
Definition of Depreciation |
|
00:02:00 |
|
Amortization: Depreciation vs Amortization |
|
00:02:00 |
|
Related Terms to Depreciation |
|
00:02:00 |
Various Methods of Depreciation and Depreciation Accounting |
|
Most Common Methods of Depreciation |
|
00:09:00 |
|
Accounting for Depreciation |
|
00:05:00 |
Depreciation and Taxation |
|
Depreciation Tax Shield |
|
00:04:00 |
|
Depreciation Tax Shield Under Various Depreciation Methods |
|
00:03:00 |
Master Depreciation Model |
|
Introduction and basics of Depreciation Model in Excel |
|
00:01:00 |
|
Deep Dive into Depreciation Model |
|
00:07:00 |
|
How to use the model with Example |
|
00:10:00 |
|
Advanced functionalities of the Model |
|
00:11:00 |
Conclusion |
|
Thank you for Enrolling |
|
00:01:00 |
Resources |
|
Resources – Microsoft Excel Training: Depriciation Accounting |
|
00:00:00 |
Assignment |
|
Assignment – Microsoft Excel Training: Depreciation Accounting |
|
00:00:00 |
Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query |
Introduction |
|
Course Outline and Introduction |
|
00:04:00 |
|
Minimum Requirements for the Course |
|
00:01:00 |
|
Prepayments Introduction |
|
00:01:00 |
Prepaid Expenses Models: Resources Download |
|
Month End Date Prepaid Expenses Amortization Calculation |
|
00:00:00 |
|
Exact Prepaid Expenses Payment Date Calculation |
|
00:00:00 |
Accounting for Prepaid Expenses |
|
Prepaid Expenses Accounting Definition: Prepayments |
|
00:03:00 |
|
Prepaid Expense Example: How Accounting works for Prepayments |
|
00:03:00 |
|
Advantages and Disadvantages of Prepaid Expenses |
|
00:03:00 |
Excel Formulas Detailed: Introduction to three Excel Models |
|
Introduction to PRO Excel Models and Formulas |
|
00:06:00 |
|
Date Function |
|
00:05:00 |
|
EOMONTH Function |
|
00:04:00 |
|
DATEVALUE function |
|
00:03:00 |
|
IF Function |
|
00:08:00 |
|
IFS Function (Office 365 Only) |
|
00:07:00 |
|
VLOOKUP Function |
|
00:07:00 |
|
MATCH Function |
|
00:05:00 |
|
INDIRECT Function |
|
00:02:00 |
|
NAMED Ranges: Name Manager |
|
00:03:00 |
|
Advanced Version of VLOOKUP Function |
|
00:07:00 |
Formula Based Prepaid Expenses Model |
|
Introduction to Model and Control Panel Tab (Important Sheet Tab) |
|
00:08:00 |
|
Formula Based Prepaid Expenses Model – Deep Dive (Part 1) |
|
00:05:00 |
|
Formula Based Prepaid Expenses Model – Deep Dive (Part 2) |
|
00:06:00 |
|
Formula Based Prepaid Expenses Model – Deep Dive (Part 3) |
|
00:06:00 |
|
IFS Function – Month End date Prepayment calculation |
|
00:04:00 |
|
Prepaid Expenses – Closing Balance Summary Tab (Formula Based Summary) |
|
00:09:00 |
|
Protecting Formulas Cells and Fields in the Model |
|
00:04:00 |
Calculate Prepaid Expenses Amortisation from Exact Start Date |
|
Exact Date Prepaid Amortisation calculation Intro |
|
00:03:00 |
|
Formulas update and Model Changes for Exact Prepaid Exps Calculation |
|
00:03:00 |
|
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 1) |
|
00:04:00 |
|
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 2) |
|
00:03:00 |
|
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 3) |
|
00:02:00 |
|
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 4) |
|
00:07:00 |
|
IFS Function – Exact Date Prepayments Amortisation |
|
00:04:00 |
|
Data Validation Controls (Enhancing Data Input Controls with Protection) |
|
00:10:00 |
|
Bonus: Prepayment Model with Opening Balance Calculation (Part 1) |
|
00:08:00 |
|
Bonus: Prepayment Model with Opening Balance Calculation (Part 2) |
|
00:09:00 |
|
Additional Material: Resources |
|
00:00:00 |
Prepaid Expenses Summary with Power Query and Pivot Table |
|
Power Query and Pivot Table Prepayment Summary Table Intro |
|
00:06:00 |
|
What is Power Query and Some Awesome Resources for Power Query learning |
|
00:07:00 |
|
Power Query and Pivot Table Summary – Deep Dive (Part 1) |
|
00:05:00 |
|
Power Query and Pivot Table Summary – Deep Dive (Part 2) |
|
00:04:00 |
|
Power Query and Pivot Table Summary – Deep Dive (Part 3) |
|
00:05:00 |
|
Power Query and Pivot Table Summary – Deep Dive (Part 4) |
|
00:09:00 |
|
Using Array Formulas to Add Formula Protection |
|
00:04:00 |
|
Bonus: Allocate Prepaid Expenditure Cost Centre Wise – 1 |
|
00:02:00 |
|
Bonus: Allocate Prepaid Expenditure Cost Centre Wise – 2 |
|
00:08:00 |
|
Bonus: Prepayment Model with Opening Balance Calculation (PQ and PT Version) |
|
00:13:00 |
Advanced VBA Prepaid Expenses Amortisation Model |
|
Changing Macros Security in Excel |
|
00:05:00 |
|
Complete Walkthrough – Advanced VBA Prepaid Expenses Amortisation Model |
|
00:06:00 |
|
Bonus : New Version – Excel VBA Model for Prepayment Expenditure |
|
00:08:00 |
BONUS: Dynamic Dashboard for Divisional Profit and Loss statements: Easy Way |
|
Dynamic Dashboard Overview |
|
00:07:00 |
|
Importing Profit and Loss Statements Source Files and creating YTD P&L Sheets |
|
00:08:00 |
|
Creating Dynamic Data Validation |
|
00:02:00 |
|
Creating Named Ranges for Dynamic Table Arrays |
|
00:03:00 |
|
Dynamic Date Column Headings for each Divisional PL Table |
|
00:02:00 |
|
Dynamic Month and YTD Dashboard tables headings (PRO TIP) |
|
00:03:00 |
|
Dynamic VLOOKUP Formula – Preparing First section of the Dashboard |
|
00:04:00 |
|
Creating Rolling Dashboard with Dynamic VLOOKUP Function |
|
00:08:00 |
|
IMPORTANT : Error Checking for your reports/Dashboard (PRO TIP) |
|
00:03:00 |
|
Data Prep for Visualization: AREA Charts (Awesome trick using #NA Function) |
|
00:05:00 |
|
Visualization: AREA Charts for Month – Revenue, Gross Profit and Net Profit |
|
00:05:00 |
|
Visualization DONUT Charts Revenue, Gross Profit and Net Profit (Part 1) |
|
00:03:00 |
|
Visualization DONUT Charts Revenue, Gross Profit and Net Profit (Part 2) |
|
00:06:00 |
Power Query & Pivot Tables based Dashboard without any Formulas, Fully Dynamic |
|
Introduction – Formula-less Dashboard – Fully Dynamic and easily refreshed |
|
00:05:00 |
|
Understanding the data files before building dashboard |
|
00:02:00 |
|
Consolidating Reports with Power Query (Get & Transform) , How to install PQ |
|
00:08:00 |
|
Dynamic File Path Trick in Power Query with Parameters (Amazing trick) |
|
00:06:00 |
|
Conditional Cumulative totals with SUMIFS Function |
|
00:04:00 |
|
Bonus: Conditional Cumulative totals with Power Query Custom Formula (M Code) |
|
00:06:00 |
|
Dashboard Creation – Pivot Table showing Month and YTD KPIs division wise |
|
00:06:00 |
|
Dashboard Creation Donuts Charts linked with Pivot Table (Replicate Charts fast) |
|
00:08:00 |
|
Dashboard Creation – Line Charts |
|
00:08:00 |
|
Update Dashboard with Additional Divisional Data with Few Click (Magical) |
|
00:03:00 |
Thankyou |
|
Thank you |
|
00:02:00 |
|
Ultimate Prepaid Expenditure Model (Super Bonus) |
|
00:02:00 |
Resources |
|
Resources – Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query |
|
00:00:00 |
Assignment |
|
Assignment – Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query |
|
00:00:00 |