This course was created with the
course builder. Create your online course today.
Start now
Create your course
with
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:03)
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)
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)
Key Formula 2 Quiz
Putting Together the Project
Introduction (2:44)
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)
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)
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)
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)
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)
Actuals vs Budget Quiz
Setting Up Competitor and Market Size Tracking
Introduction (0:56)
Defining Sheet Structure (1:40)
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)
Market Sizing Quiz
Professional and Effective Data Visualisation
Introduction (1:41)
Intro to the First Graph (1:10)
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:03)
Graph III: Tracking Weekly Performance over Monthly Budget (10:31)
Final Touches (0:47)
Section Recap (1:51)
Data Visualisation Quiz
Creating Dynamic and Reliable Data Connections
Introduction (0:41)
Status for Data Dump (6:08)
Dynamic Budget Connection with IMPORTRANGE & TRANSPOSE (4:15)
Dynamic Budget Connection with QUERY (4:13)
Final Touches (3:45)
Data Connections Quiz
Wrapping Up the Project
Introduction (0:42)
Adding Instructions (1:40)
Reviewing Links in Cover tab (1:09)
Final Touches on Whole File (3:17)
Protecting the Sheets (3:29)
Recap of Course Project (1:52)
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 Errors (2:13)
Manage Reporting Files through Scripts (2:07)
Section Recap (1:07)
Tips and Tricks Quiz
Conclusion
Conclusion (2:16)
Bonus Lecture (3:15)
Final Touches
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock