Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Master Reporting Automation With Google Sheets
Introduction
Introduction (5:01)
Why Google Sheets, not Excel? (3:00)
Quick Example: How to Efficiently Automate Reporting (1:52)
Overview of Course Projects (2:25)
Learn and Practice the Key Formulas
Introduction (1:10)
Key Formula Combination - INDEX & MATCH (7:02)
Determining Year with TEXT (2:07)
Smart use of IF's - Dynamic SUMIF and AVERAGEIF (7:51)
Use of COUNTIF(S) (3:38)
Identify ROW & COLUMN Numbers (2:14)
RANK the Data (1:52)
Avoid Errors with IFERROR (2:40)
Improve Calculation Accuracy with AVERAGE.WEIGHTED (2:20)
Quiz 1: Key Formula Quiz
Learn and Practice the Key Formulas: Advanced Section
Introduction (0:39)
Pulling Data with INDIRECT & ADDRESS (2:41)
Use OFFSET for Dynamic Calculations (4:34)
IMPORTRANGE with INDEX & MATCH (5:07)
Advanced Formula: QUERY (2:58)
Advanced Formula: QUERY 2 (3:44)
Section Recap (1:08)
Quiz 2: Key Formula 2 Quiz
Putting Together the Project
Introduction (2:43)
Mapping Down the Metrics (11:02)
Defining the Week Periods and Week References (3:59)
Connecting the Formulas with INDEX & MATCH (3:28)
Setting Up Budget Connections and Calculations (8:03)
Formatting of Week Numbers (1:53)
Visual Formatting (8:48)
Quiz 3: Project Mapping Quiz
Adding the Forecasts
Introduction (0:42)
Defining Sheet Structure (5:01)
Setting Up Forecasting Formulas (8:57)
Advanced: Connecting Stable Metrics to Dataset (11:48)
Linking Targets with Actuals (6:51)
Defining Current Week through TODAY Formula (3:09)
Target Conversion from Weekly to Monthly (5:28)
Comparison between Targets and Budgets (6:04)
Notification for Reconciliation with Budgets (8:40)
Sections Recap (1:19)
Quiz 4: Forecasting Quiz
Building the Comparison Between Actuals and Forecasts
Introduction (0:40)
Defining Sheet Structure (4:11)
Connecting the Formulas (2:48)
Not Showing Data for Unpassed Weeks (2:13)
Calculating the Variances (2:19)
Conditional Formatting for Variances (3:02)
Conditional Color Notifications (7:16)
Final Touches (1:25)
Quiz 5: Comparison Building Quiz
Designing the Landing Page
Introduction (0:27)
What to Include in the Cover tab? (7:00)
Table of Contents (3:05)
Linking Key Information (2:48)
Use Your Brand Colours (5:15)
Section Recap (1:32)
Quiz 6: Landing Page Quiz
Reconciling Weekly Actuals with Monthly Budgets
Introduction (1:03)
Defining Sheet Structure (5:57)
Connecting the Data to the Tab (5:16)
Color Coding for Weekly Trends (2:53)
Color Coding for Monthly Budgets (10:00)
Final Touches (1:39)
Quiz 7: Actuals vs Budget Quiz
Setting Up Competitor and Market Size Tracking
Introduction (0:56)
Defining Sheet Structure (1:39)
Creating the Framework for Competitor Tracking (3:08)
Adding Formulas to Framework (5:35)
Estimating Competitor and Market Size (10:27)
Summary of Key Metrics (8:34)
Final Touches (7:10)
Connection with Other Tabs (8:23)
Sections Recap (1:36)
Quiz 8: Market Sizing Quiz
Professional and Effective Data Visualisation
Introduction (1:41)
Intro to the First Graph (1:09)
Data Visualisation Principles (2:11)
Graph I: Key Actuals with Targets (17:55)
Graph II: Monthly Actuals with Budgeted Forecasts (12:16)
Dynamic Table: Data Layout for Competitor Tracking (10:57)
Dynamic Table: Ranking for Competitor Tracking (6:02)
Graph III: Tracking Weekly Performance over Monthly Budget (10:31)
Final Touches (0:47)
Section Recap (1:51)
Quiz 9: Data Visualisation Quiz
Creating Dynamic and Reliable Data Connections
Introduction (0:41)
Status for Data Dump (6:08)
Dynamic Budget Connection with IMPORTRANGE (4:15)
Dynamic Budget Connection with QUERY (4:13)
Final Touches (3:45)
Quiz 10: Data Connections Quiz
Wrapping Up the Project
Introduction (0:42)
Adding Instructions (1:39)
Reviewing Links in Cover tab (1:09)
Final Touches on Whole File (3:17)
Protecting the Sheets (3:29)
Recap of Course Project (1:51)
Quiz 11: Project Wrap-Up Quiz
Further Development Tips and Tricks
Introduction (0:49)
Automating Data Dump through a Script (2:37)
Creating Multi-Level Reporting System (3:15)
Optimise Spreadsheet Performance (1:40)
Workaround for IMPORTRANGE Internal Error (2:13)
Manage Reporting Files through Scripts (2:07)
Section Recap (1:07)
Quiz 12: Tips and Tricks Quiz
Conclusion
Conclusion (2:16)
Bonus Lecture (3:15)
Manage Reporting Files through Scripts
Complete and Continue