Google Sheets Fundamentals Course Notes

Course Overview

• Project-based course focused on Google Sheets fundamentals.
• Instructor: Eamon Cottrell.
• Suitable for both beginners and advanced users.
• Key topics include navigation, functions, custom formulas, conditional formatting, data visualization, and more.

Module 1: Introduction to Google Sheets

• Basic and advanced functionality of Google Sheets.
• Introduction to spreadsheet layout:
  • Columns: Labeled by letters (A, B, C...)
  • Rows: Numbered from 1 onward.
  • Cells: Intersection of columns and rows (e.g., A1, B2).

Navigation and Basic Actions

• Use the mouse or arrow keys for navigation.
• Keyboard shortcuts:
  • Ctrl + Home: Go to top-left corner.
  • Ctrl + End: Go to bottom-right corner.
  • Ctrl + Arrow Keys: Jump to next filled cell.
  • Insert/Delete Rows/Columns: Right-click to insert/delete.

Naming and Managing Sheets

• Rename sheets by double-clicking the tab.
• Create multiple sheets within one spreadsheet (e.g., budget and categories) and customize colors.

Google Sheets Functions and Formulas

• Functions vs. Formulas:
  • Function: Predefined operation (e.g., SUM).
  • Formula: Custom calculation (e.g., =A1+A2).
• Common functions include SUM, AVERAGE, and COUNT.
• Highlighting and color-coding for visibility.

Module 2: Building a Personal Budget

• Create income and expense categories in the categories sheet.
• Import data from external sources (e.g., Mockaroo for fake data).
• Sort data chronologically.

Formulas and Functions in Budget Management

• Use functions to dynamically calculate balance based on income/expenses.
• Implement IF statements to categorize transactions as income or expense.
  • Example: =IF(C2 > 1000, "Big Spender", "Not Much").
• COUNTIF for counting based on criteria.

Module 3: Conditional Formatting and Data Validation

• Conditional formatting to visually distinguish data based on rules.
  • Example: Change cell color based on value thresholds.
• Data validation to create drop-down lists for categorization.
  • Use ranges from the categories sheet as sources for drop-downs.

Module 4: Advanced Functions and Filtering Data

• Combine functions to automate categorization in budget:
  • Use MATCH, IFERROR, and IF to determine income vs. expense.
• Filtering data using Filter function and slicers for dynamic data manipulation.

Module 5: Data Visualization

• Create charts to visualize budget data:
  • Pie charts for income vs expenses.
  • Bar charts for spending by category.
  • Line charts for tracking balance over time.
• Customize charts for readability and presentation purposes.

Module 6: Introduction to Google Apps Script

• Overview of Apps Script for advanced automation within Google Sheets.
• Create custom functions and menus to enhance spreadsheet functionality.
  • Example of a custom function: function amen(input) { return input + " is awesome!"; }
• Implement onOpen triggers to create menus upon opening the spreadsheet.

Conclusion

• The course offers a comprehensive foundation in Google Sheets, advanced techniques, and the potential for automation using Google Apps Script.
• For further information and resources, refer to gotsheet.xyz.